Database -relationship set up

[Harry Bo]

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 are:

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 are:

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

 

[rpw]

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....

 

[Harry Bo]

Thanks rpw,

Ok my tables are:
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

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

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 are:

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 are:

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

 

[rpw]

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

Thanks rpw,

Ok my tables are:
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

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

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 are:

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 are:

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