| View previous topic :: View next topic |
| Author |
Message |
r18bby
Newbie
Joined: 30 Jan 2006
Posts: 2
|
 Posted: Tue Jan 31, 2006 2:53 am Post subject: 'Simple' Base/Calc Problem - But canot resolve! |
 |
|
All,
I would love someone to be able to help me with, what I'm sure is a simple request, BUT [code]I've got a mental block on it, and cannot find a way to do it...
In simple terms I would like a table of distances from one place to another, something like:-
[code]From To Distance
-------------------------------
London Paris 123
Paris Rome 456
Rome Milan 789[/code]
Then, I would like to be able to enter in a 'spreadsheet', from(A1) and to(B1), and for the distance to be entered in via 'select Distance from Table where From=A1 and To=B1'
Am I going mad, or is it not possible to perform this kind of select in Base/Calc...?
Many thanks in anticipation
Peter[code][/code] |
|
| Back to top |
|
 |
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Tue Jan 31, 2006 10:24 am Post subject: |
|
|
Step one - Create the database. Name it (for example) TravDist.odb
Step two - Create that table Distances, or maybe you want to create two tables.
Tables:
Locations( ID, Name)
Distances(ID, Name1ID, Name2ID, Distance)
Distances.Name1ID and Distances.Name2ID both link back to the Locations.ID field. This way you only one Location named Paris, but many distancesses for Paris (to Milan, to NY, to London.....)
Now as for the search the only tricky part is the order. In other words Paris to London, should I would imagine be the same as London to Paris, right? You can handle this either in the search routine, making order inisignificant, only the two names matter. Or you could create seperate entries in your Distances table for the different order. (this would not be my choice for a solution)
If we make the search handle the order then it would be. Using a basic macro this is gonna be fairly easy - A quick thought of the flow is
- see if we have an entry in the Locations table for the value in A1
| Code: |
IF Select * from Locations where Name = :A1 has 1 record
AND Select * from Locations where Name = :B1 has 1 record THEN
IF select * from "Distances" where "Name1ID" = :AI and "Name2ID" = :B1 then
Return "Distance"
else IF select * from "Distances" where "Name1ID" = :B1 and "Name2ID" = :A1
Return "Distance"
else
Return no record found error
endif
endif
ELSE
Report the one of the other is not found
ENDIF
|
Now it turns out we can acutally do better then this, and handle the ordering problem in our query. For the tables structure I used above you can write this query
| Code: |
SELECT
"Locations"."Name" AS "FromName",
"Locations_1"."Name" AS "ToName",
"Distances"."Distance"
FROM "Distances" "Distances", "Locations" "Locations", "Locations" "Locations_1"
WHERE ( "Distances"."Name1ID" = "Locations"."ID" AND "Distances"."Name2ID" = "Locations_1"."ID" )
AND
( ( "Locations"."Name" = :Frm AND "Locations_1"."Name" = ? )
OR
("Locations"."Name" = ? AND "Locations_1"."Name" = :Frm ) )
|
This is quite valid, so you would be prompted for the two names. In the basic macro we would turn this into a prepared statement and simply fill in the values from the spread sheet.
If you want to test this simply do this.
Create you database. Create the tables as shown and enter some data. My test data is
I recreated the query using query builder and it looks like this:
Sure enough you can enter either Paris / London or London / Paris and get the same results.
OK, now we need to tie this to Calc.
Question, how will you call it. Is it always A1 and B1 for the names or is it really
A(x) and B(x) where x is the current row?
Either way, I just don't know the API for Calc that well yet, so need to go off and actually write this with an eye to the docs...so will be a few minutes. If you answer before I get back with that..then I will adjust as dessired.
EDIT - well, a little search on the forum under Calc and I came up with this.
I created a spreadsheet and attached the following basic macro to it.
| Code: |
function getDistance( aFromTo as any) as string
dim FromName as String
dim ToName as String
FromName = aFromTo(1,1)
ToName = aFromTo( 1,2)
dim conn as variant
conn = CreateUnoService("com.sun.star.sdb.DatabaseContext")._
getByname( "TravDist" )._
getConnection( "", "" )
dim prepStmt as variant
dim SQL as String
SQL = "SELECT "
SQL = SQL & " ""Locations"".""Name"" AS ""FromName"", "
SQL = SQL & " ""Locations_1"".""Name"" AS ""ToName"", "
SQL = SQL & " ""Distances"".""Distance"" "
SQL = SQL & "FROM ""Distances"" ""Distances"", ""Locations"" ""Locations"", "
SQL = SQL & " ""Locations"" ""Locations_1"" "
SQL = SQL & "WHERE ( ""Distances"".""Name1ID"" = ""Locations"".""ID"" "
SQL = SQL & "AND ""Distances"".""Name2ID"" = ""Locations_1"".""ID"" ) "
SQL = SQL & "AND "
SQL = SQL & " ( ( ""Locations"".""Name"" = ? AND ""Locations_1"".""Name"" = ? ) "
SQL = SQL & " OR "
SQL = SQL & " (""Locations"".""Name"" = ? AND ""Locations_1"".""Name"" = ? ) ) "
prepStmt = conn.prepareStatement( SQL )
prepStmt.setString( 1, FromName)
prepStmt.setString( 4, FromName)
prepStmt.setString( 2, ToName )
prepStmt.setString( 3, ToName )
dim rs as variant
rs = prepStmt.executeQuery
if not rs.isBeforeFirst then
' no records found
getDistance = -1
else
rs.next
getDistance = rs.getInt( 3 )
endif
conn.dispose
end function
|
In the spreadsheet I entered the strings
Paris in cell A3 and London in B3, then in C3 I enter = GETDISTANCE(A3:B3). When I recalc the sheet I get the value 145 in the cell.
In cell A4 is London and in B4 is Paris, C3 is = GETDISTANCE(A4:B4) and again the value displayed is 145.
This is not particularly efficient code, for a number of reasons but it should show the basic idea. I also don't do any error checking so watch that if you just try to copy and paste this code.
Drew
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
