Problem with multiple joins in MS Access 2007

[SADude]

Hey all - just found this site and I am hoping you can help me with a problem I am having constructing a query for a MS Access 2007 database. I have 3 tables of data simplified as follows
- persons: personID, name
- data1: personID, week, year, place, value
- data2: personID, week, year, value

The persons table is required, but, for a given week and year, the person can have data in either data1, data2, or both. I'd prefer not to combine the data1 and data2 tables since the person will often have many data1 values for a given week/year, but at most 1 data2 value for the same week/year.

I'd like to grab the persons.name, data1.place, data1.value and data2.value for a given week and year as long as there is a value for that week and year in either data1 or data2. Any help would be appreciated!

My current query is as follows:

SELECT persons.name, data1.place, data1.value, data2.value
FROM (persons LEFT JOIN data1 ON (persons.personID = data1.personID AND data1.week = 1 AND data1.year = 2010)) LEFT JOIN data2 ON (persons.personID = data2.personID AND data2.week = 1 AND dats2.year = 2010)

Using dummy data in the database, I get the following results:

1) If I have data in data1 for only 1 person but not in data2 for the week/year, it returns ALL the persons, along with the correct data in data1 and data2 (null) - I would prefer to just have persons with values in data1 or data2

2) If I have data in data2 for a given person, but not in data1 for the given week/year, I get a "Join expression not supported" error.

Thanks again for your help!

[nico5038]

The reason to spit data1 and data2 isn't valid from the normalization point of view and is making your query now more complex as needed.
So my advise is to merge these tables.

Next advise is to change the Name and Value fieldnames into e.g. PersonName and PersonValue as Name and Value are reserved words.

To solve your problem in this structure you'll need to add a GROUP BY clause on the personID and week and use a MAX() for the Name and the Value.

Nic;o)

[SADude]

Sorry, in my attempt to simplify things to make it easier to understand, I actually made it more difficult. Here's a little more info on the problem:

data1 is various hypothetical values gathered from different places. data2 is the actual measured value. So while I could merge these two tables and either have a column like "isActual" or just set the site field to -1 to indicate that this value is the actual value, that seems a little clunky.

Also, I'd like to query the database in such a way as to return recordsets with the person info, the site where the hypothetical value was from, the hypothetical value, and actual value in each row, which I could then easily display in a table. Since some places may not have a hypothetical value for the given week/year, or the actual value may not have been determined for that week/year, the query should be able to handle that and just return null for the missing value. However, if there is no hypotethical value and no actual value for that given time period, I would prefer to not have the person info returned.

Hope that helps clarify the issue and doesn't muddy it further!

[nico5038]

OK, got the idea, but not clear what to do with the PersonValue when multiple are present for one week.

I would start with a UNION query like:

SELECT tblData1.PersonID, tblData1.PersonWeek, tblData1.PersonYear, 0 as ActualValue, tblData1.PeronsPlace, tblData1.PersonValue
FROM tblData1
UNION SELECT tblData2.PersonID, tblData2.PersonWeek, tblData2.PersonYear,  tblData2.PersonValue, "",0
FROM tblData2;

This will create a row for each data1 and data2 row.

Now this query can be JOINED with the person table like:

SELECT tblPersons.Name, qryDataUNION.PersonWeek, qryDataUNION.PersonYear, Max(qryDataUNION.ActualValue) AS MaxOfActualValue, Max(qryDataUNION.PeronsPlace) AS MaxOfPeronsPlace, Max(qryDataUNION.PersonValue) AS MaxOfPersonValue
FROM tblPersons INNER JOIN qryDataUNION ON tblPersons.PersonID = qryDataUNION.PersonID
GROUP BY tblPersons.Name, qryDataUNION.PersonWeek, qryDataUNION.PersonYear;

The "straight" JOIN will exclude persons with no values in the week.

The group by will group the data into one week showing both the data1 and data2 PersonValue. For the multiple weeks here MAX() is used, but AVG (average) could also be appropriate.

Getting the idea?

Nic;o)

[SADude]

That helped tremendously - it's been so many years since I last tried my hand at database work I had completely forgotten about the uses of the union clause. Thanks for all your help!

[nico5038]

Glad I could help and success with your application !

Nic;o)