ComboBox Search

[JMZLR]

Hello All

I created an Access database to keep track of some reloading Powders that are assigned to different Calibers.


The table structure is ID, Powder, Cal1, Cal2, Cal3, this is where I associate Powder to different Calibers. I also have Form and SubForm with a combo box on the form where the Caliber is selected from a Caliber Table (ID, Caliber).


The problem comes in where some Powders work with multiple Calibers and others only work with one. When a Caliber is selected the only data that populates the SubForm is Cal1 and that's if the Caliber is in Cal1, it may be in Cal2 or Cal3.




What I need is when a Caliber is selected in the ComboBox it looks at Cal1, Cal2 and Cal3 and populate the SubForm with records associated with that Caliber.

Any ideas on this one.

[Welshgasman]

Structure your db correctly. Each caliber should be a separate record.

[JMZLR]

So, no work around?

[Welshgasman]

There is always a workaround. Just depends on how many hoops you want to jump through?
Have another combo with 1 to 3 and use that to indicate which caliber to populate?

[JMZLR]

Don't understand that one?

[Welshgasman]

You appear? to want to populate caliber2 instead of caliber1 for a single caliber powder?
If you leave your db as it is, no need for subform as all the fields are in the same record, so just have 3 separate controls for each.
Populate each in turn, so if cal1 is mt populate that, if cal1 has a value, use 2 etc.
As I said, hoops to jump through.

[JMZLR]

Your right and I'm no good at hoops either.
I'll drop that idea.

Thank you for your time.

[Welshgasman]

Go with a normalized DB, then your subform idea would work.
I would expect
tblPowder
PowderID
PowderName

tblCaliber
CaliberID
CaliberDescription

tblPowderCaliber Junction table
PowderCaliberID
PowderIDFK
CaliberIDFK

Then you can have 1 to n Calibers etc.
So if a powder uses 1 caliber, you have one record, it is uses 3 you have 3 records.

If you still need one record with just caliber2, then you can use that method I suggested with a second combo to indicate which caliber to populate.

[JMZLR]

Welshgasman
Thank you for your explanation. Sorry for just getting back but I went on vacation.
My SubForm works great the only thing I can't figure out is how to have the query SQL look at the other two fields (Cal2 and Cal3).
The Brass and Projectiles are unique in that they only fit the caliber they are made for where the Primer or Powder will work with multiple calibers.
This is why there are two other fields Cal2 and Cal3. When a Caliber is selected the SQL retrieves all the rows where the selected caliber appears and that is in Cal1 But it could also
appear in Cal2 or Cal3 if the component happens to be a Primer or Powder.
That why I need to look at the other two Fields and I can not get the WHERE in SQL query to look there because I really don't know SQL, just what I've seen in the Query.

I can show you more if interested.

[Welshgasman]

You would likely need an OR for each of the 'other' caliber fields.
So add the criteria on a new line for the other two fields.

If you went the normalized route, you would get all records that matched that caliber, be it Cal1, Cal2, Cal3 or ............. Caln and you would just be searching in one place.

There is a reason why people recommend normalization.

[ArviLaanemets]

To JMZLR!

There is a reason why people recommend normalization.

Another thing to consider: Let's assume you get your form(s) working now. Someday in future there is a need to add 4th Caliber.
With your current design, you have to edit the table, then to edit form(s), then to edit all queries (when used) and reports;
With normalized design, you have to add new Caliber entry into Calibers table. It's all!

[JMZLR]

I understand the normalization process I would have one record for one Primer associated to one Caliber, right. There are in my DB thirteen different Primer Types,
Small Pistol, Small Pistol Magnum, Small Pistol Match
Large Pistol, Large Pistol Magnum, Large Pistol Match
Small Rifle, Small Rifle Magnum, Small Rifle Match, Small Rifle Military Magnum
Large Rifle, Large Rifle Match, Large Rifle Magnum Match


Now with thirteen (13) different Primer Types times eleven (11) different Calibers, creating One Hundred and Forty Three (143) Records using a Normalized Database.
My Database has only thirteen (13) records one for each Primer Type and three Fields to cover all calibers associated with one Primer. And no future Calibers. I know Not Normalized.


We could do the same math as above to calculate total records needed for the Two Hundred and Two (202) different Powder Types which cover
the Eleven Calibers and creating Two Thousand Two Hundred and Twenty Two (2,222) more Records.


Now all the Brass and Projectile's are UNIQUE as they will only fit one Caliber and every Caliber will appear in the Field Caliber1 and this is where
my problem comes to light. To find out how many Cartridges I can reload I need the SQL to not just look at Caliber1 but also scan Caliber2 and Caliber3 for a Caliber match, this will cover all possibilities of the selected Caliber appearing in Caliber2 or Caliber3 when looking for possible Powder and Primers.


A WHERE statement in the SQL will take care of this problem. I don't know SQL so I need someone's help. This is as close as I can get:

WHERE [tStock]![Caliber1] = cboCaliber OR [tStock]![Caliber2] = cboCaliber OR [tStock]![Caliber3] = cboCaliber

[Welshgasman]

Your math is a little off?
You only have records for Primer and Calibers. You are saying all primers have 3 calibers, which is not what you stated earlier.
Besides 143 records is nothing.

Your SQL above should work, so time to start uploading the DB?

[JMZLR]

Strike 2, my DB and Math are wrong... I'll create a smaller table for upload a long with the Form, SubForm and Query.
When you run the Form select 50 Beowulf from the ComboBox I will show you all three Calibers in the SubFrom which are normally
not in there, I put them there so you could see the correlation. 50 Beo shows in Caliber1, Caliber2 show 45 ACP etc. if you look at the Primer name
then select 45 ACP the Primer is not there, because the SQL did not scan Caliber 2 or Caliber3. I can't explain it any better than that, I'm sorry.

Play a round with it and you will see what I mean. I placed 28 records in the Table to cover most scenarios, Zero quantity, blank rows, etc.

I'll send the DB shortly if I don't screw that up to. Hence, Strike 3.

[JMZLR]

The DB shows in the attachment.