query results table

[MrZimma]

query results table-Merged thread

I have a query_ReimburseResult
that counts the yes/no answers in field Reimburse from tbl_Survey.

How do I take the results from the query and put it into a field in another table?

I have tbl_Result and a field called ReimburseResult. I set the type to long integer and then what?

thanks!

 

[Malcy]

Hi
I think I would be wondering why you want to try and store a calculated value in a field. You can easily recalculate the value any time you want and it would be more accurate since if anyone amends the data they are unlikely to remember to amend the calculated value - so you will have a discrepancy.
Any help??
Good luck

 

[MrZimma]

Because I want to consolidate the results from all the queries into a report, and I need a place to store the information.

 

[Malcy]

OK
How about having your query to collate the yes/no results and then using a conditional DCount to get the results. I think that would work.
If you have to go the table route then you could set up a make-table query from a totals query I think, although I do recall getting into difficulties myself at one point doing this.
Any help?

 

[MrZimma]

OK, here is another point I am stuck. I want to count multiple records with a single query. Here's the SQL View of the one I have now, that counts a field set. What I want to do is add more fields (say, fields A, B and C to the SQL statement but I suck with syntax.

SELECT [survey records].[Employer reimbursement], Count([survey records].[Employer reimbursement]) AS CountOfReimbursement
FROM [survey records]
GROUP BY [survey records].[Employer reimbursement];

What I'm trying to do is take other yes/no fields and consolidate the counting query so that there will be one query instead of 37. What is the correct syntax to do this?

thanks!
matt

 

[MrZimma]

count multiple yes/no records with a single query

OK, here is another point I am stuck. I want to count multiple records with a single query. Here's the SQL View of the one I have now, that counts a field set. What I want to do is add more fields (say, fields A, B and C to the SQL statement but I suck with syntax.

SELECT [survey records].[Employer reimbursement], Count([survey records].[Employer reimbursement]) AS CountOfReimbursement
FROM [survey records]
GROUP BY [survey records].[Employer reimbursement];

What I'm trying to do is take other yes/no fields and consolidate the counting query so that there will be one query instead of 37. What is the correct syntax to do this?

thanks!
matt

 

[The_Doc_Man]

As long as you followed the rules carefully regarding Yes/No fields, you could do the following.

First, build a query that references all 30-something Yes/No fields with an IIF statement. Something like

SELECT ....,
IIF( [this-particular-field]="YES", 1, 0 ) AS this-particular-fieldCOUNT, ...;

Second, build a summation query that does the sum of all 30-something this-particular-fieldCOUNT.

If you tried to get "cute" and pack the Yes/No field (which is stored as a byte integer) with extra bits, you would get true chaos. BUT if you stayed within the guidelines and only used YES or NO for the fields (or TRUE or FALSE, works just as correctly), you could do as I suggested.

 

[Pat Hartman]

Your problem is really one of lack of normalization. The 30 yes/no fields should be stored as rows not columns. Access is a relational database NOT a spreadsheet. This is a common mistake. Search the archives and the net for articles on database normalization. In a properly normalized table, you could use a simple query. You wouldn't need 30 IIf()s:

Select TranType, Count(*) As CountOfTrans
From YourTable
Group by TranType;

 

[Pat Hartman]

Please don't post multiple threads asking the same question.

 

[MrZimma]

Sorry if I posted another thread on this topic, if I did it was accidental.

Anyway, I'm not sure what you mean by making them rows instead of columns. They are different questions that pertain to one record for each response.

And I went to About.com for an article on normalization. Would it be a good idea to set the questions as different tables that group related questions together? For instance, I have about 6 yes/no fields for a single question that list the preferred location(s) they would find convenient. I set them up as separate yes/no fields so that they could store more than one. Would it be smarter to set this question as a separate table? Is that what you were getting at?

Thanks,
Matt

 

[MrZimma]

Sorry to keep posting, but if you know of any good articles on normalization and sound database structure and planning, please post them,.

 

[Pat Hartman]

Start with this:
How to find information about designing a database in Access 2000
There is another good article at www.fmsinc.com You'll have to wade through their menus because I don't have the direct URL but they have many interesting articles.

Also, search for posts on survey and questionnaire databases here since those are most like what you are describing.