Need help creating a query

[MeginMurphy]

I have been trying to relearn Access and have a project due and just can't figure out how to create the query.

My output should be:

The data is in a table that contains duplicates of the location, address, city, state and employee names. This is very small snapshot of what my table looks like with sample data.

There are several different locations and states.

I am able to create a query that gives me a unique list of location, address, city and state but I am not able to get a headcount and not able to bring in the earnings to each of the class codes I need.

Are there other posts out there you can point me to or can anyone offer any suggestions?

Thanks,


Megin

[rpeare]

This is a non-normalized table structure which is going to give you problems. For instance, let's say Instead of ABC road, someone puts in ABD road, even though it may actually be the same location and it was just mistyped it's going to come through as a different line for the same location.

That aside you can get your employee count by doing something like (I'm assuming ID is the employee ID, which should be stored on your table and not the employee name, same with your job code, that, I assume is the employee's job title/class which should be stored in a separate table)

SELECT LOCATION, ID, Sum(EARNINGS) as TOT_EARNINGS FROM <TABLE NAME HERE> GROUP BY LOCATION, ID

This should give you a count of employees by location and their total earnings for that location, if you want it for a specific time period, etc you'd have to add criteria to the query but this is the basic aggregate query you want.

The next thing you'd need is a summary of this query to condense it to a number of employees and their total earnings for the three major locations so let's say you named the first query "qry_Summary_Pre" you would make a second query

SELECT LOCATION, Count(ID) as EmployeeCount, Sum(Tot_Earnings) as LocationEarnings FROM qry_Summary_Pre GROUP BY Location

The third part of this is how do you get your cost centers into this query and there are a couple of ways to do this. If you only have the five cost centers you've indicated in your sample you can include those columns in your qry_Summary_Pre by doing something like:

SELECT LOCATION, ID, Sum(EARNINGS) as TOT_EARNINGS,  Sum(Iif(Left([Class Code], 4) = "7539", [Earnings], 0)) as [7539 - Plant Ops] FROM <TABLE NAME HERE> GROUP BY LOCATION, ID

I didn't test this code so it may bomb but basically you're testing the class code string for the left four characters being your cost center, if they are equal you add the earnings, if it's not, you don't, then summing those values and giving it an alias (a different name) in this case "7539 - Plant Ops".

Then you can simply sum the values in your second query.

If you have more cost centers than your example, and it has a variable number of cost centers every time you run the report you may consider a crosstab query, be aware, however, that if you create a crosstab query you will likely not be able to base a report on it unless you're very careful about how you set it up.

[MeginMurphy]

Thank you so much for taking the time to detail a process! I iwll try it and see how it works.

Megin

[MeginMurphy]

After reading over your notes a few times this is what I came up with and it works! Thank you very much again!

SELECT qry_Summary_Pre.LOCATION, qry_Summary_Pre.Address, qry_Summary_Pre.City, qry_Summary_Pre.State, Count(qry_Summary_Pre.EMPID) AS Headcount, Sum(qry_Summary_Pre.[7539 - Plant Ops]) AS [7539-Plant Ops], Sum(qry_Summary_Pre.[7720 - Security]) AS [7720 - Security], Sum(qry_Summary_Pre.[8601 - Eng]) AS [8601 - Eng], Sum(qry_Summary_Pre.[8809 - Officer]) AS [8809 - Officer], Sum(qry_Summary_Pre.[8810 - Clerical]) AS [8810 - Clerical], Sum(qry_Summary_Pre.Tot_Earnings) AS LocationEarnings
FROM qry_Summary_Pre
GROUP BY qry_Summary_Pre.LOCATION, qry_Summary_Pre.Address, qry_Summary_Pre.City, qry_Summary_Pre.State
ORDER BY qry_Summary_Pre.City;