seemingly complex query

[trf]

I'm relatively new to Access and am having trouble constructing a query that, to me, seems fairly complex. I have records that look like this: (call it Table1)

ID FacType UrbanRural
AR0001 TP U
AR0001 TP U
AR0001 TP R
AR0002 WL U
AR0002 WL R
AR0002 WL R
AR0002 WL R
AR0003 WL U
AR0004 WH R


And I want to transform them into a series of records that shows, for each ID, the FacType, the number of times the ID appears in the first table, the number of times the ID appears in the first table and has a "U" under "UrbanRural", and the number of times the ID appears in the first table with a "R" under "UrbanRural" (the number of "U"s plus number of "R"s will always be the total number; there are no other codes or Null values). So in the example, I would want my Table2 to look like:

ID FacType TotalUR TotalU TotalR
AR0001 TP 3 2 1
AR0002 WL 4 1 3
AR0003 WL 1 1 0
AR0004 WH 1 0 1

So I'm breaking down the steps and it seems like they are (in my "pidgin SQL", which may or may not look anything like actual SQL):
1. select distinct ID into Table2 from Table1
2. join Table2 and Table1 and add: FacType, count of UrbanRural equal to "U", count of UrbanRural equal to "R" and the sum of those last two fields.

But I can't quite figure out how to write that query, either in SQL or in the QBE pane. Can anyone help?

Thanks!

 

[Russell Hauf]

To do this, you'll have to create 2 queries (replace tbl1 with your table name). First, a crosstab query:

TRANSFORM nz(Count(tbl1.FacType),0) AS Total
SELECT tbl1.ID, Count(tbl1.FacType) AS [TotalUR]
FROM tbl1
GROUP BY tbl1.ID
PIVOT tbl1.UrbanRural;

Then just create a make-table query that selects * from the crosstab query (obviously you'll need to save the crosstab query first).

HTH,

Russell

 

[Parra]

Use the following, you can just copy it and paste in a query design SQL. You can get the results from 1 query

SELECT Table1.ID, Table1.FacType, Count(Table1.UrbanRural) AS [Total UR], Sum(IIf([UrbanRural]="R",1,0)) AS [Total R], Sum(IIf([UrbanRural]="U",1,0)) AS [Total U]
FROM Table1
GROUP BY Table1.ID, Table1.FacType;

I actually am a little bored at work and created the database and query. If you provide me with your email address I can email it to you. Post it on the board or send me a private message.

Parra

 

[Russell Hauf]

Parra's way is much better for what you are doing - I just like Crosstabs!

 

[trf]

Russell and Parra - thanks for your help. Parra, thanks for offering to email me the code and database but in truth, I need to make some changes to adapt to my real table and field names, etc. For instance, my table is actually called ACWSs-TP-WH-INWL-LLNZ-3, which makes a lot of sense to me, but I figured I might as well just call it Table1 here. And I need to project more columns than just FacType, and so on...

But your code works perfectly (I didn't try yours, Russell, but I will when I am not facing a deadline - I really want to get the hang of SQL). Thanks so much! I feel like I am a bit farther on the way to understanding SQL and database logic...

Rob