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.
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.