Re: Query problem - How do I do this?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

If I understand correctly, a properly normalized table would help. You might
want to look at "At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. There is
a report in AYS that shows counts of each response value by question.

You may be able to normalize your table with a union query
SELECT 1 as Fld, Field1 as TheValue, Count(*) as NumOf
FROM tblTooManyFields
GROUP BY 1, Field1
UNION ALL
SELECT 2, Field2, Count(*)
FROM tblTooManyFields
GROUP BY 2, Field2
UNION ALL
SELECT 3, Field3, Count(*)
FROM tblTooManyFields
GROUP BY 3, Field3
....etc...

--
Duane Hookom
MS Access MVP
--

"No-One @shaw.ca>" <<> wrote in message
news:t0yie.1394600$Xk.1053060@xxxxxxxxxxx
>I have a db that has only a single table.
> Each record contains 55 fields.
> Most of the fields contain a value of 1, 2, 3, 4, or 5.
> The rest are text fields that will simply be printed.
>
> What I want to do is run a query that will provide me with a count each 1
> in field1 and each 2 in field1 and each 3 in field1 and so on for each
> field.
> So I want it to display like this:
> field1 countoffld1
> 1 6
> 2 11
> 3 7
> 4 6
> 5 20
>
> Now, I've been able to do the query but only on each individual field.
> Whenever I try to combine the queries I end up with a long listing that
> display mulitiple rows with field1 that contain a value of 1 and then its
> count beside that with field2 and its count beside that.
> So what I end up with something like this:
> field1 countoffld1 field2 countoffld2
> 1 1 1 1
> 1 2 1 2
> 2 1 1 3
> 1 3 2 1
>
> and so on...
>
> How the heck can I get a count for each value, in each field, in the same
> query?
>
> --
> To send email to me you need to send to:
> kwgagel
> at
> shaw (dot) ca
>


.

Relevant Pages

  • Re: Group By, Max and Min
    ... SELECT field1, field2, field3, LAST, MIN, MAX ... I have (from a previous saved query) these fields: ...
    (microsoft.public.access.queries)
  • Re: Using flexible query criteria
    ... criteria for the query is based on the user's input in the main form. ... One of the user inputs is a field called "Vendor_Name" (in the form of ... and said that Field1 is the primary key and is an autonumber field, and Field2 is the field shown in the combo box, you must modify it to a UNION select, similar to ...
    (comp.databases.ms-access)
  • Re: need to delete record having two different fields with same identical value
    ... Assuming you're always comparing the same two fields (Field1 and Field2) ... Run the query to check it, and then turn it into a delete query. ... which might be a lot safer (I hate deleting data unless I ...
    (microsoft.public.access.gettingstarted)
  • Re: Count Problem
    ... Access MVP 2002-2005, 2007-2008 ... Query as below: ... But after running, the result of field2 is correct, while field1 is the total records number of table x, not what I wanted. ...
    (microsoft.public.access.queries)
  • Re: Combo-Box list unsorting
    ... RecordSource property and select it. ... it'll take you to the query. ... drag-dropping fields from Table1 to the Form. ... want one of the fields (say Field2) in Table1 and the Form. ...
    (microsoft.public.access.gettingstarted)