Comparing Data In Access

[Guest]

I have 5,000 customers separated by class of trade in a simple database.
Lets take Bakery as a class of trade as an example.

I want to come up with a list of items a Bakery should buy from us.
I then want to compare that list of products against all of the Bakery
accounts we have (against their current product templates). If one or more
items are in the list I created but not in these customers product templates
then I want to display the customers name and the "missing" items.

Is this even possible? I can't seem to get my head around it.... Thx in
advance.

 

[Pieter Wijnen]

Sure, Not knowing your table-structure, but something in the neighborhood
of:

SELECT CustomerID FROM Customer C
WHERE C.Class='Bakery'
AND NOT EXISTS (SELECT 'X' FROM CustTemplate CT INNER JOIN ProdClass
PC On CT.ProdID=PC.ProdID
WHERE CT.CustomerID=C.CustomerID
AND PC.Class=C.Class)

HTH

Pieter

 

[Guest]

I haven't been able to make this work...
Let me explain in more detail.

I have a db (2003), where I've imported all of our customers as well as
their existing product profiles. Each customer is assigned a class of trade.

I want to manually create a new list of products for each class of trade
(using items I think each class is most likely to purchase from us.) I then
want to somehow make a comparison in a form and a report displaying only the
items the customer is not already buying from us & a percentage by account of
the number of items they are buying verses what I think they should buy.

First of all, I'm not exactly sure what the best way is to create the new
table or tables for the new lists of items I've created. I have 50 classes of
trade and 175 potential items per class. One item could be in all 50 classes
- just item number & item description. I thought of also putting the class
abbreviation next to the item and then list the item as many times as it's in
classes - but I don't know. I thought if the account class matches the class
abbreviation next to the item display the item - which is easy enough - but
then I don't know how to NOT display the items already in the customer's
profile...

I'm really stumped on this one...
I'd be happy to supply more information if necessary...

If you or anyone else could point me in the right direction I would be
extremely grateful.


Jason

 

[Guest]

I'm going to give this one more shot; I really could use some help.

Database Structure

Table: Customer Information (all customer data, acct#, name, address, class
of trade, etc)
Table: Product Information (account number, item number, item desc., item
class, (all allied items in customers existing product profile))
Table: tblItemClassOffering (item class code, item class description, class
of trade code, class of trade description (Item Classes I think each Class of
Trade should be buying))

If a Bakery Account is buying Cups/Lids and I think they should be buying
Cups/Lids and Hot Chocolate, then display Hot Chocolate.

If an item class appears in the customers existing product profile do not
let it appear in the suggested item class profile. So if the customer is
classified as a bakery (there are many different acct classes), pull the list
of suggested item classes and do not display the item classes that already
exist in the customers product profile.

My main menu (form) has a customers name, address, etc. Then, there is a Tab
Control; one has a sub-form for that customers existing product profile, and
another sub-form for the suggested product profile (item classes instead of
items.)

Main Form: CustomerInformation
Existing Product Profile Sub-Form: CustomerItems_SR
Suggested Item Class Profile Sub-Form: frmItemClassOffering

I really could use someone's help on this one...
Anyway, thx if you took the time to read this.

 

[Pieter Wijnen]

Something in the neigborhood of

SELECT ICO.* FROM tblItemClassOffering ICO
WHERE NOT EXISTS (SELECT 'X' FROM ProductInformation PI
WHERE PI.ClassOfTrade = ICO.ClassOfTrade)
AND ICO.ACCT#=Forms!MyForm!Acct#

HTH

Pieter

 

[goode2fox]

i know how you feel