Simple (Should be) Count IIF Query

[Steven.Allman]

I am stumped... shouldnt be as hard as I am making this but someone is going to laugh and point out my error hopefully.

Table has employee records
includes a 2 week scheduler. Two real options on scheduler. Working, or Off.
So here are the fields I want in a report.

Grouped by table.department and table.subdepartment
monday totals for working
monday totals for off
tuesday repeate
wed
thurs
fri

What i am getting, is an exponential issue instead of showing me
HR , HR1
mon 1,4
tues 2,3

Im getting
Monday 1,4
monday 2,3
tueday 1,4
tuesday 2,3

If I add Wednesday, then I get three results for three days, 5 days I get 25 results

Ive tried doing a single query for each day, and each query works great, then doing another query which looks at my queries and I get the same result.
I went straight into a report and just built a field =sum(iif and again, if I just use Monday, it works great... the second I add a second field for tuesday, instead of 2 results I get 4, then 8, then 16, then 32.....

I am sure i am missing something super simple here.
But I need a report in the end, or a query i can base my report off of, that says
HR
HR1
Monday 3 people off 2 works
Tuesday 2 people off 3 works
HR2
Monday 1 and 2
tueday 2 and 1
Warehouse
Shift1
Monday 2,3
Tuesday 1,4


Shift2
Etc

[Gicu]

Can you show your table and or query? Looks like you include in the grouping something that is unique. I suspect you table is not normalized...

[Steven.Allman]

Database8.accdb

See attached db.. thank you

[mike60smart]

Hi

You only have 1 table which looks like it is based on an Excel file.

As Vlad said you need to normalise this one table.

[Steven.Allman]

its actually from a Sharepoint List, but should act the same as any Access Table.
Normalize? What am i missing?

[CJ_London]

data isn't normalised and field names don't match description (department/subdepartment) v staff/division

think the first thing you need to do is normalise your data. If for some reason you can't, use a union query to normalise the view. Given the steps in you ID column, I suspect the original source of the data may be normalised. You can probably union your day queries



SELECT * FROM tele1Mon
UNION SELECT * FROM tele1Tues
UNION SELECT * FROM tele1Wed
etc

Once the data is normalised, a simply group by query should meet your requirements

[Gicu]

As I thought, the data is not normalized making it difficult to achieve what you're after. But as a work-around I have used a union query to normalize it for the purposes of this report and created a crosstab query that should give you what you want.

Cheers,
Vlad

[mike60smart]

Hi

A sharepointl ist is not the same as an Access Table.

In Access you would normally have a table to enter your Staff details

Another table listing Departments
Another table listing Divisions
Another table listing Ranks
Another table listing Status
Another table listing which allows you to enter Days Worked.

In your table Example the Days are going Horizontally (ie as in Excel)

In Access when you record Days Worked each Day is a Record in a Table.

As you can see you need more than 1 table to record your process.

[Steven.Allman]

this is protected information so i was using simpler terms in my example.
Division/Staff is Department/SubDepartment etc.
Also said "work/off" when its really telework/TARC... you can see i removed all names as well.

Splitting into two tables is not really an option as I mentioned this is a Sharepoint list which is where the end user will be making edits, in sharepoint not via Access backend.
I dont have true repeated data through the table, so normalizing through the table analyzer which wants to split my tables (list) which also wont work.

[Gicu]

Have you had a chance to look at my example?

[Steven.Allman]

Vlad, thank you so much.
I do have a follow up question though.
Should the union have 14xNumber of table.records (33 here so 462)= records?
Each record with 14 fields, Union Query generates a normalized tableset with one record for each?
Im not sure Im using to proper syntax but hopefully you understand what i am trying to ask.

[Steven.Allman]

Added the ID column to the Union and it worked perfectly. This gives me precisely what i am needing without splitting my list (tables).
MUCH appreciated VLAD!!!!

[Gicu]

Cheers,