One Table has no data the Other does 1

[cretanion]

I have 2 tables. Both Tables are linked together by one field called Company. I run a monthly report based on Company and Month. In this instance, first time it has come up, the Table "Resources" has no data in any fields, but the other Table "Projects" does. I have a report that Groups the Company and Month, then adds the total values of both tables together for that Company and Month. For some reason in the report if the "Resources" Table does not have data in it, it does not add or display in the report. If I add data to any field in Resources, as a test, it will display along with whatever data is in the Projects table.
I was thinking that maybe I need to have a nodata or something setup in the Report Event Procedure if for some reason in this case there is nodata to add, but I don't know how to do this. Utterly confused,thanks in advance.

 

[randysmid]

Hi,
Your report should be based on a query, and you can set the relationships in the query to give you ALL the records in the Company table, and only those records in the Resources that matches it. Even if there are no records for that month in the Resources table, it should still give you a report.
HTH, Randy

 

[cretanion]

This is hard to explain so bare with me.
The 2 tables,
1.) Resources ( Created a query from this table)
2.) Projects (Created a query from this table)

Sample of the Tables
Resources Projects Companies
Desktops Doctor 110
Laptops Nurses 120


Each has a Grand Total field in their own queries. If I don't use a Company in the Resources table like 110, but I used 110 in the Projects table, then I don’t get it to add. Obviously the Resource table is not only null, but there is nothing to add, so is it MS Access that does not know how to calucalate a non existant field?

(Simple version)
This is what I got now Resource + Projects = Total
But if I got Resource(Sorry dude there is nothing) + Projects = nothing displays

 

[randysmid]

Hi,
Let me try to explain my answer to you. In Access, each report should be based on a query. You mentioned you have two queries and this is ok. But, you should create a 3rd query that uses your two queries as input (instead of the actual tables). Obviously, you want to show all rows from Projects, right? And, you only want to show the rows from Resources that match, right? Well, if you right-click on the line connecting your two queries, you will see an option to set Join Properties. This is where you can tell Access you want all rows from Projects, and those rows from Resources that match. In any event, this is the place where you tell Access what data you want displayed on the report.
HTH, Randy

 

[cretanion]

I agree with your statement, I don't want matches or equal, I want to see all the data regardless. I think that MS Access is not capable of doing this?????

Company 110
Resources Month Quarterly Use Total
Nurse April $1.00 2 $2.00

Company 110
Project # Month Cost Hours Worked Total
1111 April $2.00 2 $4.00

This scenario above works great for the Report. BUT...If the top part Resources information were not there it would not display in the Report.
Is MS Access capable of displaying information that is not equal or matches. I cannot find an answer to this. Thanks again.