Missing row in a query (2 Viewers)

[shawnacker]

I have what seems to be a fairly simple question.

There are two tables in my database. A and B. Each table has the same criteria: Name, Number, and Date.

I have created a formula in my query which will add the Number fields together when they both have the same name. My problem is that when there is not a row for a particular name on table B, the calculation does not list that row in the results.

I would like for my calculation to insert a zero in the space and add Number.

I have tried the Nz function, but this results in a missing name and date and just the numbers are shown.

How do I get the results to show a row where the name is only on one field and still make sure that it places a zero in its spot and add the zero and the other number together based on the name.

Thank you in advance for any assistance.

Shawn

 

[Jon K]

It will help if you can post the SQL statement of your query and illustrate your question with some sample data.
.

 

[shawnacker]

Sql

Thanks for the response...here is my SQL. Using this statement, all data is returned, BUT the Name and Date fields are blank for any rows where Table A does not have a value. Number1 does have a zero in the field and the calculation does work properly, I just can't see what the name is for the row.

SELECT TableA.Date, TableA.Name, Nz(TableA.Number,0) AS Number1, TableB.Number AS Number2, [Number1]+[Number2] AS Total_Number

FROM Table1 RIGHT JOIN Table2 ON Table1.Name = Table2.Name

GROUP BY Table1.Date, Table1.Name, Nz(Table1.Number,0), Table2.Number;

Thanks again!!!

 

[Jon K]

SELECT TableB.Date, TableB.Name, Nz(TableA.Number)+0 AS Number1, TableB.Number AS Number2, [Number1]+[Number2] AS Total_Number
FROM TableA RIGHT JOIN TableB ON TableA.Name = TableB.Name;


Notes
1. As you used TableA Right Join TableB, you have to select Date and Name from TableB instead of TableA.

2. Instead of using Nz(TableA.Number,0) as Number1, use Nz(TableA.Number)+0 AS Number1, which returns a numeric field instead of a text field.

3. Remove the Group By clause as you didn't use any of the aggregate functions such as Sum, Count, Max, Min.

4. Date is an Access function and Name is a reserved word. Better avoid using them as field names.
.

 

[shawnacker]

Thanks

Thanks for your help Jon. It makes perfect sense and of course works like a charm. You're the best!