Sum across a record

[thomer]

Very new to Access - I have a dataset that unfortunately is structured where an individual item is across one record (eg one record = one row with a number of columns) and it isn't feasible to restructure it into a more database friendly format prior to being brought into Access. I'm looking to count how many occurrences of an entry occur across one record (eg "Red" was selected 23 times, "Yellow" was selected 14 times, and "Green" was selected 5 times in that row/record) and have no idea how to go about doing so.

Access 2010 if it matters - any help is appreciated!

 

[pbaldy]

You might consider a UNION query to normalize the data, enabling you to use normal methods for querying it. Failing that, a pretty tedious calculation:

IIf(Field1 = "Red", 1, 0) + IIf(Field2 = "Red", 1, 0)...

You could also adapt something like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[thomer]

The one issue with normalizing it is that I'm generating about 3000-4000 records a month and each has about 80 fields (so it would be a lot of individual records for Access to handle very quickly). I'm also effectively doing some analysis on it across a record, and across all records for that particular field (so do not know if normalizing it would make that difficult).

 

[pbaldy]

Well, it's your baby, so use either method you want. Access is designed to handle normalized data, so analysis across a record will be more tedious.

 

[thomer]

I'd rather normalize it but I don't know enough about Access to know if it's really worth the trouble it to do so every time I update the database (several times a week).

 

[pbaldy]

What I suggested was a UNION query to normalize the data. That could be applied during import to put un-normalized data into a normalized table, or after import to run reports against.

 

[thomer]

So really quick (because I literally know nothing about Access) - how would I structure my query if I don't normalize the data (query so far below):

SELECT Responses.Status, Responses.[ID], Responses.[Create], Responses.[(F1)], Responses.[(F2)], Responses.[(F3)], Responses.[(Q1)], Responses.[(Q2)]
FROM Responses
WHERE (((Responses.Status)="Complete") AND ((Responses.[(F1)])="User" Or (Responses.[(role)])="Admin") AND ((Responses.[(F2)])="August" Or (Responses.[(month)])="September"));

 

[pbaldy]

Assuming the F fields repeat, this type of thing

SELECT Responses.Status, Responses.[ID], Responses.[Create], Responses.[(F1)]
FROM Responses
UNION ALL
SELECT Responses.Status, Responses.[ID], Responses.[Create], Responses.[(F2)]
FROM Responses
UNION ALL
SELECT Responses.Status, Responses.[ID], Responses.[Create], Responses.[(F3)]
FROM Responses

Basically all the SELECT's includes the static fields and then each includes a different repeating field (or fields). If appropriate each SELECT can have a WHERE, or you can treat the UNION like a table and run queries against it.

 

[thomer]

Is that how to normalize it or how I would set it up to do the IIf(Field1 = "Red", 1, 0) + IIf(Field2 = "Red", 1, 0) idea?

 

[pbaldy]

Done correctly that effectively normalizes the data. You could run a totals query against it:

SELECT Status, Count([(F1)] As HowMany
FROM QueryName
GROUP BY Status

and get counts by color:

Red 123
Green 456

 

[thomer]

I'm probably not going to normalize it right off the bat (mostly because I'm still finding out how I want to cut/analyze the data) so how would I structure the query to do it the tedious way (if I'm looking across a single record to see how many times "Red" is used - per my first comment).

Thanks.

 

[pbaldy]

Per my first response:

IIf(Field1 = "Red", 1, 0) + IIf(Field2 = "Red", 1, 0)...

either in a report or as a calculated field in the query. In SQL view of a query:

IIf(Field1 = "Red", 1, 0) + IIf(Field2 = "Red", 1, 0) AS TotalRed

 

[thomer]

Where would I insert that into the SQL query I copied above (sorry, complete scrub at this).

Thanks!

 

[pbaldy]

After the last field, with a comma:

SELECT Responses.Status, Responses.[ID], Responses.[Create], Responses.[(F1)], Responses.[(F2)], Responses.[(F3)], Responses.[(Q1)], Responses.[(Q2)], IIf(Field1 = "Red", 1, 0) + IIf(Field2 = "Red", 1, 0) AS TotalRed
FROM Responses

Of course, you'd have to insert the correct field names.

 

[thomer]

When I run the query it gives me a pop-up asking me to enter the value for Q1 and Q2. Actual SQL query below:

SELECT Responses.Status, Responses.[Internal ID], Responses.[Created At], Responses.[(storenum)], Responses.[(role)], Responses.[(month)], Responses.[(SQ1)], Responses.[(SQ2)], Responses.[(SQ3)], Responses.[(TQ1)], Responses.[(TQ2)], Responses.[(TQ3)], IIf(SQ1 = "Yes",1,0)+IIF(SQ2="Yes",1,0) as TotalYes INTO Results
FROM Responses
GROUP BY Responses.Status, Responses.[Internal ID], Responses.[Created At], Responses.[(storenum)], Responses.[(role)], Responses.[(month)], Responses.[(SQ1)], Responses.[(SQ2)], Responses.[(SQ3)], Responses.[(TQ1)], Responses.[(TQ2)], Responses.[(TQ3)]
HAVING (((Responses.Status)="Complete") AND ((Responses.[(role)])="User" Or (Responses.[(role)])="Admin") AND ((Responses.[(month)])="August" Or (Responses.[(month)])="September") AND ((Responses.[(SQ1)])="Yes" Or (Responses.[(SQ1)])="No"));

 

[pbaldy]

Your actual field name appears to have inadvisable parentheses in it, so it would have to be the same as before, [(SQ1)]

 

[thomer]

Yep, how it pops out unfortunately... and that fixed it. Thanks!

 

[pbaldy]

No problem, and welcome to the site by the way!

 

[marlan]

Hi,
As written above normalization is the proper way for Access and other relational DBs to handle these types of tasks.
Please see if this thread could help you create the query pbaldy suggested, that will help you normalize your data.

ATB!