Union Query (several colunms merged into one)

[klornpallier]

I have set-up a union query like below:

SELECT [MONTH],[AUTH CODE/S], [PAYSLIPS DATE],[PAYSLIPS DATE],[PAYSLIPS DATE],[PAYSLIPS DATE]
FROM [ESR MONTHLY SCHEDULE]
UNION ALL SELECT [MONTH],[AUTH CODE/S],[P45’S DATE],[P45'S],[BACS DATE],[BACS PAYDATE]
FROM [ESR MONTHLY SCHEDULE];


Which instead of merging fields [P45’S DATE],[P45'S],[BACS DATE],[BACS PAYDATE] into the [PAYSLIPS DATE] fields which I want it seem to create expressions in new columns which I dont want.

 

[dhookom]

Could you type/paste a couple samples into a posting? UNION queries don't merge data/records.

Duane
MS Access MVP

 

[klornpallier]

If Union Queries dont merge records what facility in access can use use to merge the fields?

Thanks

 

[dhookom]

klornpallier,
What do you mean by merge? Again, please enter a few records into your posting so that we can understand what you expect.

Duane
MS Access MVP

 

[klornpallier]

Hi, I need the fields to be converted from this:

PAYSLIPS DATE PAYSLIPS P45’S DATE P45'S
Thu 24/04/2003 PAYSLIPS Thu 24/04/2003 P45'S
Thu 22/05/2003 PAYSLIPS Thu 22/05/2003 P45'S

To This:

PAYSLIPS DATE PAYSLIPS
Thu 24/04/2003 PAYSLIPS
Thu 22/05/2003 PAYSLIPS
Thu 24/04/2003 P45'S
Thu 22/05/2003 P45'S

Hope this makes sense.

Thanks

 

[dhookom]

Your initial UNION query had many more fields than mentioned in this posting. I am confused.

Duane
MS Access MVP

 

[lespaul]

From looking at your query it doesn't look like your data is normalized which will probably make it difficult to extract the data in the manner you are looking for.

If you'll tell us what your table looks like (all the fields in it), then we may have a better chance of helping you.

Leslie

 

[jhall01]

Your union is screwed up. It is not matching the columns! When you union you need to create aliases to match your columns from the different column names:

SELECT [PAYSLIP DATE] AS [DATE], [PAYSLIPS] AS [PAYSLIPS] FROM [ESR MONTHLY SCHEDULE]
UNION ALL
SELECT [P45 DATE] AS [DATE], [P45] AS [PAYSLIPS] FROM [ESR MONTHLY SCHEDULE]

Now the union query will move [p45 date] to column [date] and [p45] to [payslips] column

 

[dhookom]

This might be a good guess at a solution. I would however suggest that you don't create a field/column named "Date" since Date() is a function name and could cause confusion.

Duane
MS Access MVP

 

[jhall01]

true...I didn't even notice that but usually it is okay as long as you practice placing Brackets around it but you will run into problems when using other DBMS's, but you are right a better name would be [pdate] or something more descriptive

 

[klornpallier]

Thanks for all your help as this is given me a part solution.
However is it possible to UNION more DATE fields into [PDATE]and [PAYSLIPS]. At present I get a error message "Duplicate output alias 'PDATE'

 

[dhookom]

You can create as many dates as you want in the output. Just make sure the field names are all unique and there are the same number of fields in each select.

Also, you don't need to place "As fieldName" in any of the Selects other than the first. Ie: Don't use "As ..." for any fields after the first "UNION".

Duane
MS Access MVP

 

[LELOA]

Help needed

basic union query

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

error message
THE SELCT STATEMENT INCLUDES A RESERVED WORD OR AN ARGUMENT NAME THAT IS MISSPELLED OR MISSING, OR THE PUNCTUATION IS INCORRECT

This seems so simple but, I can't figure it out.

help please

 

[lespaul]

I'm betting [Relationship] is a reserved word in Access, name it something else like [RelationshipType]

 

[dodge20]

I am confused with the "Customers" and "Suppliers". What are you trying to see with these. That is where the error is. It looks like you may have just put the name of the table in quotes. If it is actually a field name, then you don't want the quotes. If it is the name of the table, then you don't want it at all.

Dodge20

 

[dodge20]

I think lespaul is right, I think I am wrong.

Dodge20

 

[jhall01]

You can go to help and do a search for "reserved" to look at a list of Microsoft Jet Reserved words. Relationship is not one of them but it is always good to add on something to a whole word:

[customer_type], [Relation_type] or [RelationshipCode], etc.

As for your columns:

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

What are your column name isn Customers and Suppliers if those are the column names your code should look like this:

SELECT [City],[CompanyName], [ContactName], [Customers] AS [RelationshipType] FROM [Customers]
UNION ALL
SELECT [City],[CompanyName], [ContactName], [Suppliers] FROM [Suppliers]

Notice you don't need "As [RelationshipType]" in the second query because Access already knows where to put the data which is in the 4th column already named [RelationshipType] from the first query as Duane has stated above.