Linking Tables

[Logo]

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

 

[Daryl S]

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.

 

[Stefan Hoffmann]

hi,

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

 

[De Jager]

hi,


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

 

[joelgeraldine]

;jh;hv;

hi,


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