Help Needed with Lookup Query

michaels

New member
Local time
Today, 09:44
Joined
Mar 29, 2021
Messages
3
We have a table of customers and per each customer there are 68 columns that represent each manufacturer a bit like the picture below.
66N,ABF,ADI etc each represent the manufacturer and the no of orders by customer for that particular manufacturer.


1617039358587.png


We have another table called Manufacturers Like so

1617039544368.png


We have a column in table 1 called Maxid. We want this value to be updated with the maximum id from table 2 based on what the customer had ordered.
For example Customerid has 66N and AMJ. If 66N = 0 and AMJ = 1 then the Maxid value will be 1.

I hope someone can help please with this :)
 
Hi. Welcome to AWF!

What I would recommend is to normalize Table1 first, so that you only have two columns: CustomerID and Ref

After that, you would be able to create a crosstab query to simulate your current table structure, in case you really need it displayed that way, and you would also be able to create a query to get the MaxID very easily.
 
OK thanks I will do that. I will normalise the data as suggested then get the maxid. Thanks
 

Users who are viewing this thread

Back
Top Bottom