grouping coulumns (if true)

[afslaughter]

I am very new to Access and need some help to figure this one out. I tried Google but I can't figure out how to phrase the search to get the proper results.



I have a table with Area1 column, Area2 column, etc. trough 20 with yes/no. A table with the Areas descriptions. What I would like to do is in the report stack all of the columns data if true and match it to the table with the Areas description. Thank you for any help. Example:

Table 1:
MyID /Area1 / Area2 / Area3
1 / yes / no / yes
2 / no / yes / yes


Table2: Note: this table has only 1 ID
ID / TypeArea1 / TypeArea2 / TypeArea3
1 / front / side / back



Report for MyID 1:
front
back




Report for MyID2:
side
back

[June7]

Your data is not normalized. As is will require UNION query. There is no wizard or designer for UNION, must type in the SQL View of query designer. Like:

SELECT ID, "Area1" As Source, Area1 As Category FROM table1
UNION SELECT ID, "Area2", Area2 FROM table1
UNION SELECT ID, "Area3", Area3 FROM table1
...
UNION SELECT ID, "Area20", Area20 FROM table1;

This will result in a dataset like it would be if normalized. This query can be used in subsequent queries. There is a limit of 50 UNIONs. Resorting to UNION is sign data is not properly structured.

Be aware that in my experience VBA cannot work with UNION data. If UNION is involved in any part of query sequence, VBA will not work with the data.

[afslaughter]

Thanks June7 for pointing me in the right direction, however I am still having some problem putting the pieces together. I believe it is my fault for not posting my question in a clear manner. I edited it to help distinguish between Table1 and Table2.

I tried the Select method and I get a object missing error, and reviewing the help file didn't help me to explain "Select". Would you mind breaking it down a little bit more for me, I apologize for my ignorance but this is really the first time I have ever typed a SQL statement.

--------/ is this Table 1 or 2? and which column /
SELECT ID, "Area1" As Source, Area1 As Category FROM table1

Thank you for your time.

[June7]

I based my example query on your table1 example data. Should have been: SELECT MyID...

You could use the query designer to help create SELECT query then go into SQL View window to see the SQL statement and type the UNION clauses.

What do you mean table2 has only 1 ID?

You could provide project for analysis. Attach to post.

[afslaughter]

I feel like I am making some progress but still not getting the results I am looking for. The Query will now run but I get an input window to Enter Parameter Values and whatever I enter is what is displayed. It is not pulling the data from Table2.

To make things easier (I Hope) I set up a Query to grab just 1 record instead of going through all of the records in Table1. To answer your question about Table2, I am using it to store global settings for my entire project like the detailed names of the Areas. This table will only have one record and be used like a cross reference. So I will be dealing with the Query that will only have 1 record and Table2 with only one record.

The end result I would like is if someone has a yes for area1 and area3 then the report would show area number they have a yes for and then the detailed description of that area.

Results:
1 Front
3 Back

Thanks for the help.

[June7]

Try:

query Union1
SELECT MyID, "Area1" As Source, Area1 As Criteria FROM Table1
UNION SELECT MyID, "Area2", Area2 FROM Table1
UNION SELECT MyID, "Area3", Area3 FROM Table1;

query Union2
SELECT ID, "Area1" As Source, TypeArea1 As Type FROM Table2
UNION SELECT ID, "Area2", TypeArea2 FROM Table2
UNION SELECT ID, "Area3", TypeArea3 FROM Table2;

query Query1
SELECT Union1.Source, Union2.Type, Union1.MyID, Union1.Criteria
FROM Union2 INNER JOIN Union1 ON (Union2.ID = Union1.MyID) AND (Union2.Source = Union1.Source) WHERE Criteria=True;

[afslaughter]

I tried working with this a bit before posting a reply. At first I tried pasting the all of the code into one query module, and this did not work so I figured maybe there should be 3 separate queries’s and worked the code that way.

With Union1, I am having a hard time understanding and getting to work. It is a union but it seems to be linking the table to itself. The error I get is "Circular reference caused by alias “ in query definitions SELECT List."

Union2 I think I do understand and seems to runs great.

Query1 probably doesn’t work because Union1 is failing, and although the code is way over my head I think I can see how this would work.

Thank you for helping me along and I am starting to think maybe this is a Query issue and not a Report issue. I guess if I get a Query to run properly I would just bind that to a text box in the report and the work it done.

[June7]

I built tables based on your example data and tested these queries. They worked. Yes, these are 3 separate queries. They could be nested into one long SQL but that is not necessary. These statements are copy/pasted from the SQL View window. Show your actual SQL statement by copy/paste into post. There really isn't any conceptual difference between the UNION queries.

