Comparing a Large Amount of Information

[jasonkbu]

Ok, so here's the deal.

I have over 5,000 customers in a simple database. For each customer, I'm only displaying the account number, name & class of trade. Each customer represents a record and there is also a sub-form below displaying each customers product template - so each customer has different items displayed and this changes as I switch from record to record or customer to customer. (sorry if this is too much info)

I want to take a class of trade (Bakery for example) and I want to say, a Bakery should purchase these items (I'll have a list of items for the class of trade Bakery.) I want to then take that list and compare it to what is already in all Bakery customer product templates. Lastly, I want to display all of the missing items. The items I think they should buy - but only the items that are not already in the customer's product template.

Obviously, using the final report generated, our Sales Staff would be more effective while hitting the beat (so to say.)

I have a little experience with Access and VBA - but I can't get my head around this one. Any help would be much appreciated...

 

[andy callaghan]

I am not that good either, but as we are all part of the "community" I'll try to help.

If you open a new query in design mode, add the two two you have new bakers list and existing bakers list and link the two fields that have the items in( they may be called different in tables but as long in same format( eg text) should be okay. Now right click on the line that joins the tables click properties. You should now have three optins show only records that totally match, All off table 1 and matches off table 2, or lastly all off table 2 and matches off table 1. All three options obviosulyt will produce different results. Might not be the "PRO" but this is how I have a report set up to highlight new customers each month to add to a customer list going forward

 

[Egg 'n' Bacon]

Jason, it sounds like you have a seperate table with "list of items" (if not you'll need it ).

To do the comapison, you'll need to have a query with both tables selected & related on the 'class of trade' fields. Then you populate the query field with the relevant fields from the list of items table, but the last field criteria is set to 'Is Null'.

This should list those items that are in the bakery items table that aren't in the customers list of items.

Hope that makes sense

 

[jasonkbu]

Comparing Data in Acceses

I know it's like a year later, but I still have not been able to solve this problem. I have to tables with similar information in each.

Table1 (Customer's Existing Product Template)
Account Number, Item Number, Item Description, Class Of Trade

Table2 (List of Suggested Items by Class of Trade)
Item Number, Item Description, Class of Trade

Based on the Class of Trade value, if there are items in Table2 that do not exist in Table1, then display the items.

This seems like a simple thing to do, but I can't seem to make it happen.

I tried the last suggestion, but I couldn't make it work. I'm not sure what you mean by "setting the last field criteria to Is Null"?

Please help....

 

[gemma-the-husky]

a couple of queries -

can a customer only be in one class of trade. in this case the class of trade should be in the customer table.

scondly i presume a product can be in multiple trade class groups

------
so your partial structure is

customer (custid, classoftrade etc)
customertemplate (custid, itemno) - dont put the classoftrade OR the item desc in here
suggesteditems(classno, itemno)
product(itemno, description)
productclasses(classno, desc)

---------
on that basis, heres an example with a few products and customer classes

create a query of all actual items per customer
create a query of all potential items per customer
identify those missing from these 2 queries

note the query finding the missing items uses the null concept referred to in an earlier post
you need to set the arrows by right clicking the join line and selecting properties

these are the inner/outer/left/right joins referred to in various posts - its easier to see what is happening visually

look at the sql to see how the expressions are framed

 

[jasonkbu]

Using your example, I was able to make it work. You have no-idea how thankful I am; to all that have helped. I never thought I would be able to pull this off, even with online help.

Thx again!