ComboBox Search

[xps35]

I think you're trying to fit way too much into one table.
When I try to analyze your table tStock, I come to the following.
A "projectile" has a caliber. Seems clear to me.
A "brass" also has 1 caliber. Also natural.
A "powder" can apparently be used for one or more calibers. As mentioned before, you have to put the calibers of a powder in a separate table.


Apparently "primers" can also be used for one or more calibers.
When designing your database, it is important to consider what you are going to use it for. On the one hand, this can be keeping up with the stock (of brasses, powders, primers and projectiles). On the other hand, you can record the coherence between those different components.
I have the impression that those two things are now intertwined. For example: the stock of a powder has nothing to do with what it can be used for. So you have to model these two aspects separately. So you will first have to determine the purpose of your database and then put your design in order.

[Welshgasman]

Your combo is linked to CaliberID in the subform, nothing to do with Caliber?

I think you would need to Union each caliber for your query and then link to Caliber, not CaliberID?

This all because you will not normalize, and is only going to get worse.

[JMZLR]

Welshgasman
I did you get the DB?

[Welshgasman]

Yes, but I cannot see how you can link to three different fields in the subform. Access only gives provision for one?
I can only think that you create a union query, one query for each field, and use that as the subform source.

Not sure how far you could get with amending the data though, as not something I have ever done or want to do.
You can only experiment I suppose.

The experts here might have other ideas, but I am out of them.

[JMZLR]

I do thank you for your time and effort. I will continue this quest until I or someone figures it out and when that happens I will be happy to share the results
with you.
Right now I'm trying to figure out the combination of SQL's WHERE - OR - And, because I feel my answer lies with in those three bugers.

[davegri]

I think this project is an exercise in overkill. The number of records is tiny and the requirement to maintain an inventory could be handily served by an Excel spreadsheet looking like your table tStock. Excel doesn't give a hoot about normalization.

[ArviLaanemets]

Excel doesn't give a hoot about normalization.

..., but applying some of it (all data organized into Tables, having registry Tables used as Data Validation sources, having any column only single type of data, etc.), will allow user to get much more (and much easily) info out of stored data. But for many Excel users it is only a modern paper sheet.

[Welshgasman]

I woke up early this morning, and started thinking about this That meant I was not going to get to sleep.

Your data structure does not allow you get what you want as you are also using incorrect fields to link. You are doing 50% one way and 50% another way.

Amending the linkage and code to suit your data (as I said hoops to jump through), this might be what you want?

I have only tested on 44 Magnum, as Beowolf is only in caliber1 ? and StockCaliber was for testing as well.

I still feel I am giving you a loaded gun with no mention of gun safety, or even telling you which end the bullet comes out of.

Edit: FWIW I had to normalize your data to get the result, hence the StockCaliber field.

[JMZLR]

Welshgasman
Nice job, you are truly a VIP! You came back pack'n a huge roll of Duc-Tape to solve an ugly problem. Hats' Off.

First off let me explain something, this is my personal DB it's not being sold or passed round, unless someone downloads it from here.
I'm 71 and was trying my hand at Access and this database was fun to do, even though it made everyone shiver. I was always told you can't get good at something if you don't jump in and learn how to fix what you broke. I even sat here last night and watched a 1 hour video on SQL Basic and can't believe the possibilities'.

Now to explain the screen view you posted. The 44 Magnum is in Caliber1 because the Brass & Projectiles shown are unique to that Caliber 44 and any other caliber would be in the same column also. Now 44 M appears in Caliber2 because the Powder N350 is used in 50 Beowulf and the 44 Magnum with the 50 Beowulf being a larger caliber it sets in Caliber1 for that Powder Type (all caliber are ranked by size) hence 44 Magnum is in Caliber2. In Caliber3 the 44 Magnum sets because the Primer is used for the 50 Beowulf, 45 ACP and the 44 Magnum etc. I'll stop here before the shivers become tremors.

I like what you did for me and it is a valued lesson, so here is my question, would you show me how it should have been set up as a normalized database, with out loosing any sleep. "chuckle"

Oh, this may be the problem, my wife has always told me that being left handed I always do things bass-ackwards. I just smile and think to my self she's left handed to, what a pair.

[Welshgasman]

I would probably start with making a Manufacturer table and put them in there.
Then your tStock stable would be along the lines of

tStockID
ComponentIDFK 'FK is foreign key from tblComponent
ManufacturerIDFK 'I'll let you work that one out
CaliberIDFK 'I'll let you work that one out again
CaliberIndex ' If you need to know whether it would be 1 ,2 or 3 etc That could be populated correctly when you select a component.
then the rest of your fields.

I have a similar setup for a DB of mine that supports a website. The heart of the system is a table called Links, which is just what it says, it holds links to 4 other tables, but with that table I can enter data (all ID FK) for a date, a ship, a Rank and a CrewMember

So that table just consists of 5 fields of Long datatype, 1 for itself and 4 for the linked tables.

That then can produce reports that support www.bibby-gazette.co.uk

Edit: If the Item can exist in more than one record, then I would have a table for them as well?
I would probably use combos for all the selections, as I did in my DB.

Good luck with the project, do not use age as an excuse, I am 68, retired and also not a professional developer.

[JMZLR]

I can't thank you enough for your help and encouragement.
I will try to recreate the Stock database so it will be Normalized but first I should read and understand the In's and Out's. Ahh Retirement.

My goal with database if I haven't already said, is to keep my stock current so when I select a caliber it will show me how many cartridges I can make for the selected Caliber.
To prepare for a Target Shooting Event, I was always counting components and I would either reload or buy. And, with the current events supplies is either to expensive or not available.
This database should help me stay ahead of the game at least until price drops or availability occurs.

Oh, should I remove the creped database?

Question: are you Paul?

[Welshgasman]

No, you have a perfectly good subform arrangement. I would use your db, as had to alter links and combo source to get what you wanted.
You do not have much data, so I would probably do it manually. Rename tstock to tstockold and create the new table as tstock, then copy and paste, or use a data entry form to recreate tstock. Should be mostly combos except for the amount etc controls.

Yes, I am Paul.

Various members have posted links for normalization, have a search for them.

[JMZLR]

It's a pleasure.
Well I did cut tStock up a little, only to show you the different scenarios that occur because of the primer and powder.
tStock table has 102 records so copy and paste it is. The Manufacture table you say put them in their, are saying the Component names or Caliber names?
And one more question, the ComboBoes you talk about, they are for?

[Welshgasman]

The combos would be to add new data. There has to be new ranges of anything in that db surely?.
If you are comfortable with queries, then use those to construct the populate the tables, but I do not think you have that knowledge yet, and easy to get wrong, so make sure yiu back up what you have now.
No if you have multiple manufacturers, which you have, they should be in their own table, and you store their PK as a FK in any table they are linked to.

[Welshgasman]

See post #5 here https://www.accessforums.net/showthread.php?t=86488