Union query results in seperate columns

[solsearcher]

Hi Forum, I'm a newbie to Access and have been thrown in at the deep end to gather information from multiple tables and export that data into excel. If I could have some help to solve this first problem I think the rest will be straight forward. Fingers crossed.

First problem then.....

I am trying to collate data from the same table based on two queries. here is the SQL:

SELECT UserDefinedProperties.nVariableID, UserDefinedProperties.sValue, UserDefinedProperties.sContentID
FROM UserDefinedProperties
WHERE (((UserDefinedProperties.nVariableID)=65887))
UNION
SELECT UserDefinedProperties.nVariableID, UserDefinedProperties.sValue, UserDefinedProperties.sContentID
FROM UserDefinedProperties
WHERE (((UserDefinedProperties.nVariableID)=65877));

The results are what I wanted but listed. I really need the second query results in a new column.

Is this possible with Access?

 

[boblarson]

This should do it.

SELECT UserDefinedProperties.nVariableID, UserDefinedProperties.sValue, UserDefinedProperties.sContentID, Null As Table2VariableID, Null As Table2sValue, Null As Table2sContentID
FROM UserDefinedProperties
WHERE (((UserDefinedProperties.nVariableID)=65887))
UNION
SELECT Null, Null, Null, UserDefinedProperties.nVariableID, UserDefinedProperties.sValue, UserDefinedProperties.sContentID
FROM UserDefinedProperties
WHERE (((UserDefinedProperties.nVariableID)=65877));

EDIT: Now that I think about it, actually a Union Query isn't going to be able to give you side by sides. I'll post the answer in the next post.

 

[solsearcher]

Thanks for your help. I really didn't know if with "unions" I was barking up the wrong tree.

 

[boblarson]

Your query would need to be like this (substitute your field and table names as appropriate):

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees_1.EmployeeID, Employees_1.LastName, Employees_1.FirstName
FROM Employees, Employees AS Employees_1
WHERE (((Employees.EmployeeID)=2) AND ((Employees_1.EmployeeID)=5));

 

[boblarson]

So the corrected version (wasn't sure if I had time to do it but I did) is:

SELECT UserDefinedProperties.nVariableID, UserDefinedProperties.sValue, UserDefinedProperties.sContentID, UserDefinedProperties_1.nVariableID, UserDefinedProperties_1.sValue, UserDefinedProperties_1.sContentID
FROM UserDefinedProperties, UserDefinedProperties AS UserDefinedProperties_1
WHERE (((UserDefinedProperties.nVariableID)= 65887) AND ((UserDefinedProperties_1.nVariableID)= 65877));

 

[solsearcher]

Can't thankyou enough. Thanks for taking the time out for this one.

 

[solsearcher]

Query results in seperate columns not matching

The results for these queries don't match.
The sName 65887 refers to a front image for a product and the sName 65877 refers to the back image for the same product.
Not all products have a front and back image so I'm guessing that's why the order of results in both columns are not matching.
The sContentID is the product code so I was expecting the columns of results to match.
So the results would read all sContentID (product code) values, sName (front image) value if true and sName (back image) if true. That would make life much easier. I've been tinkering for hours now but really need some more help. I'm happy to learn some more but just need a poke in the right direction.

Thanks in advance.

 

[solsearcher]

I can see why now. Duplicates are being added, this seems to be throwing them out of sync.
Any idea how to remove the duplicates?

 

[boblarson]

The problem is that I didn't have full information when giving you the other solution. The fix is actually fairly simple.

1. Create a query with just

SELECT sContentID FROM UserDefinedProperties
WHERE nVariableID In(65887, 65877)
GROUP BY sContentID

And save the query then

2. Then add that query into the one you were given by me and link the nVariableID on nVariableID from each of the table instances that are in the original query.

3. Then double click on the link between the query in step 1 and each of the other table instances and set it to be one where you "select all records from that query in step 1 and only those records which match in UserDefinedProperties and UserDefinedProperties_1

 

[solsearcher]

Could you just explain how to add the new saved query to the first query that I saved.

I have uploaded a screen shot but the forum will not allow me to display it.

I think everything else is done

 

[boblarson]

Could you just explain how to add the new saved query to the first query that I saved.

I have uploaded a screen shot but the forum will not allow me to display it.

I think everything else is done

You may have thought you had uploaded a screenshot but you didn't. When you use the manage attachments form you have to click the button to navigate to the file to upload and then you need to be sure to click the UPLOAD button. If it uploads, a link will appear just below the text box which had the file path and file name in it prior to you hitting the Upload button. If that link doesn't appear, you didn't upload anything.

As for how to add to the existing query, I already told you but did leave out one step. You can right click in your gray area in the query designer (where the tables show) and you select ADD TABLES. Then select the query tab on the dialog that appears and find your newest saved query in the list. Select it and click ADD. Close the dialog and then link the field I told you in the way I told you and you're done.