If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database -relationship set up
Hi all,
I posted earlier on a query that was doubling up the values and it was suggested that the query was somehow linked. So I'm going back to basics and trying to really understand this relationship: I have a table of transcations.....freight invoices main fields a From Suburb - To Suburb - items - kilos - charge As most freight suppliers put the delivery suburb on the invoice, but chatge by a direct port, I have set up a postcode table also which lists : Suburb - Postcode - Direct Port The data entry person doesn't usually know the direct port associated with the suburb, so I've attempted to set up a query that looks up the suburb and returns the direct to and from ports with the sum of items, kilos and charge. This doesn't seem to adding in the items, kilos and charges fields. So my questions a In the transaction table - what field should the PK be? What relationship should I set up between the "From Suburb" & "To Suburb" [TblTranscations] and my [TblPostcode]? I've spent weeks on this and if I got paid to do this I'd be a millionairre! Thanks, hope I've explained this ok. Harry |
#2
|
|||
|
|||
Database -relationship set up
G'day Harry!
I took a look at your other post and while I don't have a solution, I do have couple of helpful tips. When posting table structure information, use this format: tblTransactions TransID FromSuburbID ToSuburbID QtyItems Kilos Charge tblPostCodes SuburbID Postcode DirectPort When asked for the SQL for the query, open the query in design view, right click the area that displays the tables, select SQL view, select all of the text in the window, copy it (Ctrl-c), then paste it onto your posting here (Ctrl-v). The text would be something like this.... SELECT First(tblTimeClock.TimeClockDate) AS [TimeClockDate Field], First(tblTimeClock.InstallerID) AS [InstallerID Field], Count(tblTimeClock.TimeClockDate) AS NumberOfDups, tblTimeClock.TimeClockDate, tblTimeClock.InstallerID, Count(tblTimeClock.InstallerID) AS CountOfInstallerID, Installer.InstallerFirstName & " " & Installer.InstallerLastName AS Installer FROM tblInstallers AS Installer INNER JOIN tblTimeClock ON Installer.InstallerID = tblTimeClock.InstallerID GROUP BY tblTimeClock.TimeClockDate, tblTimeClock.InstallerID, Installer.InstallerFirstName & " " & Installer.InstallerLastName HAVING (((Count(tblTimeClock.TimeClockDate))1) AND ((Count(tblTimeClock.InstallerID))1)); Of course, this sample has nothing to do with your problem - I just hope that it gives you an idea of how the information that Van was asking for might look like. hope this helps.... -- rpw "Harry Bo" wrote: Hi all, I posted earlier on a query that was doubling up the values and it was suggested that the query was somehow linked. So I'm going back to basics and trying to really understand this relationship: I have a table of transcations.....freight invoices main fields a From Suburb - To Suburb - items - kilos - charge As most freight suppliers put the delivery suburb on the invoice, but chatge by a direct port, I have set up a postcode table also which lists : Suburb - Postcode - Direct Port The data entry person doesn't usually know the direct port associated with the suburb, so I've attempted to set up a query that looks up the suburb and returns the direct to and from ports with the sum of items, kilos and charge. This doesn't seem to adding in the items, kilos and charges fields. So my questions a In the transaction table - what field should the PK be? What relationship should I set up between the "From Suburb" & "To Suburb" [TblTranscations] and my [TblPostcode]? I've spent weeks on this and if I got paid to do this I'd be a millionairre! Thanks, hope I've explained this ok. Harry |
#3
|
|||
|
|||
Database -relationship set up
Thanks rpw,
Ok my tables a tblTransactions TransID FromSuburbID ToSuburbID Service QtyItems Kilos Charge tblPostCodes SuburbID Postcode DirectPort I have 2 postcode tables , one for the From's and one for the To's thanks rpw! The SQL for the query is: SELECT QryFro.[Direct Port] AS [From], QryTo.[Direct Port] AS [To], TblTransactions.Service, TblTransactions.Items, TblTransactions.Kilograms FROM (QryFro INNER JOIN TblTransactions ON QryFro.[From Suburb] = TblTransactions.[From Suburb]) INNER JOIN (QryTo INNER JOIN [To Suburb] ON QryTo.[To Suburb] = [To Suburb].SubPost) ON TblTransactions.[To Suburb] = [To Suburb].SubPost WHERE (((TblTransactions.Service) Like "A*")); Hope this helps, thanks again Harry "rpw" wrote: G'day Harry! I took a look at your other post and while I don't have a solution, I do have couple of helpful tips. When posting table structure information, use this format: tblTransactions TransID FromSuburbID ToSuburbID QtyItems Kilos Charge tblPostCodes SuburbID Postcode DirectPort When asked for the SQL for the query, open the query in design view, right click the area that displays the tables, select SQL view, select all of the text in the window, copy it (Ctrl-c), then paste it onto your posting here (Ctrl-v). The text would be something like this.... SELECT First(tblTimeClock.TimeClockDate) AS [TimeClockDate Field], First(tblTimeClock.InstallerID) AS [InstallerID Field], Count(tblTimeClock.TimeClockDate) AS NumberOfDups, tblTimeClock.TimeClockDate, tblTimeClock.InstallerID, Count(tblTimeClock.InstallerID) AS CountOfInstallerID, Installer.InstallerFirstName & " " & Installer.InstallerLastName AS Installer FROM tblInstallers AS Installer INNER JOIN tblTimeClock ON Installer.InstallerID = tblTimeClock.InstallerID GROUP BY tblTimeClock.TimeClockDate, tblTimeClock.InstallerID, Installer.InstallerFirstName & " " & Installer.InstallerLastName HAVING (((Count(tblTimeClock.TimeClockDate))1) AND ((Count(tblTimeClock.InstallerID))1)); Of course, this sample has nothing to do with your problem - I just hope that it gives you an idea of how the information that Van was asking for might look like. hope this helps.... -- rpw "Harry Bo" wrote: Hi all, I posted earlier on a query that was doubling up the values and it was suggested that the query was somehow linked. So I'm going back to basics and trying to really understand this relationship: I have a table of transcations.....freight invoices main fields a From Suburb - To Suburb - items - kilos - charge As most freight suppliers put the delivery suburb on the invoice, but chatge by a direct port, I have set up a postcode table also which lists : Suburb - Postcode - Direct Port The data entry person doesn't usually know the direct port associated with the suburb, so I've attempted to set up a query that looks up the suburb and returns the direct to and from ports with the sum of items, kilos and charge. This doesn't seem to adding in the items, kilos and charges fields. So my questions a In the transaction table - what field should the PK be? What relationship should I set up between the "From Suburb" & "To Suburb" [TblTranscations] and my [TblPostcode]? I've spent weeks on this and if I got paid to do this I'd be a millionairre! Thanks, hope I've explained this ok. Harry |
#4
|
|||
|
|||
Database -relationship set up
Hi Harry,
I posted your info back to your original thread where Van T. Dinh was helping you. Maybe one of the experts can help here or there? -- rpw "Harry Bo" wrote: Thanks rpw, Ok my tables a tblTransactions TransID FromSuburbID ToSuburbID Service QtyItems Kilos Charge tblPostCodes SuburbID Postcode DirectPort I have 2 postcode tables , one for the From's and one for the To's thanks rpw! The SQL for the query is: SELECT QryFro.[Direct Port] AS [From], QryTo.[Direct Port] AS [To], TblTransactions.Service, TblTransactions.Items, TblTransactions.Kilograms FROM (QryFro INNER JOIN TblTransactions ON QryFro.[From Suburb] = TblTransactions.[From Suburb]) INNER JOIN (QryTo INNER JOIN [To Suburb] ON QryTo.[To Suburb] = [To Suburb].SubPost) ON TblTransactions.[To Suburb] = [To Suburb].SubPost WHERE (((TblTransactions.Service) Like "A*")); Hope this helps, thanks again Harry "rpw" wrote: G'day Harry! I took a look at your other post and while I don't have a solution, I do have couple of helpful tips. When posting table structure information, use this format: tblTransactions TransID FromSuburbID ToSuburbID QtyItems Kilos Charge tblPostCodes SuburbID Postcode DirectPort When asked for the SQL for the query, open the query in design view, right click the area that displays the tables, select SQL view, select all of the text in the window, copy it (Ctrl-c), then paste it onto your posting here (Ctrl-v). The text would be something like this.... SELECT First(tblTimeClock.TimeClockDate) AS [TimeClockDate Field], First(tblTimeClock.InstallerID) AS [InstallerID Field], Count(tblTimeClock.TimeClockDate) AS NumberOfDups, tblTimeClock.TimeClockDate, tblTimeClock.InstallerID, Count(tblTimeClock.InstallerID) AS CountOfInstallerID, Installer.InstallerFirstName & " " & Installer.InstallerLastName AS Installer FROM tblInstallers AS Installer INNER JOIN tblTimeClock ON Installer.InstallerID = tblTimeClock.InstallerID GROUP BY tblTimeClock.TimeClockDate, tblTimeClock.InstallerID, Installer.InstallerFirstName & " " & Installer.InstallerLastName HAVING (((Count(tblTimeClock.TimeClockDate))1) AND ((Count(tblTimeClock.InstallerID))1)); Of course, this sample has nothing to do with your problem - I just hope that it gives you an idea of how the information that Van was asking for might look like. hope this helps.... -- rpw "Harry Bo" wrote: Hi all, I posted earlier on a query that was doubling up the values and it was suggested that the query was somehow linked. So I'm going back to basics and trying to really understand this relationship: I have a table of transcations.....freight invoices main fields a From Suburb - To Suburb - items - kilos - charge As most freight suppliers put the delivery suburb on the invoice, but chatge by a direct port, I have set up a postcode table also which lists : Suburb - Postcode - Direct Port The data entry person doesn't usually know the direct port associated with the suburb, so I've attempted to set up a query that looks up the suburb and returns the direct to and from ports with the sum of items, kilos and charge. This doesn't seem to adding in the items, kilos and charges fields. So my questions a In the transaction table - what field should the PK be? What relationship should I set up between the "From Suburb" & "To Suburb" [TblTranscations] and my [TblPostcode]? I've spent weeks on this and if I got paid to do this I'd be a millionairre! Thanks, hope I've explained this ok. Harry |
Thread Tools | |
Display Modes | |
|
|