Selecting data via many to many join

[peter2012]

I am working on a database (in Access 2010) which records time worked for different clients on each of their many matters.

So there are the following tables: Clients, Matters, Timecards, Rates, MatterStats. Matterstats joins Matters to Rates as it is a many to many relationship (each matter can have several rates and every rate has many matters).

I attach a relationships screenshot.

I have a form based on the Timecards table. Each field of the form is bound to a Timecards field. I select Client from a lookup dropdown which lists client names but adds a client ID to the Timecards table.

This requeries the Matter field so that the Matter dropdown is limited to the matters of that client. This is achieved via a simple WHERE query:

SELECT Matters.ID, Matters.Matter, Matters.ClientID
FROM Matters
WHERE (((Matters.ClientID)=[Timecards]![ClientName]))
ORDER BY Matters.Matter;

(ClientName is a numeric field despite its name.)

That one I can do as it is a simple one to many join (each client has many matters).

Now I want to select the hourly rate from the Rates table. I want only the rates applicable to that matter to be shown in the Rates dropdown. The combo box is bound to Timecards.RateID and gets requeried when the Matter is selected.

I can get a list of all the rates easily with the following SQL query:

SELECT Rates.ID, Rates.Rate, Rates.FeeEarner
FROM Rates
ORDER BY Rates.Rate DESC;

This gives me all rates applicable to all matters.

It would be better if the dropdown were to be limited to the rates applicable only to the matter. That way there is less chance of choosing the wrong rate.

I have tried inner joins and subqueries and all sorts, but without success, with the idea that once I've chosen the matter (Timecards.MatterName - which is a numeric field despite its name) the RatesID field runs a query along the lines of:

SELECT Rates.ID, Rates.Rate, Rates.FeeEarner

WHERE

Rates.ID = MatterStats.RatesID

AND

MatterStats.MatterID = Matter.ID

AND

MatterID = Timecards.MatterName

I have not been able to extract the right data. I am ending up with either a blank or a list of multiple rates, i.e. multiple appearances of one rate followed by multiple appearances of another rate, etc. Can anyone suggest an SQL statement that would achieve a selection of just the one or two rates that apply to the matter?

Peter

 

[Beetle]

The Row Source SQL for your Rates combo box needs to involve two tables;

MatterStats and Rates

You should pull MatterID and RateID from MatterStats and Rate from Rates. Then, in the MatterID column you should have a reference to the form control where the current MatterID value is. The SQL would look something like;


SELECT MatterStats.MatterID, MatterStats.RateID, Rates.Rate
FROM Rates INNER JOIN MatterStats ON Rates.ID = MatterStats.RateID
WHERE MatterStats.MatterID=forms!TimeCards!MatterName

You would then requery the Rate combo box in the Current event of your form and/or the After Update event of your other (Matters?) combo box.

 

[peter2012]

Beetle, yes that works, you are a star - reference to the form control particularly important - I was referencing the field value (and too many tables!).

I've definitely learnt a few things. Thanks

Peter