Union qry- results show #'s not words

[sooz9]

I reached a roadblock on my current database. I have multiple tables for
data entry for each type of product we run. For example, product Crag 4/6
has it's own table of data entry and Island 4/6 has it's own. Each product
has it's own lookup table of flavors depending on the product. Craig 4/6 has
it's own flavor list and so on. These flavors have a unique ID. I used a
union querry to combine all the different types of flavors to one list.
I then made a union qry to combine al the data from all the different
product mixes so I could use this qry to run reports and so forth. The
problem is the Union qry (that combines all the data from all the tables)
shows my ID numbers not the test of what the flavor is.

SQL from Union qry= combines all the tables data
SELECT *
FROM [YTD Craigmont 6 pack Log] <--( a qry producing calculated data)
UNION SELECT *
FROM [YTD Island 6 pack Log]; <--( a qry producing calculated data)

(this qry produces the ID for flavors not the words)

My table that has all the flavors is called "All Flavors"

How can I get the SQL to report the flavors as the test not the ID

 

[Jeff Boyce]

Hold on a sec...

You have tables specific to each product? That sounds like a spreadsheet,
not a relational database.

Please provide an example of your table(s) (such as a table name, field
names, example data in fields).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[sooz9]

Tbl Craig 4/6
ID- number
Flavor- test

Tbl Caig 4/6 Log
Date
Flavor- Look-up tbl Craig 4/6 for a drop down a different flavors
Filler speed- number
cases per hour- number

Tbl Island 4/6
ID- number
Flavor- text

Tbl Island 4/6 Log
Date
Flavor- Look-up tbl Island 4/6 for a drop down a different flavors
Filler speed- number
cases per hour- number

The database has other product types and flavors in it but if I can get it
to work for 2 then I can get it to work for more. The purpose of the
database is to track the performance of different flavors we run each day
etc.

Hold on a sec...

You have tables specific to each product? That sounds like a spreadsheet,
not a relational database.

Please provide an example of your table(s) (such as a table name, field
names, example data in fields).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I reached a roadblock on my current database. I have multiple tables for
data entry for each type of product we run. For example, product Crag 4/6
has it's own table of data entry and Island 4/6 has it's own. Each product
has it's own lookup table of flavors depending on the product. Craig 4/6
has
it's own flavor list and so on. These flavors have a unique ID. I used a
union querry to combine all the different types of flavors to one list.
I then made a union qry to combine al the data from all the different
product mixes so I could use this qry to run reports and so forth. The
problem is the Union qry (that combines all the data from all the tables)
shows my ID numbers not the test of what the flavor is.

SQL from Union qry= combines all the tables data
SELECT *
FROM [YTD Craigmont 6 pack Log] <--( a qry producing calculated data)
UNION SELECT *
FROM [YTD Island 6 pack Log]; <--( a qry producing calculated data)

(this qry produces the ID for flavors not the words)

My table that has all the flavors is called "All Flavors"

How can I get the SQL to report the flavors as the test not the ID

 

[sooz9]

Craig 4/6 (table of flavors)
ID Craig 4/6
1 CRAG COLA FRNCH 4/6/12 OZ
2 CRAG GRP FRNCH 4/6/12 OZ
3 CRAG LEM LIME FRNCH 4/6/12 OZ
4 CRAG RTBR FRNCH 4/6/12 OZ
5 CRAG STWBRY FRNCH 4/6/12 OZ
6 CRAG ORNG FRNCH 4/6/12 OZ
7 CRAG TROP FRNCH 4/6/12 OZ

YTD Craigmont 6 pack Log (this is a qry) but the results I want to use in
my Union qry

