Look for a value in 3 columns and then create a report

[rutica]

Hello,

I am using Access 2010. I have a table with the fields:
ProjectID
ProjectName
AsOfDate
Prg1
Prg2
Prg3

ProjectID	ProjectName	AsOfDate	Prg1	Prg2	Prg3
1000	Project1	5/31/2014	FAC	BVV-1
1001	Project2	5/31/2014		FAC
1002	Project3	5/31/2014			MEE
1003	Project4	5/31/2014		ABC
1004	Project5	5/31/2014	ABC
1005	Project6	5/31/2014
1006	Project7	5/31/2014			ABC
1007	Project8	5/31/2014	FAC	BVV-1
1008	Project9	5/31/2014	BVV-1	MEE	ABC

(I know it's not a good idea to have a wide table, ex: Prg1, Prg2, Prg3, but we get this data from another source, so it comes in this way).

I'm trying to run a report that shows all the same values from Prg1 plus Prg2 plus Prg3 as the headers. So for example, the report would look like:

FAC
Project1
Project2
Project8

BVV-1
Project1
Project8
Project9

MEE
Project3
Project9

ABC
Project4
Project5
Project7
Project9

Thanks!

[June7]

Need to normalize the data for the report output. A UNION query can manipulate the data into normalized structure. Then use that query as report RecordSource. There is no wizard or designer for UNION, must type in SQL view of query builder. Limit of 50 SELECT lines. Forum is not allowing me to type UNION in the query example (weirdness that happens here occasionally). UNION goes where the _____ blanks are.

SELECT ProjectID, ProjectName, AsOfDate, "P1" As Source, Prg1 As Category FROM tablename
______ SELECT ProjectID, ProjectName, AsOfDate, "P2", Prg2 FROM tablename
______ SELECT ProjectID, ProjectName, AsOfDate, "P3", Prg3 FROM tablename;

[rutica]

Hi,
That works great!! Thank you so much. I hadn't considered a Union query. it works perfectly.

I will mark the thread as solved.

Thanks again