Query Results Restrictive

[Guest]

Note: I have the message in MS Word. I did not see where I could upload the
file in this forum. What is here is copy/paste from MS Word. I can send an
file of the question below, I know it would be easier to read

I have a commercial data base to capture data. I can't query the data very
easy within it so I have to OBDC link to the data from a db I created.

START....

Table definitions:

1. DATA ENTRY INCLUDE THESE FIELDS

Sample Name Sample Number Additive Code 1
Additive 1 Spec Additive Code 2 Additive 2 Spec Additive Code 3 Additive 3
Spec Additive Code 4 Additive 4 Spec
AA 100 2 100 ppm 4 200 ppm
AB 101
B1 102 5 300 ppm 6 400 ppm
B2 103 10 700 ppm
C1 104 11 500 ppm 200 600 ppm 4 1 % 2 1800 ppm
C2 105


2. I have access to other Tables for Additive Codes

Additive Code Additive Name
2 B
4 Z
5 AA
6 ZZZ4
10 78
11 GH
200 FGD






I want to make a query that takes DATA ENTRY and substitutes “Additive Nameâ€
for “Additive Code†for 1 – 4 so the result of the query yields:

Sample Name Sample Number Additive Code 1
Additive 1 Spec Additive Code 2 Additive 2 Spec Additive Code 3 Additive 3
Spec Additive Code 4 Additive 4 Spec
AA 100 B 100 ppm Z 200 ppm
AB 101
B1 102 AA 300 ppm ZZZA 400 ppm
B2 103 10 700 ppm
C1 104 GH 500 ppm BFD 600 ppm Z 1 % B 1800 ppm
C2 105


As of Now… My query is setup similar to… NOTE>>> Here is a graphic of my
query design














If a line doesn’t include 4 Additive Codes it is not shown in the results

My query now returns:

Sample Name Sample Number Additive Code 1
Additive 1 Spec Additive Code 2 Additive 2 Spec Additive Code 3 Additive 3
Spec Additive Code 4 Additive 4 Spec
C1 104 GH 500 ppm BFD 600 ppm Z 1 % B 1800 ppm


What changes do I need to make?

STOP... Thank you

 

[Duane Hookom]

It is difficult to understand your table structure and data. If you have
spaces in your field names (bad idea), you should wrap the name in []s so
you would show something like:
[Sample Name] [Sample Number] [Additive Code 1] [Additive 1 Spec]

Your table is un-normalized so it requires joining to your code table
multiple times. You may need to modify your joins by double-clicking the
join lines and selecting the proper option.

I would normalize the tables if at all possible.