Date Period #: Week #: Day Shift Night Shift Flavor: Filler Speed: Run Time
(hrs) C/L CPH Percentage
17-Jan-08 1 3 Yes No CRAG TROP FRNCH 4/6/12 OZ 1236 0.95 1917.89 63.93%
23-Jan-08 1 4 No Yes CRAG GRP FRNCH 4/6/12 OZ 1240 0.83 2169.60 72.32%
23-Jan-08 1 4 No Yes CRAG ORNG FRNCH 4/6/12 OZ 1240 1.08 3057.23 101.91%
24-Jan-08 1 4 Yes No CRAG LEM LIME FRNCH 4/6/12 OZ 1168 1.15 1748.70 58.29%
24-Jan-08 1 4 Yes No CRAG COLA FRNCH 4/6/12 OZ 1235 3.13 2538.19 84.61%
06-Feb-08 2 2 Yes No CRAG COLA FRNCH 4/6/12 OZ 1240 0.68 3263.41 108.78%
19-Feb-08 2 4 Yes No CRAG COLA FRNCH 4/6/12 OZ 1238 1.45 2867.59 95.59%
19-Feb-08 2 4 Yes No CRAG STWBRY FRNCH 4/6/12 OZ 1240 1.17 2187.43 72.91%
19-Feb-08 2 4 Yes No CRAG GRP FRNCH 4/6/12 OZ 0.83 2558.40 85.28%
20-Feb-08 2 4 Yes No CRAG ORNG FRNCH 4/6/12 OZ 1.15 2062.61 68.75%
22-Feb-08 2 4 Yes No CRAG RTBR FRNCH 4/6/12 OZ 1238 0.77 3315.65 110.52%
25-Feb-08 3 1 Yes No CRAG COLA FRNCH 4/6/12 OZ 1240 2.25 1964.00 65.47%
27-Feb-08 3 1 Yes No CRAG TROP FRNCH 4/6/12 OZ 1239 1.17 2036.57 67.89%
05-Mar-08 3 2 Yes No CRAG LEM LIME FRNCH 4/6/12 OZ 1180 1.18 2297.75 76.59%
11-Mar-08 3 3 Yes No CRAG GRP FRNCH 4/6/12 OZ 1236 1.22 2276.71 75.89%
11-Mar-08 3 3 Yes No CRAG ORNG FRNCH 4/6/12 OZ 1237 2.03 1151.80 38.39%
12-Mar-08 3 3 Yes No CRAG COLA FRNCH 4/6/12 OZ 1237 1.92 2193.91 73.13%

Island 4/6 (flavors for this product)
ID Island flavors
8 ISLAND COLA 4/6/12 OZ
9 ISLAND DEW FRNCH 4/6/12 OZ
10 ISLAND GRP 4/6/12 OZ
11 ISLAND LEM LIME 4/6/12 OZ
12 ISLAND ORNG 4/6/12 OZ
13 ISLAND PNP FRNCH 4/6/12 OZ
14 ISLAND RTBR 4/6/12 OZ
15 ISLAND STWBRY 4/6/12 OZ
16 ISLAND TROP FRNCH 4/6/12 OZ

Island 6 pack Log (this is a qry) but the results I want to use in my Union
qry

Date Period #: Week #: Day Shift Night Shift Flavor: Filler Speed: Run Time
(hrs) C/L CPH Percentage Start Time: Comments
14-Jan-08 1 3 Yes No ISLAND ORNG 4/6/12 OZ 1232 1.00 2814.00 93.80% 6:15
14-Jan-08 1 3 Yes No ISLAND PNP FRNCH 4/6/12 OZ 1242 1.33 1619.25 53.98% 8:10
15-Jan-08 1 3 Yes No ISLAND DEW FRNCH 4/6/12
OZ 1237 0.52 3594.19 119.81% 7:15
17-Jan-08 1 3 Yes No ISLAND GRP 4/6/12 OZ 1200 0.52 3214.84 107.16% 7:15
19-Feb-08 2 4 Yes No ISLAND PNP FRNCH 4/6/12 OZ 1239 0.80 2783.75 92.79% 9:52
19-Feb-08 2 4 Yes No ISLAND TROP FRNCH 4/6/12
OZ 1240 1.05 2193.33 73.11% 10:46
05-Mar-08 3 2 Yes No ISLAND PNP FRNCH 4/6/12 OZ 1235 1.18 2345.92 78.20% 8:29

What I am getting is:
Date Period #: Week #: Day Shift Night Shift Flavor: Filler Speed: Run Time
(hrs) C/L CPH Percentage Start Time: Comments
01/02/2008 1 1 Yes No 13 1235 1.87 2117.68 70.59% 13:28
01/02/2008 1 1 Yes No 2 1226 1.07 2370.00 79.00% 12:24
01/02/2008 1 1 Yes No 23 1236 5.15 1781.75 59.39% 7:15
01/02/2008 1 1 Yes No 6 1238 1.42 1578.35 52.61% 15:02
01/02/2008 1 1 No Yes 18 1237 0.92 2221.09 74.04% 16:27
01/03/2008 1 1 Yes No 11 1165 2.57 1535.84 51.19% 12:35
01/03/2008 1 1 Yes No 18 1238 0.90 3236.67 107.89% 7:15
01/03/2008 1 1 Yes No 19 1162 2.03 2236.23 74.54% 15:09
01/03/2008 1 1 Yes No 21 1165 4.43 1913.91 63.80% 8:09
01/03/2008 1 1 No Yes 19 1240 2.25 2344.44 78.15% 20:00
01/04/2008 1 1 Yes No 15 1240 6.25 2728.00 90.93% 7:15 Adj- ran out of
materials
01/04/2008 1 1 No Yes 14 1204 1.58 2288.21 76.27% 17:45
01/04/2008 1 1 No Yes 24 1230 1.08 2048.31 68.28% 19:50
01/04/2008 1 1 No Yes 3 1240 0.92 3327.27 110.91% 21:20
01/07/2008 1 2 No Yes 23 1240 3.55 2391.55 79.72% 17:04
01/08/2008 1 2 No Yes 23 1240 2.00 2553.00 85.10% 20:00
01/09/2008 1 2 No Yes 23 1240 2.00 2370.00 79.00% 20:15
01/10/2008 1 2 No Yes 1 1240 0.67 3262.50 108.75% 20:30
01/10/2008 1 2 No Yes 5 1240 1.17 1665.43 55.51% 21:20
01/14/2008 1 3 Yes No 12 1232 1.00 2814.00 93.80% 6:15
01/14/2008 1 3 Yes No 13 1242 1.33 1619.25 53.98% 8:10

