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
|
|||
|
|||
Linking Tables
I have 2 tables linked in a one to many relationship. Problem is, one table
has values that are entered daily, and the other table has values that are entered monthly. When I try to create a query joining the two tables, the monthly values aren't joining with the daily values correctly. There must be a way to do this? TIA -- Logo |
#2
|
|||
|
|||
Linking Tables
Logo -
Can you give us the table names, the pertinent fields, and what you mean by not joining correctly? Please post your SQL also so we may be able to help. -- Daryl S "Logo" wrote: I have 2 tables linked in a one to many relationship. Problem is, one table has values that are entered daily, and the other table has values that are entered monthly. When I try to create a query joining the two tables, the monthly values aren't joining with the daily values correctly. There must be a way to do this? TIA -- Logo |
#3
|
|||
|
|||
Linking Tables
hi,
On 26.02.2010 14:52, Logo wrote: I have 2 tables linked in a one to many relationship. Problem is, one table has values that are entered daily, and the other table has values that are entered monthly. When I try to create a query joining the two tables, the monthly values aren't joining with the daily values correctly. There must be a way to do this? TIA The question is: how do you ensure referential integrity? Which fields do you use? Basically there are two common scenarios: a) surrogate keys, thus your monthly values are stored in a table with an AutoNumber field: SELECT M.*, D.* FROM dailyTable D INNER JOIN monthlyTable M ON M.ID = D.idMonth; b) you have a normal key over the date b1) you are storing the date in atomic values for year, month and day: SELECT M.*, D.* FROM dailyTable D INNER JOIN monthlyTable M ON M.year = D.year AND M.month = D.month; b2) you are storing the date in a single Date/Time field: SELECT M.*, D.* FROM dailyTable D INNER JOIN monthlyTable M ON Year(M.monthDate) = Year(D.monthDate) AND Month(M.monthDate) = Month(D.monthDate); mfG -- stefan -- |
#4
|
|||
|
|||
Linking Tables
"Stefan Hoffmann" wrote in message ... hi, On 26.02.2010 14:52, Logo wrote: I have 2 tables linked in a one to many relationship. Problem is, one table has values that are entered daily, and the other table has values that are entered monthly. When I try to create a query joining the two tables, the monthly values aren't joining with the daily values correctly. There must be a way to do this? TIA The question is: how do you ensure referential integrity? Which fields do you use? Basically there are two common scenarios: a) surrogate keys, thus your monthly values are stored in a table with an AutoNumber field: SELECT M.*, D.* FROM dailyTable D INNER JOIN monthlyTable M ON M.ID = D.idMonth; b) you have a normal key over the date b1) you are storing the date in atomic values for year, month and day: SELECT M.*, D.* FROM dailyTable D INNER JOIN monthlyTable M ON M.year = D.year AND M.month = D.month; b2) you are storing the date in a single Date/Time field: SELECT M.*, D.* FROM dailyTable D INNER JOIN monthlyTable M ON Year(M.monthDate) = Year(D.monthDate) AND Month(M.monthDate) = Month(D.monthDate); mfG -- stefan -- |
#5
|
|||
|
|||
Linking Tables
|
Thread Tools | |
Display Modes | |
|
|