Assigning various counts to various ID's (1 Viewer)

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
This is going to be a somewhat of a pain to describe, so I will try my best.

In the app I am working on, we have light fixtures that are assigned a type/designation via the designer/engineer. When my user inputs those types, they are assigned an ID (just a typical auto-number and not anything special). We then input out internal counts for them, as well as all counts we receive from various contractors. I have all of them segregated into different tables. One table holds the types, it ID and all other info relevant to it. I have another table that holds our counts per blueprint page, then another table that handles contractor counts (this is linked to the contractors table and the types table).

So say Type A (ID 1) could have a count of 2, 5, 4, 1 (and possibly 0 due to a contractor missing it). All of this works just fine (so far). The problem that has arisen is the boss wants to be able to easily "flip" through the counts for our reporting. So on the quote report, he wants to be able to easily flip between sets of counts. Via button, combo box, check box, it doesn't overly matter. The problem is when the vendors quotes are imported, the types on their quote are not assigned any ID, and even if they were, they wouldn't match the other ID's. I know this is a problem with trying to have auto-number actually mean something, but apart from this ONE instance, it works fine.

Another issue would be the vendors don't always 100% follow suit on types. We might input it as Type A, but they might do type A-EM or even worse, breaking type A into subparts (an example would be type A consisting of A-Frame, A-Trim, A-Driver etc).

So my question is how could I get a query to take the counts we have in 2 other tables, and properly "assign" them to the relative product in my product table? To make matters worse, currently my products table isn't related to anything other than vendors as at the time of creation, I could not remedy how to assign the incoming types to a correct and meaningful ID.

I personally don't think it is possible, but I will leave that up to you experts. Ask questions, because I am sure I did not explain this well and people will need more details.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
Hi. I'm not sure what the actual question was; but if you're asking how you could match Type A with A-Frame or A-Trim, etc., then one approach is to use a translation table. If that's the question, let us know, and we'll explain what is a translation table (unless you already know what it is).
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
The question is how can I take counts and choose what ones to use for the report.
So I have 3 different "sets" of counts that were entered, but when it comes time to generate my report, I want to give the user and option to be able to pick which set of counts the report will use, then (I assume update rather than append) the product table with those counts.

I also wouldn't mind the walk through on a translation table, as I know this is something I am going to have to reconcile at some point with variations in types. I believe I will need that anyways, as I couldn't correctly assign counts until then.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
The question is how can I take counts and choose what ones to use for the report.
So I have 3 different "sets" of counts that were entered, but when it comes time to generate my report, I want to give the user and option to be able to pick which set of counts the report will use, then (I assume update rather than append) the product table with those counts.

I also wouldn't mind the walk through on a translation table, as I know this is something I am going to have to reconcile at some point with variations in types. I believe I will need that anyways, as I couldn't correctly assign counts until then.
Okay, are you able to simply filter the report on whichever count source the user selects?

For example, if your report includes all the sets of counts, you should be able to filter it to only show/display a specific (or range of) count sets, right?

Or, is the problem more like trying to modify the report to use a different table as a source, based on user choice?
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
The problem would be closer to the 3rd option, as the product table has it's own "set" of counts that are brought in during the import process. It was probably bad design, but I have 3 tables that each contain counts (the reason I did this was because while they are all counts, they are specific to different things).

So I have a table that holds our internal counts (tblDrawingFixtureType), a table that holds contractor counts (tblContractorJob), and a table that holds the product and counts from the vendors (tblProducts). Currently the report only uses the counts from tblProducts, as I had designed it with the intent of the vendors using our counts 98% of the time, then changing them manually as needed. I did not anticipate the boss wanting to do sweeping count changes with a click.'

So I would essentially have to modify the report to pull counts from the table the user chooses. Either from our internal counts, one of 1-5 different sets from the contractors table, or use the counts from the vendors located in the product table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
The problem would be closer to the 3rd option, as the product table has it's own "set" of counts that are brought in during the import process. It was probably bad design, but I have 3 tables that each contain counts (the reason I did this was because while they are all counts, they are specific to different things).

