Query to update [Qty] field if [TypeName] field matches (1 Viewer)

[tmyers]

I had touched on this awhile back, but based on data inconsistency, I gave it up as I didn't think it was doable.
Now I think it can be done, at least partially.

How can I design a query that takes quantities assigned to a typename/ID then update a field in another table with that quantity where typename matches?

This would involve 3 different tables. [tblContractorJob], [tblDrawingFixtureType], and [tblProduct]. [tblProduct] doesn't have any relationship to the other two and is essentially a stand alone table.

Here is the structure for each:
[tblContractorJob]

[tblDrawingFixtureType]:

[tblProduct] (it is very wide in datasheet view, so here it is in design):

So what I am trying to do is run a query where the user selects which counts they want to use, either our in house counts from [tblDrawingFixtureType], or select a contractors set of counts from [tblContractorJob] and update [tblProduct]'s [Quantity] field with those counts where their fields [TypeName] matches (I did just notice I accidentally used a [Type] in the product table and it is now fixed).

For ones it doesn't find a match, open a form showing all of those and allow the user to manually input values for those. This is so the user can quickly change entire counts for a quote.

I would assume it would be something along the lines of if [typename] from table is like [typename] from product update quantity to quantity from other table.

 

[plog]

[tblProduct] doesn't have any relationship to the other two and is essentially a stand alone table.

Incorrect. All 3 tables are related via JobID.

My gut is telling me you haven't set up your tables correctly. All 3 tables sharing JobID is 1; tblContractorJob & tblDrawingFixtureType sharing both JobID and TypeID is 2; and needing an UPDATE query to maintain data is 3.

Can you address those 3 issues? Also, can you complete the Relationship Tool in your database and post a screenshot with all tables expanded so I can see their fields?

 

[tmyers]

Incorrect. All 3 tables are related via JobID.

My gut is telling me you haven't set up your tables correctly. All 3 tables sharing JobID is 1; tblContractorJob & tblDrawingFixtureType sharing both JobID and TypeID is 2; and needing an UPDATE query to maintain data is 3.

Can you address those 3 issues? Also, can you complete the Relationship Tool in your database and post a screenshot with all tables expanded so I can see their fields?

I was incorrect in what I stated. The product table isn't directly related to the other two, but is through my "master" table JobDetails via JobID. I have JobID everywhere as it is what I used to keep things "together". Most of my tables that contain specific data have JobID in them, to keep them tied to a specific job. Where data isnt job specific, JobID isn't present.

I will post a screenshot in a moment.

 

[tmyers]

I already know I am going to get a stern talking to for this. I had a friend who has experience in databases, and he had originally set this up and assured me that even though I was protesting the way this is laid out, while spider-webby, could not result in circular references. Most of the relationships are join specific that he setup. The structure he made (according to him) will assure that I should never have orphan records in the event a job a deleted.

I am ready to receive my stern talking to.

 

[plog]

Yeah, that's a mess. Everything is directly related to tblJobDetails (and then indirectly many times over). My thoughts on orphaned records are 1--who cares and 2--don't allow deletions, instead mark records as obsolete/invalid/inactive/etc.

My suggestion is to make a copy of your database, clear out the Relationship Tool and rebuild it. Starting with nothing, add tblJobDetails, then add one table at a time with the rule that it can only be directly related to one table already in the Relationship Tool. If you feel the need to connect that new table to more than one existing table its time to examine all the tables in that relationship and determine the correct way to add it such that you don't create multiple paths. This will mean reallocation of foreign keys--deleting some altogether, adding some to tables and moving a few fields around.

If you get stuck post back here. We can help in general, but its your organization so only you understand how the data truly relates.

 

[tmyers]

I was afraid of that. My friend may have done it this way so as I worked on it, I understood it rather then fully explaining the concept of foreign keys and how they work.

I will try to restructure it and see what happens.

 

[tmyers]

Plog, how would you handle the relationship between the tables tblcontractors, tblcontractorjob and tblfixturetype?

tblContractorJob is essentially a many to many table, but if I link it to either one, I can get back to tblJobDetails in two ways. I dont know how to prevent that because of my table tblUsers.

 

[plog]

Looking at your screenshot, the real issue is does tblFixtureTypes directly relate to tblContractorJob or tblJobDetails:

1. Is a fixture tied to a contractor? If so, TypeID stays in tblContractorJob and JobID comes out of tblFixtureTypes

2. If a fixture is independent of the contractor then TypeID comes out of tblContratorJob and JobID stays in tblFixtureTypes.

As for tblUsers, sometimes a table is used twice in the Relationship Tool, but used independently. In those instances, you bring the table in twice and link appropriately so that there is no spider web created. One would be linked to tblContractors and nothing else and one would be linked to tblJobDetails and nothing else.


However, the problem with Access's relationship tool is that it "fixes" that and upon saving deletes the second table and the next time you open the Tool it shows that table just once and creates the spider web. So, long story short, don't worry about tblUsers yet--I would add it last, not at all or just once.

 

[tmyers]

How's that look.

 

[plog]

Passes the initial normalization smell test. Now, you need to make sure it serves your organizations needs.

By that I mean, can you get the data out of it that you want? Do you have a report already set up in your database? Can you run it or reconfigure it to run with the new structure? If not, is there a report you need this database to generate that you can create? That will verify it works and then we can move on to your initial issue of this thread.

 

[tmyers]

I am reverifying links now. Some completely broke due to me linking things to jobid everywhere, so I have to re-establish those. Once that is done, I can test.