Why can't I do this union query? 1

[ietprofessional]

Here is an example of the table I'm querying and secondly how I want it to be displayed in a form.

1) Initial Table: (this is one row)
sales rep - company name - date - novunit - novasp - decunit - decasp

Notice that the november and december units are in one record.

2) Why I want a union: (this is two rows)
sales rep - company name - date - novunit - novasp
sales rep - company name - date - decunit - decasp

Notice that the november and december units are in separate records. I'm having trouble accomplishing this.

How I'm trying to accomplish the union:

SELECT unitsandasp.salesrep as one, unitsandasp.companyname as two, unitsandasp.date as three, unitsandasp.product as four, unitsandasp.novunits as five, unitsandasp.novasp as six
FROM unitsandasp
union
SELECT unitsandasp.salesrep as one, unitsandasp.companyname as two, unitsandasp.date as three, unitsandasp.product as four, unitsandasp.decunits as five, unitsandasp.decasp as six
FROM unitsandasp;

THANKS!

 

[SkipVought]

Hi,

...and the question is????

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[Golom]

You didn't say exactly what the problem is. Are there missing rows? Is the query giving an error?

I can see four possible things to look at.

[li]'date' is a reserved word. Try square brackets around it.[/li]
[li]Are data types consistent? Every entry in a column must be of the same data type as every other entry.[/li]
[li]UNION eliminates duplicate records in the result. Try UNION ALL to avoid duplicate elimination.[/li]
[li]Your query references a "Product" field but your description does not. Does that field really exist?[/li]

 

[mordja]

ietprofessional,

Works fine.

SELECT temp.salesr , temp.cname, temp.date, temp.novunit, temp.novasp
FROM temp
UNION
SELECT temp.salesr, temp.cname, temp.date, temp.decunit, temp.decasp
FROM temp;


Whats the error ? Your names in your SQL are different than in your spec ie decunit vs decunits , salesrep vs sales rep but Im assuming this is not your problem.

Mordja

 

[ietprofessional]

The problem is I'm not getting any results from the union query.

 

[ietprofessional]

When I run this I get 35 records:

SELECT unitsandasp.salesrep as one, unitsandasp.companyname as two, unitsandasp.date as three, unitsandasp.product as four, unitsandasp.novunits as five, unitsandasp.novasp as six
FROM unitsandasp

When I run the union I get 36 records. I don't understand, shouldn't there by 70 records.

SELECT unitsandasp.salesrep as one, unitsandasp.companyname as two, unitsandasp.date as three, unitsandasp.product as four, unitsandasp.novunits as five, unitsandasp.novasp as six
FROM unitsandasp
union
SELECT unitsandasp.salesrep as one, unitsandasp.companyname as two, unitsandasp.date as three, unitsandasp.product as four, unitsandasp.decunits as five, unitsandasp.decasp as six
FROM unitsandasp;

 

[ietprofessional]

UNION ALL WORKS! There were duplicates. Thanks Golom!