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.
Jeff Boyce said:
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 said:
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