Inner Join not returning all results 1

[cm80]

Hi,

I have a bit of a problem with an Access query. I don't think the solution will be very complicated but I can't quiet figure it out. The easiest way to explain it is to give an example:

I have three tables July Usage, August Usage and September Usage,
There is only 2 fields in each table, the user and their usage given in minutes.

Example data;

July Usage
Tom 2000
Mary 3000
Ed 1500

Aug. Usage
Mary 2500
Will 1000
Tom 2000

Sept. Usage
Tom 2000
Ray 1500
Mary 3000

I also have another table of users called User_Detail.

What I want is a list of all users and their usage however my query is just returning the users that appear in all 3 tables, ie. Tom and Mary.

The SQL view of the query is as follows:

SELECT User_Detail.corp_id,
User_Detail.name,
Jul_Usage.Total,
Aug_Usage.Total,
Sept_Usage.Total
FROM ((User_Detail INNER JOIN Aug_Usage
ON User_Detail.corp_id = Aug_Usage.[Corp ID])
INNER JOIN Jul_Usage
ON User_Detail.corp_id = Jul_Usage.[Corp ID])
INNER JOIN Sept_Usage
ON User_Detail.corp_id = Sept_Usage.[Corp ID];

If anyone could offer any words of advice on how to solve this, either using SQL or the QBE it would be much appreciated,

Thank you,
cm80

 

[KenReay]

Hi

In the query grid, right click on the line(s) joining the User Table with the usage tables. Change the rule to show all records from UserTable

Regards
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[cm80]

That worked perfectly. Thank you very, very much!!

While I’m at it...would you by any chance know how I could create a new field in the query that would be a total of the three months altogether?
Essentially it would be a derived field.
I was trying to do it doing the QBE but it keeps giving me an error because I’m not sure what to enter in the ‘Field’ part of the derived field,

Thanks again for all the help,

Cm80

 

[KenReay]

Hi

You would normally do this using an aggregate query, based on the query you have been building above,

Alternatively if you are displaying in the query results in a Report, just put a textbox control in the Report Footer with source of =Sum(thenameofyoufield)

Regards
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[cm80]

Again, thank you very much! I got that to work using the following expression.

TOTAL: [aug_usage]![Total (Minutes)]+[july_usage]![Total (Mins)]+[sept_usage]![Total (Mins)]

Thank you.

I have one final question I was hoping you might be able to help with,

With the expression above, a total is only returned if there is a value for all three months.

So using the example above, it will return a total for Mary and Tom, but no one else because they have null values for some months.
I have looked for the answer myself and have tried to use the find and replace function, however it won’t work with the query.

Do you know of any other way that I could replace null values with a 0.

Thank you very much again,

Cm80

 

[KenReay]

Hi

The Nz() function is useful in this context:

TOTAL: Nz([aug_usage]![Total (Minutes)],0)+Nz([july_usage]![Total (Mins)],0)+Nz([sept_usage]![Total (Mins)],0)

Regards

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use