Passing data from a table into a query...help! (1 Viewer)

Pete433

New member
Local time
Today, 12:34
Joined
Jul 28, 2020
Messages
7
hi - i'm trying to score customers depending on the volume of material they have purchased.

so, customer A has purchased 50,000 units from us, and customer b has purchased 10,000 units. i have a table which says if you buy between 30,000 and 60,000 then you are customer score 4 and if you have purchased between 0 and 29,999 units you are score 3. i know that i can use "IIf ([units]>30,000 AND [units]<60,0000,4,0), however, the scoring range changes each month (our business has a template in excel in which our commercial team set the limit) therefore i somehow need to pass the range through to the IIF statement as a top and bottom limit. Most of this question is for illustritive purposes so i can explain the concept, we actually have 5 categories not just quantity and each has 5 scoring ranges so it is a bit of a task to do it manually, ideally i would load the template into the database and it would automatically sort the formula,

any help is greatly appreciated!

Pete
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:34
Joined
Sep 21, 2011
Messages
14,038
I think the simplest way would be a DlookUp() ?
 

mike60smart

Registered User.
Local time
Today, 12:34
Joined
Aug 6, 2017
Messages
1,899
Hi Can you upload a zipped copy of the database?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:34
Joined
May 7, 2009
Messages
19,169
If the excel file can be Created as Link table do so. Otherwise you need a temp table to import excel data.

Involve the data (table) from excel in your query using Join.
Eg, supposed that the linked table has these columns:

MinQty (long)
MaxQty(long)
Score(integer)

On your query;

Select a.customer, a.units, b.score from purchaseTable a left join theLinkTable b on a.units >= b.MinQty and a.units <= b.MaxQty;
 

Pete433

New member
Local time
Today, 12:34
Joined
Jul 28, 2020
Messages
7
Hi Can you upload a zipped copy of the database?
i've taken out some table, if i can get this to work i can use it in my main database, hopefully it makes sense, if needed i can change what are columns or rows etc in the scoring table

i've attached my scoring template if that helps... many thanks
 

Attachments

  • Att_Test.zip
    353.6 KB · Views: 279
  • Banding Scores.zip
    14.2 KB · Views: 283
Last edited:

Pete433

New member
Local time
Today, 12:34
Joined
Jul 28, 2020
Messages
7
If the excel file can be Created as Link table do so. Otherwise you need a temp table to import excel data.

Involve the data (table) from excel in your query using Join.
Eg, supposed that the linked table has these columns:

MinQty (long)
MaxQty(long)
Score(integer)

On your query;

Select a.customer, a.units, b.score from purchaseTable a left join theLinkTable b on a.units >= b.MinQty and a.units <= b.MaxQty;
thanks, ill give it a try
 

Users who are viewing this thread

Top Bottom