Yes, should be able to bind report to the final SELECT query.

Resorting to UNION is indication tables are not properly structured.

[afslaughter]

Ok I think I am creeping a bit closer to what I need, thanks to you. I figured out the "Circular reference" came from the "ID" I switched to “SSN and it works but still ask to “Enter Parameter Value" If I just leave it blank it grabs all the records in the Table. Query1 dose the same thing except shows no records.

Here is my code:

Union1: Removed from "ID" to "SSN" to get rid of "Circular reference"
SELECT SSN, "Area1" As Source, Area1 As Criteria FROM Table1
UNION SELECT SSN, "Area2", Area2 FROM Table1
UNION SELECT SSN, "Area3", Area3 FROM Table1
UNION SELECT SSN, "Area4", Area3 FROM Table1;

Union1: Trying to switch from Table1 to Query that returns the 1 record from the User Form.
SELECT SSN, "Area1" As Source, Area1 As Criteria FROM Data Query
UNION SELECT SSN, "Area2", Area2 FROM Data Query
UNION SELECT SSN, "Area3", Area3 FROM Data Query
UNION SELECT SSN , "Area4", Area3 FROM Data Query;

Union2: Works Great
SELECT ID, "Area1" As Source, TypeArea1 As Type FROM Table2
UNION SELECT ID, "Area2", TypeArea2 FROM Table2
UNION SELECT ID, "Area3", TypeArea3 FROM Table2
UNION SELECT ID, "Area4", TypeArea4 FROM Table2;

Query1: Not working
SELECT Union1.Source, Union2.Type, Union1.SSN, Union1.Criteria
FROM Union2 INNER JOIN Union1 ON (Union2.ID = Union1.SSN) AND (Union2.Source = Union1.Source)
WHERE Criteria=True;

After working with this a bit I found that 1 textbox will not work I will have to make a text box for each field in the Report and use a DlookUp code to test for the correct results. Like this:

Textbox1:
=IIf([Aera1]=Yes,DLookUp("TypeArea1","Table2","ID=1"),"")

My next questions are:
Can I tell Union1 to just query the 1 record?
Can a query have an auto number column or a column with 1-20 so I can use that in the DlookUp code?
What would the code look like if I combined all 3 Union1, Uniion2, and Querry1? Just out of curiosity.

This has been a really good learning experience for me, thank you for all of your help.

[June7]

I've never had a 'circular reference' error with UNION query.

Names with spaces, special characters, punctuation, or are reserved words need to be enclosed in []. Data Query should be [Data Query], otherwise should fail. Should avoid naming with those elements.

You can include a WHERE clause on each SELECT line of the UNION.

Numbering rows in a query is not easy. Look at:
http://allenbrowne.com/ranking.html
http://www.lebans.com/rownumber.htm
http://forums.aspfree.com/microsoft-...ge-440477.html

I build nested queries by first building and saving the individual queries then pasting the inner SQL statements into the outer query.

SELECT Union1.Source, Union2.Type, Union1.SSN, Union1.Criteria
FROM (paste Union2 SQL here, keep the parens but no semicolon) As Union2 INNER JOIN (paste Union1 SQL here, keep the parens but no semicolon) As Union1 ON (Union2.ID = Union1.SSN) AND (Union2.Source = Union1.Source)
WHERE Criteria=True;

[afslaughter]

Goooooaaaaal!!!!

Ok, after having your help, working with the code, and much research I have exactly what I need. Here is code that worked for me, in case anyone else needs to achieve the same thing.

Union1:
SELECT SSN, "Area1" As Source, Table1.Aera1 As Criteria FROM DataQuery
UNION SELECT SSN, "Area2", Table1.Aera2 FROM DataQuery
UNION SELECT SSN, "Area3", Table1.Aera3 FROM DataQuery;

Union2:
SELECT ID, "Area1" As Source, TypeArea1 As Type FROM Table2
UNION SELECT ID, "Area2", TypeArea2 FROM Table2
UNION SELECT ID, "Area3", TypeArea3 FROM Table2
UNION SELECT ID, "Area4", TypeArea4 FROM Table2;

Query1:
SELECT Union1.Source, Union2.Type, Union1.SSN, Union1.Criteria
FROM Union2 INNER JOIN Union1 ON Union2.Source = Union1.Source
WHERE (((Union1.Criteria)=-1));

Query2: To get the record count
SELECT (Select Count(1) FROM Query1 A
WHERE A.type <=Query1.Type) AS ID, Query1.SSN, Query1.Source, Query1.Type, Query1.Criteria
FROM Query1
ORDER BY Query1.Type;

To see if I can, later I may try to squeeze this into 1 Query Module.
Thank you June7 I couldn't have done it without your help.