Query of tables in many to many relationship

[Slaven Bojko]

I have three tables:

Groups whith field groupID and ect.
Items whith field itemID and ect.
and Relations whiht fields itemID, groupID and date.

Date field keeps the dates when an item changed gropup it belongs to. So,
its a many to many relationship. The question is how can I write a query
that will give me all the items in a specific group on a given date? I see
no obvious solution, and it seems easier to put startdate and enddate fields
in Relations table, regaldles of redudancy that occures, but then I'll have
to change the design of a database... What do you think?

Thanks,

Slaven

(please remove ".makniovo" from my addres to replay directly to me)

 

[Tom Ellison]

Dear Slaven:

Your first instinct, to not store the EndDate in the Relations table,
is correct. A query solution without this is feasible.

SELECT I.itemID, R.groupID
FROM Items I
INNER JOIN Relations R ON R.itemID = I.itemID
WHERE R.[date] = (SELECT MAX([date] from Relations R1
WHERE R1.itemID = I.itemID AND R1.[date] <= GivenDate)

Here are some notes on the above:

- I have used GivenDate to represent a date constant, control, or
parameter you would probably use to implement this. Left alone, it
will prompt for GivenDate as a parameter.

- If there are multiple entries in Relations for the same itemID with
the same [date] then your information is ambiguous as to the order in
which the groupID is changing. I would suggest that [date] be part of
the primary key to this table, as it will not function if there are
duplicates. The query will report more than one groupID for an item
if this occurs.

- The Groups table was not needed in the above as it is not reporting
any value other than groupID, which is already contained in the
Relations table. You may want ot show other columns from Groups, so
you can change this later.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts