All data not showing

[Ray67]

Hi-

I put a union query together but for some reason it only show some of the data. For the ones in bold it's not showing the CPT1New code. Attached is a copy of the db.NEWdb.zip

PtAcct	DateofService	AdjAmt1	CPT1new
4454Doe	28-Jan-14	50	99214
4454Doe	14-Feb-14	50
4454Doe	14-Feb-14	1.94	99214
5164Ray	06-May-14	1.1
5164Ray	06-May-14	75	20610
5164Ray	06-May-14	50	99213
5164Ray	06-May-14	44.5	J1030
5164Ray	19-May-14	0.55
5164Ray	19-May-14	50	20610
5164Ray	19-May-14	50	99213
5164Ray	19-May-14	22.25	J1030

[ItsMe]

Perhaps you need to use UNION ALL
http://www.w3schools.com/sql/sql_union.asp

[RayMilhon]

First thing I would do is add a column to each Select in the union that shows which select is returning those rows.

Select ...,1 as querynumber
Union
Select ...,2
Union
Select ...,3

That will tell you where it's happening then you can check that particular query to determine why.

[Ray67]

I try using Union All and it didn't work.

Thank you

Perhaps you need to use UNION ALL
http://www.w3schools.com/sql/sql_union.asp

[aytee111]

You need to look at the union query - the column heading is not the field that is being displayed. For instance, the 4th line above comes from the 4th part of the union, which is Amt4 (1.1) and CPT4 (blank).

[Ray67]

Aytee

You are right there is no data amt4. So it would not show it.
I'm going about this the wrong way.
How would i be able to correct this?
The data is horizontal and i would like it to show vertical. Each patient can have up to 10 amt for each date of service.

You need to look at the union query - the column heading is not the field that is being displayed. For instance, the 4th line above comes from the 4th part of the union, which is Amt4 (1.1) and CPT4 (blank).

[June7]

Simplify - build the UNION from the table, not the query, with or without filter criteria. Can do the Replace calcs in the UNION to adjust the data for CPT. Then use the UNION as source for subsequent queries. The semi-colon goes on only the last line.

Forum is not letting me type UNION in the sql example. Replace the underscores.

SELECT 1 AS Source, PtID AS PtAcct, DateofService, CPT1 AS CPT, AdmAmt1 AS AdjAmt FROM TabNewDataClimacs
______ SELECT 2 AS Source, PtID, DateofService, CPT2, AdmAmt2 FROM TabNewDataClimacs
______ SELECT 3, PtID, DateofService, CPT3, AdmAmt3 FROM TabNewDataClimacs
______ SELECT 4, PtID, DateofService, CPT4, AdmAmt4 FROM TabNewDataClimacs;

A UNION basically manipulates data into normalized structure should be to start with. An issue could be that each record does not have a unique identifier.