Querying data from two seperate tables

[Guest]

I’ll try and keep this simple. The database is used to retrieve retail
pricing for specific accounts based on their product lines or their SKU’s.

I receive the retail data for table SSP every morning from the AS400 via a
batch script. Therefore, the SSP table is wiped out and fresh set of records
is added daily. So if any changes are made by me to that table, those
changes are lost every morning.

The data that comes to me is: Acct# / Salesman# / SKU# / Retail

In some cases I have to add “false†products or SKU’s that are not actual
products but the retailers have modified existing packages to create more
popular packages not available from us.
Example: Schlitz Malt Liquor exists in a 12 PK CAN but does not exist in 6
PK CAN. The retailer might break the 12 PK down and sell it as two 6 PK’s.
Therefore I have to generate a retail price tag for this item. This item
will not have a traditional SKU code since we do not carry the SKU in that
configuration, therefore it will have to be entered manually into the SSP.

Once again, every morning a fresh set of information comes in from the
salesman’s handhelds and the “false†SKU’s are lost.

I created a table identical to the SSP called MANUAL. I am now entering all
of the “false†SKU’s into that table and pasting the rows into the SSP table
every morning.

I then run a query that prompts me for the Account Number. It then returns
the rows that are active SKU’s for the account with a description and graphic
as well as the retail. It is then utilized in a report that generates the
retail pricing strips for the account.

The query is as follows:
ACCT# from SSP (User Input)
SKU from SSP (Ascending)
P1 from Package Master (This provides a description of the package based on
the SKU #)
Retail from SSP
Graphic for Graphic Master (Provides the logo graphic designated by the SKU)

I need the query to take the Account number from the input and grab all the
records for that account from both the SSP table and the MANUAL table.

I think a UNION query will do it but how do I use multiple tables such as
SSP / Graphic Master / Package Master as well as how do you insert a command
for a user prompt for the account number portion?

Any and all help appreciated.

WGK

 

[Guest]

A UNION operation would be one way to go. Firstly create a query which
performs the UNION operation:

SELECT *
FROM SSP
UNION ALL
SELECT *
FROM Manual;

You could extend the above to join the GraphicMaster and PackageMaster
tables to SSP and manual in each part of the UNION and include the same
parameter in each, or create another query, with a single parameter which
joins the above query to the GraphicMaster and PackageMaster tables. Once
the SSP and manual tables are populated with up-to-date data this final query
should give you the combined results:

SELECT [Acct#], YourUnionQuery.SKU,
PI, Retail, Graphic
FROM YourUnionQuery, GraphicMaster, PackageMaster
WHERE GraphicMaster.SKU = YourUnionQuery.SKU
AND = PackageMaster.SKU = YourUnionQuery.SKU
AND [Acct#] = [Enter Account Number:]
ORDER BY YourUnionQuery.SKU;

BTW if the query is used as the RecordSource of a report don't use an ORDER
BY clause in the query but sort the report by its own internal Sorting and
Grouping mechanism.

An alternative approach would be, once the SSP table is repopulated, run an
'append' query to add the rows from your Manual table to it. You can then
just join the expanded SSP table to the other two tables and include a
parameter on the Acct# column, i.e. the query would be exactly as above but
with SSP in place of each reference to YourUnionQuery. This query would be
updatable unlike that including the union query.

Ken Sheridan
Stafford, England