Extracting data from fields

[daveone23]

I am a novice when it comes to Access but I think I am getting close. See attached. I used the wizard to create a query and its close but one of my records (radios) has 2 of the same tubes, 12sk7, and its only reporting one. I am not sure where to enter the code or tweak this query.

[Micron]

Your main problem is that your table is designed like a spreadsheet. There may be a fix for your issue using what you have but you will continue to have more issues. Review database normalization. You really should have a table for radios, one for tubes and one that's known as a junction table. Imagine you get another radio and it has a tube that's not in your table. Now you have to redesign all your queries, forms, reports, etc. to accommodate this. That is the surest sign of design issues.

[daveone23]

I get having two tables but how do I tie a string of tubes to a certain radio on the other table?

[madpiet]

Getting warmer... More later. I'll restructure and explain what I'm doing. (Take heart - learning database stuff is HARD. Anybody who claims he didn't struggle at first is very likely lying.)

[Micron]

I get having two tables but how do I tie a string of tubes to a certain radio on the other table?

I could explain it but it has already been documented countless times, which is why I made this suggestion:

Review database normalization.

Another option is to wait until someone hands you the solution (the proverbial fish) and it looks like you won't have to wait long. But then how much will you learn about the principles behind the gift and where would that leave you next time? I think I'd be doing you the bigger favour but that's just me.

EDIT - here's the normalization links I usually pass on
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Welshgasman]

Why is this such a complicated code issue for a simple table. Say you wanted to know who had a blood pressure of 145. Would you have to code the query?


name JAN FEB MAR APRIL
TOM 90 80 88 145
MARK 120 90 113 123
JANE 140 100 123 101

Because you made it complicated.
What if you had to add another field?
You would have to change your code to allow for the new field.

[Micron]

WGM - Umm, I think that belongs in the other thread, where the problem is also based on lack of normalization?

[daveone23]

Ok, I think I made it into a database form. I just need a little more help to extract the data I want

[Micron]

Your tubes table is still a spreadsheet.

[daveone23]

But I have 5 or 6 fields per record. You said have one table for tubes. There are many tubes per radio

[Minty]

Here is what you need.
I'll let you fill out the rest of the data.

Data in databases is stored long and thin, and never stores data as part of a field.

See if this makes sense - now you can have 2, 3 or 50 tubes in a radio and the design accommodates it.

[Welshgasman]

WGM - Umm, I think that belongs in the other thread, where the problem is also based on lack of normalization?

Hmm, I posted that on my phone? No idea where that went wrong.

Thank you @Micron

[Welshgasman]

Done it again?

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

[orange]

Daveone23,

You said "i would say you dont understand my application".
I agree.
Please give readers the 30,000 ft overview of what this application is about --no quasi-database terms.
What issues does the proposed database application solve?

[daveone23]

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.