Query Results Restrictive

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top