Group by Problem

[smashclash]

I'm having trouble with a query for my payroll reports. The query has two tables. The first one is simply #'s: 0-29 each in a different row. These #'s represent a site aka an outside office. The second table has all of the payroll information in it. There are 3 columns. There is a column for the amount of pay, the type of pay it is (H-is used for hourly, S-is used for salary), and which site it should go to (0-29 are the site numbers.)

What I'm trying to do is create a query that lists every site in Column A, and the amount of pay for each site in Column B, except it should only show the payroll totals for "H" - hourly salary. There is a 2nd query that does the same thing except it pulls only the "S" - salary.

I thought this would be a simple task and setup my query. I put both tables in the query and linked them together by "include ALL records from site and only those where they are equal from data". (Site is the name of the site table, and data is the name of the table with all the payroll information.) In column 1 in the design mode of the query it is pulling the sites, in column 2 it is pulling the amount of pay and in column 3 it is pulling only the "H" - hourly type salary. The problem I run into when I do this is that the query is no longer including ALL the sites (all 0-29) rows. It now only shows data where there is amounts <>0. So for example, it shows totals for sites 0-8, skips 9-10, then shows 11-29. The amounts for 9-10 equal 0 but I still want to show them. When I remove the "group by "H"" feature the query works and shows all the sites. However it doesn't separate the Hourly from the Salary wages.

I've checked to make sure that both tables are set as #'s for the Sites linked from the site table and the Site linked from the Data table. For some reason the new group by for the "H" and "S" do not pull in all the sites.

Anyone know what I could do to get this to work? How do I get it to include all the sites again? There has to be a way I can have the query grouped by H or S and still show all the sites.

 

[Andrew Fergus]

Hi smashclash,

I too was surprised your query didn't work but I have found a workaround. In your query , change the formula for the pay type column to the following (using your own variable name) :

IIf(IsNull([type]),"H",[type]) - change "H" to "S" for the salary query and use your own variable name.

In the amount column, enter this :

IIf(IsNull([amount]),0,[amount]) - also remember to use your variable name.

Let me know if this works.

Cheers, Andrew.

 

[smashclash]

hmmmm, I'm not sure what exactly is going on with your suggestion Andrew. I tried it but it didn't do anything different. Still getting the same results.

For the record here is what i have:

Column 1:

Field: Site
Table: Site
Total: Group By


Column 2:

Field: Earnings Sum: Sum([regular earnings]+[other earnings])
Table:
Total: Expression


Column 3:

Field: Payroll Class
Table: Data
Total: Group By
Criteria: "S" (S = salary)


Also the arrow is point from the "Site" in the Site table to "Dept Worked" in the Data table. It is setup to include all records from Site and those in Data where they match site.

Could the formula in Column 2 somehow be a problem? The information is coming from the Data table.

 

[Andrew Fergus]

Column 2:

Field: Earnings Sum: Sum([regular earnings]+[other earnings])
Table:
Total: Expression

Try changing this peice of code to this :

Field: Earnings Sum: Sum(IIf(IsNull([regular earnings]),0,[regular earnings])+IIf(IsNull([other earnings]),0,[other earnings]))

HTH, Andrew.

 

[Andrew Fergus]

Oops - I forgot to mention that the "S" and "H" criteria also need the isnull test, otherwise it won't work. What is happening is that where there are no records then Access is doing nothing, so by changing your formula to include the isnull part, we are instructing Access to populate the field in the absence of a record so that you get a complete list of sites.

So, you might also want to try changing this part :

Column 3:

Field: Payroll Class
Table: Data
Total: Group By
Criteria: "S" (S = salary)

To this :

IIf(IsNull([Payroll Class]),"S",[Payroll Class])

HTH, Andrew.

 

[smashclash]

Andrew thanks for your help and patience with me. I think I should have done a better job explaining my situation though because the solutions you provided aren't doing anything different and I think I know why.

I believe your formula works by looking for the amount of pay in the Earnings column and if there is no data in there to place a 0. This would in turn create a value and now Access will pickup the site because the data in the row is no longer Null.

I have complete faith that this would work if it was the case. However, the problem is that the Site does not show up on the Data table when there is no payroll data for it. Meaning, if Site 001 had no $'s for Hourly pay, the Row of data that would normally contain information is not on the report because there isn't any payroll to report.

What I'm trying to do is create a table thru a query that will include those sites that don't have any data on the the Data table as well as the ones that do. I hope this clears up my problem.

Thanks again.

 

[Andrew Fergus]

Hi, now I'm curious because I set up a table of sites (0-29) and a very small data table (with 5 or 6 bits of data) and my query (as written previously) works every time, i.e. it shows every site including all of the ones with a nil value.

I know you mentioned this in your first post but have you checked that you have forced the link between the tables to include all records from the sites table?

Also, are there any other fields in your query other than the 3 you outlined previously?

Lastly, are you using the "isnull" test on both the value and the payroll type?

My query looks like this - is yours the same?

Column 1:

Field: Site
Table: Site
Total: Group By


Column 2:

Field: Earnings Sum: Sum(IIf(IsNull([regular earnings]),0,[regular earnings])+IIf(IsNull([other earnings]),0,[other earnings]))
Table: Data
Total: Expression


Column 3:

Field: IIf(IsNull([Payroll Class]),"S",[Payroll Class])
Table: Data
Total: Group By
Criteria: "S"
{change both "S" values to "H" for the hourly query}

Let me know if you are still having problems with this.

Andrew.

 

[smashclash]

Hi, now I'm curious because I set up a table of sites (0-29) and a very small data table (with 5 or 6 bits of data) and my query (as written previously) works every time, i.e. it shows every site including all of the ones with a nil value.

I know you mentioned this in your first post but have you checked that you have forced the link between the tables to include all records from the sites table?

Yes, there is a forced link.

Also, are there any other fields in your query other than the 3 you outlined previously?

Yes there are other fields in the data table. However they just relate to other types of payroll. For example there is a column for Dental, Life Insurance, etc. None of the cells are blank, if there is no value a "0" is there.

Lastly, are you using the "isnull" test on both the value and the payroll type?

My query looks like this - is yours the same?

Column 1:

Field: Site
Table: Site
Total: Group By


Column 2:

Field: Earnings Sum: Sum(IIf(IsNull([regular earnings]),0,[regular earnings])+IIf(IsNull([other earnings]),0,[other earnings]))
Table: Data
Total: Expression


Column 3:

Field: IIf(IsNull([Payroll Class]),"S",[Payroll Class])
Table: Data
Total: Group By
Criteria: "S"
{change both "S" values to "H" for the hourly query}

Let me know if you are still having problems with this.

Andrew.

Yes, I copied and pasted your formulas into my database and I'm still getting the same results. If you think it would be beneficial I could change some of the names in the database and email what I have to you. Perhaps you will see a mistake?

My email address is: smashclash@yahoo.com Send me an email and I'll reply with the database. Thanks!

 

[Andrew Fergus]

Hi

Before we send any e-mails (to the exclusion of other potential helpers in this forum), have you tried having just the 3 columns in the query (i.e. dept, class and value) and deleting the other columns (i.e. dental etc.)? If you strip your query down to the bare minimum and reintroduce the extra columns once you've got the basics working, then it might isolate what is causing the problem.

Post back if that doesn't work - I won't let this one go!

HTH, Andrew.

 

[Andrew Fergus]

BTW, is this an "update" or an "aapend" query that you are trying to use? Now I'm getting confused ..... my test query was as a simple "select" query.
Andrew