smashclash
Board Regular
- Joined
- Nov 24, 2003
- Messages
- 126
- Office Version
- 365
- Platform
- Windows
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.
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.
