Union Query and Joins

[JMichaelM]

I have a lot of queries which have to be joined by a union however since there are too many columns it reaches the maximum amount of fields defined. Is there any other options I could use to deal with this

 

[plog]

Is there any other options I could use to deal with this

Yes, in my experience the underlying queries can be written more efficently to eliminate the need for a UNION.

Does the ultimate data in these queries come from the same table(s). My guess is you can maniuplate the WHERE clauses to combine them within the same query instead of using a UNION.

 

[JMichaelM]

Yes. I just couldnt join the tables to the query.

I had to split a field last week (you may have seen my post) into 20 different fields. Each of these field needs to join onto one field on a seperate table which has a unique ID. I was unable to force the join in one single query.

 

[JMichaelM]

Example:
Table/Query 1 with Multiple fields to Join: Table with Relation:
Service ID1 > Service Name Field
Service ID2 >
Service ID3 >
Service ID4 >
Service ID5 >

 

[plog]

I had to split a field last week (you may have seen my post) into 20 different fields.

Sounds like you did it wrong. Tables should accommodate data vertically (with more rows) not horizontally (with more columns). All those ID values should be their own rows, not columns.

 

[JMichaelM]

After the split how do I get it into the same colum in a separate row?

 

[plog]

How did you do the split? Was it in a VBA function? If so, you should rewrite it so that it dumps that data into a new table along with a foreign key to the original record of the table it is currently in.

If not, then I suggest a series of APPEND queries that manually moves that data from those 20 fields to that new table. Let's say you have this now:

TableExisting
ExistingID, ServiceID1, ServiceID2, ServiceID3...
1, AXY7, AXY8, ZAY9, ...
2, BCD5, JHG4, RAR6, ...


What you would do is make a new table to hold all those service IDs:

TableNew
NewId, autonumber, primary key
ExistingID, number, foreign key to TableExisting
ServiceID, text, will hold all the ServiceID values

Then, you make an APPEND query using TableExisting:

INSERT INTO TableNew (ExistingId, ServiceId)
SELECT TableExisting.ExistingID, TableExisting.ServiceID1
FROM TableExisting;

You run that thing 20 times, each time change the TableExisting.ServiceID to the next number. Or use VBA to loop through it however many times you need.

 

[JMichaelM]

Thanks. I'll try to figure this out. I used an expression. Thankfully each ID has same # of characters:
Service ID1: Left([Service ID],12)
Service ID2: Mid([ Service ID],14,12)
Service ID3: Mid([ Service ID],27,12)
CMDB Technical Service ID4: Mid([CMDB Technical Service ID],40,12)and so on up 20