Extracting data from fields

[June7]

You need at least 3 tables.

tblRadios
RadioID


RadioName

tblTubes
TubeID
TubeModel

tblRadioTubes - this is a "junction" table. There will be a record for each radio/tube pair. Fields would be at a minimum:
RadioID_FK
TubeID_FK


Did you read any of the references on data normalization?

[Micron]

Done it again?

No this is the correct thread. The quote is from post #13 in this thread.

My bad. Need more coffee I guess.

[moke123]

I want one empty box where I can type 12sk7 and it brings up all the radios that use that tube.

While I agree you have a data problem its still do-able.

[Minty]

I ran that test program and while it looks interesting, the FORM is to complicated and full of useless information. I want one empty box where I can type 12sk7 and it brings up all the radios that use that tube.

Be careful what you want for, or at least learn to take advice when it is offered from a possibly reliable source.

If your data is stored in a sensible (normalised) fashion the searching for a tube becomes trivial no matter how many tubes you have.
if you suddenly have a tube 7 , 8 or 25 none of the solutions offered would work very efficiently.

See the attached with the search box, which was simple to make work because the data is stored correctly.

[daveone23]

I see the search box and clear button but neither work. What am I missing here? But anyways, that is the concept iam looking for.

[moke123]

I see the search box and clear button but neither work. What am I missing here? But anyways, that is the concept iam looking for.

did you remove the mark of the web?

[Welshgasman]

I see the search box and clear button but neither work. What am I missing here? But anyways, that is the concept iam looking for.

Works fine for me?

[moke123]

Works fine for me?

Same here. no problems except Minty left out 12SK7

[Welshgasman]

Same here. no problems except Minty left out 12SK7

That was in record #8 ?

[Minty]

Same here. no problems except Minty left out 12SK7

Hey, I was rushing...

[daveone23]

I have been trying to figure out how things are put together but I still believe the data base is messed up. I typed 12sa7 and it comes up with the airline radio. That radio does not use that tube.

[Minty]

I only loaded a very small subset of the data into that form / underlying tables, and did it in a hurry so it might be a little erroneous in places.

If you clear the filter and add or correct the existing data (the form is dual purpose at the moment) it will show you the correct results.
Apparently I missed a tube out - you'll need to add it to the main tbTubes.

Obviously you would normally build forms to maintain both the Radios and Tube listings.
But you aren't paying me enough to do all that

[daveone23]

Minty, I am getting the hang of it!!! Thanks for the help. One issue I see is if I have 500 tubes entered and I am entering another one, I want it to pop up and say, hey you already have that one. It would also be nice to have an easy front end form
without going thru all the tables to update stuff for those users who do not know how to use access.

[Micron]

One way would be to make the table field indexed, no dupes and Access will raise a message and not allow the dupe. Or you can go a more complicated route and use code to look up a value before the record is inserted. Or you can use Not In List Event for the combo (also more complicated). You can also create a form just for adding data for list items and use that as the List Items Edit form (see property sheet).

[daveone23]

Minty, there is one thing I really need in the database. On the pull down you created for the search, I need those tubes sorted A-Z to make them easier to find. I may not want to type in the number, just scroll down. Thanks. Just show me how to do it so I can add it to my populated data base. Thanks