Query criteria Count accross rows 1

[omooreirl]

Hi all

I have a table with 13 columns. Column 1 is my primary ID. The other twelve columns are all weights measured against the primary ID.

I need to create a query to count against each primary ID, the number of weights that meet a certain criteria. I can create the SQL for the count for single columns, but not accross a row. Any help is appreciated.

OM

 

[JBinQLD]

G'day om,

in query designer add a column of RowTot: col2+col3....?

you may need to look into the nz function if some of the data is null

Hope this helps,

JB

 

[PHV]

Do the stats against a normalization union query:
SELECT [primary ID], 'weight01' As Name, [weight 1] As weight FROM your Table WHERE [weight 1] Is Not Null
UNION SELECT [primary ID], 'weight02', [weight 2] FROM your Table WHERE [weight 2] Is Not Null
...
UNION SELECT [primary ID], 'weight12', [weight 12] FROM your Table WHERE [weight 12] Is Not Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

PHV has suggested what is now known in tek-tips as a normalizing union query. It fixes a badly designed table.
Your table is not normalized. Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your table violates the first normal form(step). Your 12 columns of weight measures have a repeatitive column heading - weight, weight, weight, etc. - no matter what nickname you gave them. So you have a category, weight, which can be a single column. What would happen if in the future you need to add 300 more weight measures? Add 300 more columns? Access tables only handle up to 256. Also if you don't measure a certain weight you'd have blank cells in your table. Not acceptable in relational database design. This causes variable length records.

Fixing your table will solve this count problem and future ones.

 

[omooreirl]

Thanks for the reply guys.

fneily - Funny you should mention the Fundmentals of Relational Database Design as I read it last night! As far as I can work out my table satisfies 1NF, 2NF and 3NF. Maybe if I give you a little more detail about the data. I have an utomated weighing scales that weights 12 capsules evey approx 0.7 seconds. The primary ID is the instance of weighing and my results from the 12 weighing cells are weights 1 to 12. I have 146474 instances of weights, with 12 weight readings in each instance giving me over 1.75 million weights to analyse. I believe 1NF is satisfied because none of the weights are atomic. I belive 2NF is satisfied as all the weights are dependant on the primary key. I belive 3NF is satisfied as none of the weights depend on each other, only the primary key (mutually independant). Please tell me if you think I am way off target with this assessment.

jbinqld - are you suggesting an append query?

PHV - you're suggesting making 12 distinct tables yes?

Thanks
Owen

 

[PHV]

PHV - you're suggesting making 12 distinct tables yes?
NO !
I've suggested ONE query based on the union of TWELVE select instructions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I have installed weighing systems at manufacturing sites that store each weight in its own record. We generally weigh 10 items every half hour but don't have 10 separate fields for the weights. If a pointy-haired-boss wants 12 items weighed each half hour, I wouldn't want a table with fields for 10 values. Are values are stored in a SQL Server database but reported through Access and web interfaces.

PH's solution uses your existing table but normalizes it with a union query.

Duane
Hook'D on Access
MS Access MVP

 

[JBinQLD]

Hey Owen,

Nope, not an append at all, just a calculated column in your query mate. Literally just + the columns together to get your result.

SELECT (MyTable.WeightOne+MyTable.WeightTwo) AS TheTotal FROM MyTable.....?

Good Luck!

JB

 

[JBinQLD]

The OP does mention that 1 "weighing session" = 1 set of 12 cells. i figured that meant a machine that spits some kinda serial output along the exact lines Owen is using to replicate the scenario?

The machine outputs a timedate and the values of each 12 cells every 0.7 seconds. I cant see a problem with his data model. The figure 12 seems physical fact rather than a conceptual one in this instance?

happy dayz

JB

 

[fneily]

The referenced article is just one of many people should read to understand normalization. And to get someone to explain it in person. The article is a synopsis.
"I believe 1NF is satisfied because none of the weights are atomic." First form has a few parts. One is that the data IS Atomic. Maybe you mistyped. It also has to do with repeatitive column headings, as I explained. Example:
Sunday, Monday, Tuesday. Most people see this as individual headings. No. They are days. So it's really day, day, day. This is a category. It could also be a table and you can't have a table within a table in relational databases. Violates first normal form. The first form also explains the consequences of such design, blank cells and variable length records. I noticed that you didn't address what would happen if you had 11 readings. Or if the machince/process expanded to 13(or a million) readings. Your table couldn't handle it. It would have to be changed.
"I belive 2NF is satisfied as all the weights are dependant on the primary key." The 2NF is for MULTI-field primary keys. Usually, not always, after coming out of 1NF, you'll have a multi-field primary key. So all non primary key fields must be related to ALL parts of the multi-field key.
3NF has to do with a single field primary key. Same explanation as for 2NF.
These steps must be done in order.
So for your data collection, there would be another field designating the instance/session. This would then let you create the 12 separated records. Or 1.75 milion records.
For more theory, read Ted Codd's, creator of normalization, papers.
But PHV solution will work.
JBinQLD - "The figure 12 seems physical fact rather than a conceptual one". 12 was conceptual first then became fact. It's arbitrary.

 

[omooreirl]

JB - Correct. 12 is physically the number of weighing cells on the machine and 12 capsules are weighed at any point in time.

JB - Your formula is for summing columns in a row as opposed to counting the number of values across columns in a row that meet a certain criteria.

PHV - Union query created. I used the resultant normailised "name" column in another query that used SELECT DISTINCT to count the instances.

Thanks for you help.
Owen

 

[omooreirl]

Thanks for the information fneily.

I've plenty to think on.

Owen