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

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

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

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

;jh;hv;

"Stefan Hoffmann" a écrit dans le message de groupe de
discussion : ...
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 --