Table Analysis

[Sheba]

I want to throw this one out there...



My form has about 50 option group controls and now that it has been used to enter hundreds of records, the stakeholder wants me to "find out" how many "Yes"s and how many "No"s occurred for each option group in the table. This request also applies to the non option group controls i.e. checkboxes. How many times each one was checked and how many times each one was not checked)

Is there a smart way of going about this please?

[orange]

Can you show us some sample data or post a copy of your db? Why are you using 64 bit Access?

You must have stored data in the related tables - can you tell from your saved records?

[ranman256]

You need a table that has all the 'options' and their choices.
if 1 option grp is:

gender option
-------
boy
girl
unk

you need a lookup table tOptGrps, that has:
[optChoice],[optGrp]
boy,gender
girl, gender
unk,gender
smoke, smoking
non-smoke, smoking


when you have all the questions stored, then run it agains the data values that were picked,
join the tData table with tOptGrps and count the choices picked.

[Sheba]

Hello, thanks for your response. Please find attached, a stripped down version. I actually have more than fifty questions that require an answer.
Thanks much.

[Sheba]

Thanks ranman. I am trying to understand your suggestions though they are not clear. The thought of manually creating the table(s) makes me shudder. I have about 10 other tables to do the same thing for.

I could start by exporting the table...

[Sheba]

hey Orange, just seen your note. I am using Access 2010 32 bit. Not sure how I came about the 64bit label

[ranman256]

The way your tables are designed, youd need a table of the questions you want pulled (analysed) that looks like

Tbl	FieldName
Paediatrics	DiltdPupl
Paediatrics	UnreactPupl
Paediatrics	CushingRf
PostCare	Hypotherm
PostCare	IntubMechVent
PostCare	paraHypoth
PostCare	paraIntub

Then EITHER ,

build a macro, (easiest) (no table above needed)
this macro would have lots of queries, ...an append query for EACH one of these fields to count and put the results to a 'reporting' table.
OR
some code that would scan this list and query the TBL for the counts in a date range. (difficult if you dont know VB)

[Sheba]

thank you for this ranman. My VB skills are not advanced yet so I'm looking for the simplest solution to this issue.

[June7]

You just want to tally the Yes/No data and summarize by some category values - how many? I expect can be done with queries, probably will involve long calc expressions.

This is not a normalized data structure and will likely cause you much frustration.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Instead of Tocolytic? better would be IsTocolytic.

[Sheba]

hey June7, I've been away and only just returned. Thanks for your feedback. Frustration? sounds familiar. Way I see it, I reckon tis a small price to pay for the table being unNormalised. I'll look into fixing the special characters.

First, without using a query, how do I get a column to receive the difference between two Date/Time columns? The data in these two columns are FirstTime and LastTime? Could you advise please?

Thanks

[Sheba]

I added a text box control to my form to calculate the difference between these two and also added a new column to the table but how do I populate this new column with the difference for existing records? I also have table validation rule: [spentTime] = [lastTime] - [firstTime] and bound the new column to [spentTime]

Sorry I know this is basic but having been away, it is taking me a bit longer to 'get back into things'

[June7]

Options are:

1. don't save, calculate when needed

2. vba code

3. since you have 2010, calculated field in table

Review: http://allenbrowne.com/casu-14.html

[Sheba]

Thank you June7 . I'm reading up the links you provided.

[Sheba]

Okay, I put this code in the Query row

Minutes: DateDiff("n",[firstTime],[lastTime])


but I'm getting error: Syntax error (comma) in query expression 'Coronary.[DateDiff("n",[firstTime],[lastTime])]'

what could possibly be wrong with this syntax now...?

[Sheba]

Oh dear, this don't look right!

I changed the syntax to: Minutes: 'Coronary.[DateDiff("n",[firstTime],[lastTime])]'
and got this:

Minutes
Coronary.[DateDiff("n",[firstTime],[lastTime])]
Coronary.[DateDiff("n",[firstTime],[lastTime])]
Coronary.[DateDiff("n",[firstTime],[lastTime])]
Coronary.[DateDiff("n",[firstTime],[lastTime])]
Coronary.[DateDiff("n",[firstTime],[lastTime])]
Coronary.[DateDiff("n",[firstTime],[lastTime])]
Coronary.[DateDiff("n",[firstTime],[lastTime])]