Ranges: Correlating Data in 2 tables

[JShep]

I am new to the forum so accept my apologies if this is a redundant thread.

I would like to see others insights into the data handling and updating challenge that I am faced with.

I have 2 tables, one contains the following:


Table 1 rows contain:
mfg date (there are many occurs)
mfg location (there are multiple locations)
daily starting serial number
daily ending serial number
total units in "ranges" (simple math calculation)

Table 2 rows contain:
Serial number of a particular unit
Model number
configuration number
(and alot of other information)

the common link between the (2) tables are the model number, configuration number and serial number.

desired outcome is to update table 2 with the mfg date and mfg location data from table 1, automatically (or through a update query)

so the question is how to use start of a range and a end of range data to update a table with the 2 fields of data?

[pbaldy]

I'm having trouble visualizing the data. Can you post a db with some sample data in the two tables?

[JShep]

Paul -

Thank you for the interest.

Is there a email I can send it to? Was having trouble attaching the file (I have cut it down in size to about 700k) to a PM.

Can you PM me with your email? I will then send the reduce size db with additional information for your fuller understanding.

Appreciate all your insight!

[pbaldy]

Okay a couple of questions/issues. I look at the first record in the issues table (ID 90930) and see no way to relate it to the production table. What I assume is the serial number, 517314, does not fit into any of the ranges in the production table (unless my old eyes have tricked me). I get a sense that this production table is but one of many, which would probably be a design mistake. If that first record relates to a record in this production table, which one and why?

[JShep]

Your eyes haven’t tricked you. There are additional qualifiers that need to “line up”, they are the SN1 (model) and SN2 (configuration) to qualify for the daily production record table I sent to you.

This daily production record table only covers 1 single model. There are other similar tables for each model and configuration, for later. To keep focused on the method of the solution and try to be simple, I wanted to constrain the data to a single model, but allow multiple models and configurations to be accommodated in the “issues” table (i.e. not updated, until we have the daily production record in the proper form available)

So all the data in the daily production record table relates to sn1= 10 and sn2=0. Run the issues query with this criteria and you will find the records (units) that I would like to assign the production date and location to. I just did it and there are about 818 records that are of this model (SN1=10) and configuration (SN2=0).

Hope this helps you understnad the data set better.

[pbaldy]

If the production tables contain the same fields, I think it's a mistake to have more than one. I would have one table with an additional field to denote the model. I would also not store the info from the production table in the issues table, as it violates normalization rules and is easy to get with a query. That's not to say I would NEVER violate a normalization rule, but this probably isn't a time when I would.

It is also not advisable to have the spaces and symbols in your names; they force you to bracket them.

In any case, the answer to your question is a non-equi join:

SELECT [tblAH Issues].ID, [tblAH Issues].Product, [tblAH Issues].[SN Complete], [tblAH Issues].SN1, [tblAH Issues].SN2, [tblAH Issues].SN3, [tbl Daily Prod Record - Model 76].Date, [tbl Daily Prod Record - Model 76].[Mfg Line]
FROM [tblAH Issues] LEFT JOIN [tbl Daily Prod Record - Model 76] ON ([tblAH Issues].SN3 Between [tbl Daily Prod Record - Model 76].[Start Unit Range] And [tbl Daily Prod Record - Model 76].[End Unit Range]) AND ([tblAH Issues].SN2 = [tbl Daily Prod Record - Model 76].Config) AND ([tblAH Issues].SN1 = [tbl Daily Prod Record - Model 76].Model)

Note that a non-equi join can't be represented in the design grid, so you're stuck with SQL.

[JShep]

Thanks Paul - Appreciate all the assistance

Yes, I am not an expert and need additional direction on where to place the non equi join

Appreciated

[pbaldy]

If you have an existing or new query in design view, you can click on View/SQL View or use the icon in the toolbar to switch to SQL view. As you will see, that is a "no GUI" environment that requires you to know the syntax you want. You can copy/paste the SQL I posted into that view and run it to see the results.

I'd start with a query in design view that has everything else you want, the switch to SQL view and add that join.

[JShep]

Paul -

Thanks for all your help today. I did plug in the sql statement and it works as expected - excellent!

I understand your comments regarding data "normalization", unfortunately I am not well versed in setting up the data well, in addition, the data exists and is spread all over a number of xl sheets from within our company and our mfg partner overseas, so sourcing the critical infomation and consolidating it into a usable / working complete data set is difficult at best. Also at work is the gap in technology from our mfg and on our side - the IT department has been focused on other priorities lately.

thanks again.

Now I will need to teach myself how to apply this to the other models / family of products.

Much Appreciated