Query Two Tables with Different Fields (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 05:49
Joined
Dec 5, 2017
Messages
843
Hi All -

I have two tables (tblWeldAssembleInspection & tblMillInspect).

These tables reflection inspection data.

We manufacture products. Some products are made up of two distinct products welded together BUT the two distinct products that are welded together are also sold as their distinct selves meaning NOT ALL products go through weld/assembly. So we may produce PartA and PartB and weld them together to make PartC or we may just sell them as PartA or as Part B - and they do not have to sell together.

My reason for saying this is to forgo the DB design discussion that always ensues. I have the tables setup separately for each type of inspection even though at first glance it appears that they are related. They are only "kind of" related.

Both tables do have fields with similar names (e.g., JobNumber, Workstation, etc.). A main commonly named field is "OilCanning." This is a yes/no field in both tables. This field is the primary focus of my upcoming question.

It should also be noted however that tblWeldAssemblyInspection contains the field "AssemblyType" whereas tblMillInspect has a similar field but it is called "PartType."

50% of the fields in each table are not named the same and do not relate to each other.

We have an ongoing issue with a manufacturing phenomenon called oil canning that can happen at any stage of production (i.e., either during part production OR not until assembly production).

I want to be able to run a query that tells me of every instance of oil canning regardless of where it happened - either during part production or assembly production. Remember - not all parts become assembly but all assemblies are made of parts.

The basic current design of this database is that it is actually multiple, unrelated databases. One for mill production and another assembly production - - - even though many inspection instances share common fields (e.g., JobNumber, Task, Resource). May be I could have designed it differently but I struggled with that forever and have posted about it multiple times.

So ...... is there a way to run a query on the two tables in question with a "yes" criteria set for the "OilCanning" field that exists in both and return every record from both tables into some sort of dynamically created table? Not all fields from each table would be necessary to retrieve but there would be some fields needed to be retrieved from one or the other table do not exist in both tables.

Is something like this possible?

Thank you in advance?

Tim
 

Attachments

  • QCDBFE20181212.zip
    287.9 KB · Views: 131

June7

AWF VIP
Local time
Today, 01:49
Joined
Mar 9, 2014
Messages
5,425
Sounds like you need a UNION query. Example:

SELECT ID, field1 AS Name1, field2 AS Name2, Null AS Name3, "T1" AS Source FROM table1
UNION SELECT ID, field1, Null, field3, "T2" FROM table2;

The first SELECT sets the column headers. Make sure each SELECT has the same number of fields referenced.

Must create UNION in query builder SQL View.

Now use the UNION query as source for your search.
 

Zydeceltico

Registered User.
Local time
Today, 05:49
Joined
Dec 5, 2017
Messages
843
Sounds like you need a UNION query. Example:

SELECT ID, field1 AS Name1, field2 AS Name2, Null AS Name3, "T1" AS Source FROM table1
UNION SELECT ID, field1, Null, field3, "T2" FROM table2;

The first SELECT sets the column headers. Make sure each SELECT has the same number of fields referenced.

Must create UNION in query builder SQL View.

Now use the UNION query as source for your search.

With what little I know about UNION queries, I believe you are correct.

Are you able to explain the logic of the SQL statement or point me to a resource? For instance: how does "field1" in the first SELECT statement relate to the "field1" in the UNION SELECT? What should I infer from "Null?"

I'll do researching on the AS. I think In know what that means.

Thank you so much,

Tim
 

Dreamweaver

Well-known member
Local time
Today, 09:49
Joined
Nov 28, 2005
Messages
2,466
Code:
SELECT -1 As FilterTypesID,"<<ALL>>" As FilterType
FROM StblFiterTypes
UNION SELECT StblFiterTypes.FilterTypesID, StblFiterTypes.FilterType
FROM StblFiterTypes
ORDER BY FilterType;


Both sides of the union must have same number of fields
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:49
Joined
Oct 17, 2012
Messages
3,276
Believe it or not, there are ways to normalize even this kind of inspection setup. What I'm thinking of would involve tables for inspection type, inspection 'list', and inspection items. It would be a fair amount of work, however.

Just keep in mind that NOT normalizing things will make things harder and harder as time goes by. As just one example, let's say you end up adding another three products, each of which gets their own tables. You'll need to go through EVERY query referring to inspections and rewrite them to include the new tables. Same with any other procedures involving them, and keep in mind that union queries are NOT updateable, and that gets passed on to any queries that use the union queries.

AS is simply an alias command. It lets you rename tables in a query and refer to them by the alias instead (useful for really long names), and it lets you rename output fields for use by whatever calls the query.
 

Zydeceltico

Registered User.
Local time
Today, 05:49
Joined
Dec 5, 2017
Messages
843
Believe it or not, there are ways to normalize even this kind of inspection setup. What I'm thinking of would involve tables for inspection type, inspection 'list', and inspection items.

I have toyed with that idea - and still may develop a major revision in the future that follows that design.

Very fortunately for me - our company moves at glacial pace introducing new products - truly glacial - epochal. lol
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:49
Joined
Oct 17, 2012
Messages
3,276
Very fortunately for me - our company moves at glacial pace introducing new products - truly glacial - epochal. lol

Heh. I work in health insurance, so 'slow' isn't exactly an option for us lately.

Now, getting the various government agencies we interact with to change something, on the other hand - THAT is a freaking nightmare.
 

Users who are viewing this thread

Top Bottom