Help Needed with Lookup Query

[michaels]

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.

We have another table called Manufacturers Like so

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

 

[theDBguy]

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.

 

[michaels]

OK thanks I will do that. I will normalise the data as suggested then get the maxid. Thanks

 

[theDBguy]

OK thanks I will do that. I will normalise the data as suggested then get the maxid. Thanks

Good luck! Let us know how it goes...

 

[michaels]

Good luck! Let us know how it goes...

Everything done as you suggested. Thanks once again.

 

[theDBguy]

Everything done as you suggested. Thanks once again.

Congratulations! Continued success with your project.