Setting up a query in Access

ElJeffe

I'm using Access 2010, and I'm trying to extract some data from a table with a query into a nice, legible format.

The table I'm querying consists of a bunch of case records along with the site name corresponding to that case, and a bunch of questions with yes/no/unknown/blank answers. What I want the output to look like is Site Names as column headings and questions as row headings, with each cell containing (for example) the number of "Yes" answers for X question at Y site. Each record uses a combination of FamilyID and entry date as the primary key.

What I tried was to set up a crosstab query (set up via Query Design, because I don't speak SQL) with a value count on the FamilyID field, the Site Name field grouped by column heading, and then each question field grouped by row heading. This gave me a big giant mess, obviously. I tried refining it by putting "Yes" into the Criteria box for each question, which gave me only those records that had answered Yes on everything. Then I tried putting "Yes" criteria into the Or: box for each question, which... also didn't work.

I can just dump all this data into Excel and do it all manually, but I'm going to need to do this semi-frequently and would like a way to automate it with a pre-built query, so I can just hit a button and yay, done. I can also make a query that only looks at one question at a time and spits back how many Yes/No/Unknown/Blank answers there were, but there are 18 questions and I'd rather not have to do them one by one.

bowen

It'd be really helpful to have some pictures for this example.

ElJeffe

Here is what the table looks like, though I C&P'd it into Excel so I could scrub the confidential data. The FamilyIDs are actually 6 digit numbers. The columns full of Yeses and Nos are the questions I'm trying to get at, and Site Name column is the one with site names.

This is what the query set-up looks like in the Design view:

And this is what it's giving me. (The numbers in the column labels correspond to the site names; if I can't get it to give me the actual names, that's fine.):

Bobble

Can you set this up so it dumps into excel automatically and feeds into a pivot table?

ElJeffe

I might be able to do a pivot table if I had some idea how to set that up.

As it stands, I have to dump giant wads of the table into Excel anyway, to run periodic analysis. I thought it would be easier to just make a query that figured this out for me, but if that's not the case, I'm open to Excel solutions.

(I've done one or two pivot tables before, but infrequently enough that I never retain the knowledge of how to do it.)

L Ron Howard

In Excel, and forgive me for being vague, you can link it to a DB (SQL, Access) for the data.
In 2010, which it looks like you're using, there's a Data tab at the top. Select that, and there should be a button that says Connections.
From there, you just point it to Access and you're good to go. And then into Pivot Table goodness.

Thundyrkatz

Also, pivot tables are actually very scarey to think about until you see how they work, then they are actually extremely easy and fun to use.

I found myself in a position where I had to learn how to use them recently and taught myself by watching a YouTube video for 10 minutes and then messed around with it for a bit.

Also once you know how to use them, people will think you are a GD Wizard!

Dr. Frenchenstein

is there any way to set it up so "questions" is one column and "answers" is another? that would be a snap to do what you want (i think). a lot of people set up access tables like an excel table, and it makes it much harder to work with.

bowen

Dr. Frenchenstein wrote: »

is there any way to set it up so "questions" is one column and "answers" is another? that would be a snap to do what you want (i think). a lot of people set up access tables like an excel table, and it makes it much harder to work with.

Yeah this table is going to be really hard to work with in general, doesn't look like the schema is really designed for querying, but rather, storing.

ElJeffe

Is there a better way to set it up? I know only the basics of database design - I do it only rarely - so I haven't learned things like designing with queries in mind. I may or may not redesign this one, but it would be nice to know what I might do differently in the future.