Query Two Tables with Different Fields

[Zydeceltico]

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

 

[June7]

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]

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

 

[June7]

You 'line up' the similar fields from each table. If a table does not have field equivalent to other table, use Null as a 'placeholder'. Review https://www.w3schools.com/sql/sql_union.asp

 

[Dreamweaver]

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]

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]

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

 

[Zydeceltico]

Both sides of the union must have same number of fields

Ahhhh - I see sayeth this blind man

Thank You

 

[Zydeceltico]

You 'line up' the similar fields from each table. If a table does not have field equivalent to other table, use Null as a 'placeholder'. Review https://www.w3schools.com/sql/sql_union.asp

Thank you very much for that clarification. I think I get it now.

 

[Frothingslosh]

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.