Report to pull data from different tables

[BinaryGirl23]

Hi Everyone : )

I hope that this question is actually something easy in disguise. Re: Access 2003 I have a report that displays information about which of our employees worked at a given job over a period of 2 weeks, and displays how much we paid them for their work at that job (using DSUM to calc all those records within that 2 week period). This works just fine.

The problem is that sometimes our guys work at these little side jobs. For a long list of reasons, we've decided to track these jobs separately and in another table. The report needs to show the following:

Employee JobName ContractCost SideJobCost
(labor table) (labor table) (dsum from labor) (dsum from extrajobs)

The WHERE statement on the contract and sidejob costs say "WHERE [employee] = employee.value AND [jobname] = jobname.value". So I'm saying to pull a list of everything they worked at for a 2 week period, then DSUM all the values to get one cohesive entry. This works fine except on certain occasions for the SideJobCost.

Consider this. Employee A works 8 hours doing a sidejob (found in another table) but he never works at this job, in that period, in the Labor table. So the report will only be able to DSUM the calculations for jobs that it CAN find in the report (which is pulling from the labor table, not the 'extrajobs' table), because I'm querying the values that are displayed in the report.

I hope this isn't confusing lol What I ultimately want is to be able to tell the report to say "Find all the jobs that people have worked at on both the labor table, and the extrajobs table" and DSUM the values to create a total.

Anyone with any info would be a tremendous help. It sounds easy enough but I can't figure this out : (

-Cathy

[puppydogbuddy]

Hi Everyone : )

I hope that this question is actually something easy in disguise. Re: Access 2003 I have a report that displays information about which of our employees worked at a given job over a period of 2 weeks, and displays how much we paid them for their work at that job (using DSUM to calc all those records within that 2 week period). This works just fine.

The problem is that sometimes our guys work at these little side jobs. For a long list of reasons, we've decided to track these jobs separately and in another table. The report needs to show the following:

Employee JobName ContractCost SideJobCost
(labor table) (labor table) (dsum from labor) (dsum from extrajobs)

The WHERE statement on the contract and sidejob costs say "WHERE [employee] = employee.value AND [jobname] = jobname.value". So I'm saying to pull a list of everything they worked at for a 2 week period, then DSUM all the values to get one cohesive entry. This works fine except on certain occasions for the SideJobCost.

Consider this. Employee A works 8 hours doing a sidejob (found in another table) but he never works at this job, in that period, in the Labor table. So the report will only be able to DSUM the calculations for jobs that it CAN find in the report (which is pulling from the labor table, not the 'extrajobs' table), because I'm querying the values that are displayed in the report.

I hope this isn't confusing lol What I ultimately want is to be able to tell the report to say "Find all the jobs that people have worked at on both the labor table, and the extrajobs table" and DSUM the values to create a total.

Anyone with any info would be a tremendous help. It sounds easy enough but I can't figure this out : (

-Cathy

It seems like all you need to do is wrap the null to zero function around ContractCost and around SideJobCost. That way, if one or the other is null, the appropriate cost column will show 0, which will calculate correctly in the DSum.

nz([ContractCost],0) nz([SideJobCost],0)

[NeoPa]

I'm more interested in the FROM clause of your SQL (JOIN types etc). Perhaps you could post all the SQL and we can see if there's anything there that might be complicating the task.

[BinaryGirl23]

I guess I was being a bit unclear with my last post : ( I do use NZ to prevent null values in all of my reporting. The problem that I'm having is that if an employee works doing only those "extra jobs" and none of the "regular jobs" then the report won't find it.

So here's how the report is reading the code: 1) Report opens up and OnOpen it changes the recordsource to one of several queries (all relatively similar) 2) The report now fills in data to the textboxes (I'm not displaying the exact SQL, this is just to give an idea):

Employee || JobName || Normal Cost || Special Cost
(from labor tbl) || (from labor tbl) || (DSUM) || (DSUM)

In each of the DSUM arguments I'm querying, I'm saying =DSum("NormalCost","LaborQuery","[Employee] = employeename.value AND [jobName]=Job.value") The variables may be slightly different but you get the idea, I'm just telling the report to dsum all values from everything it finds in the report (from the LaborQuery).

So the problem is where I get to the Special Cost, because that data is located in another table, I change the DSUM "FROM" argument to reflect that other table. But I still need to specify the WHERE argument, this is where things get problematic. The "Special Cost" WHERE argument is still trying to query based off the information provided on the report by saying [JobName] = job.value but if the employee never worked anywhere (in that time frame) on the Labor table...it wont find anything to query so it will be omitted. Does that make sense?

NeoPa, you might be right about the JOIN properties. When I tried to join the tables on the query, the query returned 0 results every single time (not for any reason I could determine), so I assumed that I did something wrong and hoped that there was just another way to do this. I WOULD LIKE to be able to have the EmployeeName and JobName fields populate based off of the criteria from BOTH tables, that would solve my problem completely but I can't seem to get it to work.

Thanks guys : )

-Cathy

[NeoPa]

...
NeoPa, you might be right about the JOIN properties. When I tried to join the tables on the query, the query returned 0 results every single time (not for any reason I could determine), so I assumed that I did something wrong and hoped that there was just another way to do this. I WOULD LIKE to be able to have the EmployeeName and JobName fields populate based off of the criteria from BOTH tables, that would solve my problem completely but I can't seem to get it to work.
...

Cathy, I can only help with the JOIN if I get to see it :S
A lot of the restrictions of records are actually in the FROM clause (on top of those in the WHERE clause).

[BinaryGirl23]

Cathy, I can only help with the JOIN if I get to see it :S
A lot of the restrictions of records are actually in the FROM clause (on top of those in the WHERE clause).

NeoPa, if my F-E database weren't 75+Mb I'd gladly upload it and have you look at it lol What did you mean by the FROM clause? Is it possible to DSUM from two separate tables/queries? If so, that solves my problem right there : )

Are there any examples of this? I looked on thescripts.com before I posted this plea for help but I wasn't able to find anything that was similar or somehow related.

[NeoPa]

What's the record source of the report?
It's basically the SQL of that that I'm talking about. I'm assuming it's going to be relatively uncomplicated. If not then it may be a show-stopper.
Big databases are fine - except no-one ever seems able to tell you where the problem actually is so you have to hunt forever before even finding the problem.

As a general rule (there are situations where this won't apply) it's best to populate the data within a report from a single recordset (Table; query; etc) which can be made up from various tables.

Let me know if this is practicable.`