"SUB LOOK-UP" within 1 Query ???

[kev100 via AccessMonster.com]

Folks,

I've got a query which accesses 2 tables.

One table is a list of Daily Orders.

The other table is an Index of Item Codes (primary key is the item #, other
fields have item name, description, etc).

The one query includes these 2 tables. Each record in the Daily Order table
has multiple item numbers.

I am needing to run the query so that it includes the item numbers and the
corresponding item descriptions on the same line.

Example:

2 Records: The first has item number A100, B234, and C300

The second has A200, B350, and C170


When the Query is viewed...it needs to show 2 records with columns for each
of the item numbers along with the item descriptions.


- Any advice greatly appreciated. Thanks !

 

[Kardan via AccessMonster.com]

Hi Kev

From your posting it is not clear what it is that you are trying to do.

When first reading it seems like you want a JOIN query and then it seems like
you want a Crosstab Query.

Please post back with a clearer explanation and possibly a short simplified
example of the table records and what results you would like to see.

Regards

Richard

 

[kev100 via AccessMonster.com]

Thanks very much....

Here's the example tables for that query condition.

These are kind of simplistic, but accurately represent my situation.

What I'm having trouble with is that Each record in Table 1 requires Multiple
Queries of table 2.

TABLE 1 (Daily Orders)
Cust#-------------ORDER 1-----------ORDER 2----------------ORDER 3------------
-----ORDER 4

C234---------------A100------------------B234-----------------------C300------
-----------------[null]
C425---------------A200------------------B350-----------------------C170------
-----------------[null]



TABLE 2 (Item Desc)
Item#-----------------Desc.

A100-----------------HAMMER
A101-----------------3" NAILS
A102-----------------VG PLIERS

 

[John Spencer]

You need to add the Item Codes table multiple times and link each
incidence of the item table to one of the Order fields, that would give
you all the items in one row.

Better would be to fix the daily orders table structure so that it is
properly designed and has one row per combination of Cust# and Order field.

As a temporary fix you could use a UNION query to normalize the data and
then use the saved union query as one table in the query. UNION queries
cannot be built in the design view, but must use the SQL view.

SELECT [Cust#], [Order 1] as TheOrder
FROM [Daily Orders]
UNION ALL
SELECT [Cust#], [Order 2] as TheOrder
FROM [Daily Orders]
UNION ALL
SELECT [Cust#], [Order 3] as TheOrder
FROM [Daily Orders]
UNION ALL
SELECT [Cust#], [Order 4] as TheOrder
FROM [Daily Orders]

Now use that query (qFixed) and the Item Desc table
SELECT [Cust#], [Item#], [Desc.]
FROM qFixed INNER JOIN [Item Desc]
ON qFixed.TheOrder = [Item Desc].[Item#]

By the way, it is a good idea to have table and field names that consist
of ONLY letters, numbers, and the underscore character.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================