Solved New to Concatenation and union queries (1 Viewer)

slharman1

Member
Local time
Today, 17:56
Joined
Mar 8, 2021
Messages
467
I have a select query that I concatenated two fields from two different tables, neither of the fields are PKs.
tblJobsItemDetails and tblJobItemDetailsAcc into one field so my list is like this: 10-1, 10-2, 11-1, 11-2, 11-3, 12-1, 13-1, 13-2, ect.
This gets me the list of accessories in a format that my users can understand.

Now I will make a select query from the tblJobsItemDetails with the item list for the main items like this: 10,11,12,13 ect or should I cantaenate a "0" to them to have similar data structure? 10-0, 11-0, 12-0, ect.

Then I will make a UNION query to hold each item whether it be the main item, or an accessory for a particular item, in order to bind the recordset to a "Time Clock" form for employees to "punch" in/out of items in the combined list for time tracking in manufacturing.

My question is this - will the data easily be extracted back out from the fields back to their original data fields for time tracking of the individual items - whether they be a main item or an item's accessory?

Thanks as this is my first attempt to use much concatenation, much less UNION queries.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:56
Joined
Oct 29, 2018
Messages
21,358
To do that, you'll have to add a column to the union query to designate where the items came from.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:56
Joined
May 21, 2018
Messages
8,463
I assume you want them sorted like
10
10-1
10-2

So it does not matter if you put 10-0 or 10 it will sort properly. In your union you may need a dummy column as stated

Select distinct ItemNumber from tblJobsItemsDetials, "Item" as Category
Union
Select distinct Accessory from tblJobsItemsDetailsAcc, "Accessory" as Category
order by 1

I know it is something different because the second query is some join concatenating the item and accessory, but that is a general idea.
 

slharman1

Member
Local time
Today, 17:56
Joined
Mar 8, 2021
Messages
467
I assume you want them sorted like
10
10-1
10-2

So it does not matter if you put 10-0 or 10 it will sort properly. In your union you may need a dummy column as stated

Select distinct ItemNumber from tblJobsItemsDetials, "Item" as Category
Union
Select distinct Accessory from tblJobsItemsDetailsAcc, "Accessory" as Category
order by 1

I know it is something different because the second query is some join concatenating the item and accessory, but that is a general idea.
Thanks MajP, I am working on the UNION now, but when I record the time stamp for say item 10-2, will I be able to apply it to the second accessory for item 10 in my accessories table? I am thinking that is the whole reason for database programming -right?

Thanks, I would be lost without you guys! You (along with countless others) helped me quite a bit on this journey writing my plant management DB
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:56
Joined
May 21, 2018
Messages
8,463
That is what I did. Your results would have a second column.

10 Item
10-1 Accessory
10-2 Accessory

Can you provide a sample db? It would be a lot easier.
Since you are pulling information from two tables you will likely need some kind of code to do something.
If you pick 10 then you know it is an item number and it goes in some field
If you pick 10-1 you know it is an accessory. In fact you can write code or add some other fields. You know it is Item 10 and Accessory 1 and then you will do something else with it.
 

slharman1

Member
Local time
Today, 17:56
Joined
Mar 8, 2021
Messages
467
That is what I did. Your results would have a second column.

10 Item
10-1 Accessory
10-2 Accessory

Can you provide a sample db? It would be a lot easier.
Since you are pulling information from two tables you will likely need some kind of code to do something.
If you pick 10 then you know it is an item number and it goes in some field
If you pick 10-1 you know it is an accessory. In fact you can write code or add some other fields. You know it is Item 10 and Accessory 1 and then you will do something else with it.
Yes sir, I got it. Debating on if I want to add a column with that info in it to pull data later.
Thanks again. This post is solved! :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:56
Joined
May 21, 2018
Messages
8,463
I would add the extra columns. Less coding.
 

slharman1

Member
Local time
Today, 17:56
Joined
Mar 8, 2021
Messages
467
I would add the extra columns. Less coding.
Thanks, I can’t see for the life of me when I would ever need them, but of course I am here trying to figure a simple thing like a union query. :) Seems like I should take good advice when given.
 

Users who are viewing this thread

Top Bottom