So I have a table that holds our internal counts (tblDrawingFixtureType), a table that holds contractor counts (tblContractorJob), and a table that holds the product and counts from the vendors (tblProducts). Currently the report only uses the counts from tblProducts, as I had designed it with the intent of the vendors using our counts 98% of the time, then changing them manually as needed. I did not anticipate the boss wanting to do sweeping count changes with a click.
Probably the simplest option for you to adapt to your design is to create a separate report per data source. So, when the user selects a source and click a button, you simply open that report.
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
Probably the simplest option for you to adapt to your design is to create a separate report per data source. So, when the user selects a source and click a button, you simply open that report.
How could I reconcile the variations in types then? As per the example I gave with trims, frames etc. All but the product table would not match that format, as the other two would just have a single type A, not one broken into pieces.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
How could I reconcile the variations in types then? As per the example I gave with trims, frames etc. All but the product table would not match that format, as the other two would just have a single type A, not one broken into pieces.
That's the one where you could probably use a translation table. Basically, it's a junction table where you have the Type you really one on one column and the types it can match to on the other column. You would add this table as a join to your query to pull in all matching types.
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
Ah. So it would match Type A to all the other variations of Type A.
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
Out of curiosity, what would such a query look like? Say I have just a simple two column table. Type and TypeVar, with Type being the one I want it to "translate" to.
The other two tables would be tblFixtureType and tblProducts.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
Out of curiosity, what would such a query look like? Say I have just a simple two column table. Type and TypeVar, with Type being the one I want it to "translate" to.
The other two tables would be tblFixtureType and tblProducts.
Just a shot in the dark, but maybe something like:
SQL:
SELECT TypeTable.Type, TypeVariantsTable.Count
FROM TypeTable
  INNER JOIN TranslationTable
    ON TypeTable.Type=TranslationTable.Type
    INNER JOIN TypeVariantsTable
      ON TranslationTable.TypeVariant=TypeVariantsTable.Type
Hope that helps...
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
Im getting a syntax (missing operator) error. I cant seem to realize what I missed.
SQL:
SELECT tblFixtureTypes.TypeName, tblProduct.Type
FROM tblFixtureTypes 
INNER JOIN tblTypeTranslate
ON tblFixtureTypes.TypeName=tblTypeTranslate.Type
INNER JOIN tblProduct
ON tblTypeTranslate.TypeVar=tblProduct.Type;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
Im getting a syntax (missing operator) error. I cant seem to realize what I missed.
SQL:
SELECT tblFixtureTypes.TypeName, tblProduct.Type
FROM tblFixtureTypes
INNER JOIN tblTypeTranslate
ON tblFixtureTypes.TypeName=tblTypeTranslate.Type
INNER JOIN tblProduct
ON tblTypeTranslate.TypeVar=tblProduct.Type;
Just try to do it using the query grid and post a screenshot, so we can tell you if it's correct.

PS. I can't remember if "Type" is a reserved word. If it is, it could be tripping up your query.
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
This is after redoing and building it in design view rather than trying to write it out.
Capture.PNG

SQL:
SELECT tblFixtureTypes.TypeName, tblProduct.Type
FROM (tblFixtureTypes INNER JOIN tblTypeTranslate ON tblFixtureTypes.TypeName = tblTypeTranslate.Type) INNER JOIN tblProduct ON tblTypeTranslate.TypeVar = tblProduct.Type;
This however doesnt return anything.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
This is after redoing and building it in design view rather than trying to write it out.
View attachment 86316
SQL:
SELECT tblFixtureTypes.TypeName, tblProduct.Type
FROM (tblFixtureTypes INNER JOIN tblTypeTranslate ON tblFixtureTypes.TypeName = tblTypeTranslate.Type) INNER JOIN tblProduct ON tblTypeTranslate.TypeVar = tblProduct.Type;
This however doesnt return anything.
Did you populate tblTypeTranslate with the type variations?
 

tmyers

Well-known member
Local time
Today, 14:37
Joined
Sep 8, 2020
Messages
1,090
I had tinkered with this for a bit, but can't seem to get it to work.
I have shelved it for the moment, as while messing with it, I found issues elsewhere in my table and form.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:37
Joined
Oct 29, 2018
Messages
21,358
I had tinkered with this for a bit, but can't seem to get it to work.
I have shelved it for the moment, as while messing with it, I found issues elsewhere in my table and form.
Okay. Let us know when you're ready to take another look. Cheers!
 

Users who are viewing this thread

Top Bottom