I deleted some of the data return results because it returns a lot of
records. As you can see for flavor: I am getting numberic values.

Here is my table that has all the flavors

All can Flaovrs with ID (table)
ID All Can Flavors
1 CRAG COLA FRNCH 4/6/12 OZ
2 CRAG GRP FRNCH 4/6/12 OZ
3 CRAG LEM LIME FRNCH 4/6/12 OZ
4 CRAG RTBR FRNCH 4/6/12 OZ
5 CRAG STWBRY FRNCH 4/6/12 OZ
6 CRAG ORNG FRNCH 4/6/12 OZ
7 CRAG TROP FRNCH 4/6/12 OZ
8 ISLAND COLA 4/6/12 OZ
9 ISLAND DEW FRNCH 4/6/12 OZ
10 ISLAND GRP 4/6/12 OZ
11 ISLAND LEM LIME 4/6/12 OZ
12 ISLAND ORNG 4/6/12 OZ
13 ISLAND PNP FRNCH 4/6/12 OZ
14 ISLAND RTBR 4/6/12 OZ
15 ISLAND STWBRY 4/6/12 OZ
16 ISLAND TROP FRNCH 4/6/12 OZ

I didn't include all the flavors on this posting. The flavors IDs in this
table should be matching the results I get from my UNION qry.

Hold on a sec...

You have tables specific to each product? That sounds like a spreadsheet,
not a relational database.

Please provide an example of your table(s) (such as a table name, field
names, example data in fields).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I reached a roadblock on my current database. I have multiple tables for
data entry for each type of product we run. For example, product Crag 4/6
has it's own table of data entry and Island 4/6 has it's own. Each product
has it's own lookup table of flavors depending on the product. Craig 4/6
has
it's own flavor list and so on. These flavors have a unique ID. I used a
union querry to combine all the different types of flavors to one list.
I then made a union qry to combine al the data from all the different
product mixes so I could use this qry to run reports and so forth. The
problem is the Union qry (that combines all the data from all the tables)
shows my ID numbers not the test of what the flavor is.

SQL from Union qry= combines all the tables data
SELECT *
FROM [YTD Craigmont 6 pack Log] <--( a qry producing calculated data)
UNION SELECT *
FROM [YTD Island 6 pack Log]; <--( a qry producing calculated data)

(this qry produces the ID for flavors not the words)

My table that has all the flavors is called "All Flavors"

How can I get the SQL to report the flavors as the test not the ID

 

[Jeff Boyce]

Consider spending a bit of time brushing up on "relational" and
"normalization". The features/functions in Access pre-suppose that you've
provided well-normalized data ... if you feed it 'sheet data, you (and
Access) will have to work MUCH harder to do simple things.

For instance, another way to organize your data might be:

tblProduct
ProductID
ProductName (I can't tell from your description if this is "Craig",
"Island", "...)
FillerSpeed (I can't tell ... if this is a characteristic of the
product or the flavor)
(?cases per hour ... this seems like it could be calculated from
FillerSpeed. If so, don't store it -- redundant!)

tblFlavor
FlavorID
FlavorTitle
FlavorDescription

trelProductFlavorTesting
TestID
ProductID
FlavorID
TestDate
(?TestResult -- I can't tell ...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Tbl Craig 4/6
ID- number
Flavor- test

Tbl Caig 4/6 Log
Date
Flavor- Look-up tbl Craig 4/6 for a drop down a different flavors
Filler speed- number
cases per hour- number

Tbl Island 4/6
ID- number
Flavor- text

Tbl Island 4/6 Log
Date
Flavor- Look-up tbl Island 4/6 for a drop down a different flavors
Filler speed- number
cases per hour- number

The database has other product types and flavors in it but if I can get it
to work for 2 then I can get it to work for more. The purpose of the
database is to track the performance of different flavors we run each day
etc.

Hold on a sec...

You have tables specific to each product? That sounds like a
spreadsheet,
not a relational database.

Please provide an example of your table(s) (such as a table name, field
names, example data in fields).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I reached a roadblock on my current database. I have multiple tables
for
data entry for each type of product we run. For example, product Crag
4/6
has it's own table of data entry and Island 4/6 has it's own. Each
product
has it's own lookup table of flavors depending on the product. Craig
4/6
has
it's own flavor list and so on. These flavors have a unique ID. I used
a
union querry to combine all the different types of flavors to one list.
I then made a union qry to combine al the data from all the different
product mixes so I could use this qry to run reports and so forth. The
problem is the Union qry (that combines all the data from all the
tables)
shows my ID numbers not the test of what the flavor is.

SQL from Union qry= combines all the tables data
SELECT *
FROM [YTD Craigmont 6 pack Log] <--( a qry producing calculated data)
UNION SELECT *
FROM [YTD Island 6 pack Log]; <--( a qry producing calculated data)

(this qry produces the ID for flavors not the words)

My table that has all the flavors is called "All Flavors"

How can I get the SQL to report the flavors as the test not the ID

 

[sooz9]

Thanks, i'll consider revising. However, I still have teh same problem with
non-table like data.

For example, Use enters downtime reason for machines
Table Downtime Entry
ID- Number
Reason: text <-- looks up different downtime reasons
Comments:

Table Downtime Reasons
ID- Number
Reasons: text

When I run the following qry expr I get the following results.
Expr1: [Reason] & "-" & [comments:]

Results are: 3 - valve broke
Results I want are: Filler - valve broke with 3 being the ID for Filler

Thanks for you assistane.

Consider spending a bit of time brushing up on "relational" and
"normalization". The features/functions in Access pre-suppose that you've
provided well-normalized data ... if you feed it 'sheet data, you (and
Access) will have to work MUCH harder to do simple things.

For instance, another way to organize your data might be:

tblProduct
ProductID
ProductName (I can't tell from your description if this is "Craig",
"Island", "...)
FillerSpeed (I can't tell ... if this is a characteristic of the
product or the flavor)
(?cases per hour ... this seems like it could be calculated from
FillerSpeed. If so, don't store it -- redundant!)

tblFlavor
FlavorID
FlavorTitle
FlavorDescription

trelProductFlavorTesting
TestID
ProductID
FlavorID
TestDate
(?TestResult -- I can't tell ...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Tbl Craig 4/6
ID- number
Flavor- test

Tbl Caig 4/6 Log
Date
Flavor- Look-up tbl Craig 4/6 for a drop down a different flavors
Filler speed- number
cases per hour- number

Tbl Island 4/6
ID- number
Flavor- text

Tbl Island 4/6 Log
Date
Flavor- Look-up tbl Island 4/6 for a drop down a different flavors
Filler speed- number
cases per hour- number

The database has other product types and flavors in it but if I can get it
to work for 2 then I can get it to work for more. The purpose of the
database is to track the performance of different flavors we run each day
etc.

Hold on a sec...

You have tables specific to each product? That sounds like a
spreadsheet,
not a relational database.

Please provide an example of your table(s) (such as a table name, field
names, example data in fields).

Regards

Jeff Boyce
Microsoft Office/Access MVP


I reached a roadblock on my current database. I have multiple tables
for
data entry for each type of product we run. For example, product Crag
4/6
has it's own table of data entry and Island 4/6 has it's own. Each
product
has it's own lookup table of flavors depending on the product. Craig
4/6
has
it's own flavor list and so on. These flavors have a unique ID. I used
a
union querry to combine all the different types of flavors to one list.
I then made a union qry to combine al the data from all the different
product mixes so I could use this qry to run reports and so forth. The
problem is the Union qry (that combines all the data from all the
tables)
shows my ID numbers not the test of what the flavor is.

SQL from Union qry= combines all the tables data
SELECT *
FROM [YTD Craigmont 6 pack Log] <--( a qry producing calculated data)
UNION SELECT *
FROM [YTD Island 6 pack Log]; <--( a qry producing calculated data)

(this qry produces the ID for flavors not the words)

My table that has all the flavors is called "All Flavors"

How can I get the SQL to report the flavors as the test not the ID

 

[John W. Vinson]

Thanks, i'll consider revising. However, I still have teh same problem with
non-table like data.

For example, Use enters downtime reason for machines
Table Downtime Entry
ID- Number
Reason: text <-- looks up different downtime reasons
Comments:

Table Downtime Reasons
ID- Number
Reasons: text

When I run the following qry expr I get the following results.
Expr1: [Reason] & "-" & [comments:]

Results are: 3 - valve broke
Results I want are: Filler - valve broke with 3 being the ID for Filler

You are another victim of Microsoft's misdesigned, misleading, and confusing
Lookup Wizard misfeature.

The table *appears* to contain "Filler". It doesn't. What the table actually
contains (and what is quite properly being included in your query!) is 3. The
actual contents of the table are concealed from view!

What you need to do is *JOIN* the lookup table to your downtime table, and
pull the text description from the lookup field.