lookup

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I'm suffering from brain fade and I could use some help! I recall creating
this query a couple of years ago but now I can't remember how I did it...

Each quote that I send out has a dollar value assigned to it. I have a table
of dollar ranges and range codes. I want to use this table of ranges to
assign letter codes to each quote (i.e. quotes between $25,000 and $100,000
would be assigned the letter "C").

Later on I will group letter codes into sums and percentages to see how
we're doing on our quotes.

What's the most efficient way to lookup and assign the correct letter code
to each quote?

Scott
 
Hi,


A table with three fields

QuotesCodes ' table name
FromThis ToThis Code ' fields name
0 999.99 A
1000 24999.99 B
25000 99999.99 C
..... data sample




Then


SELECT *, QuotesCodes.Code
FROM myOriginalTable LEFT JOIN QuotesCodes
ON myOriginalTable.Amount BETWEEN QuotesCodes.FromThis AND
QuotesCodes.ToThis




The advantage is that all data is in a table, so you can change the "scales"
without having to change your code. There are solutions where the data will
be embedded in the code, but that makes modifications ... modifying the
code... and in many place of the code (in many queries, probably). So, it is
always preferable to put data where it should be, in a table.




Hoping it may help,
Vanderghast, Access MVP
 
Michel,

I do all of my queries in Design View so I'm not at all familiar with SQL
code but that didn't stop me from trying to add your code to mine!

Here's what I have...

SELECT Quotes.[E- Number], Quotes.[Date Closed], Quotes.[Cust Grade],
Quotes.Material, QUOTE_2.DOLVOL_TOT AS Dollars, [Quote Status].Feedback,
IIf([feedback]="L",1,0) AS L, IIf([feedback]="M",1,0) AS M,
IIf([feedback]="R",1,0) AS R, IIf([feedback]="Z",1,0) AS Z
FROM Quote_2 LEFT JOIN DollarRange ON quote_2.dolvol_tot between
DollarRange.FromThis And DollarRange.ToThis, (Quotes INNER JOIN [Quote
Status] ON Quotes.[Quote Status] = [Quote Status].[Quote Status]) INNER JOIN
QUOTE_2 ON Quotes.[E- Number] = QUOTE_2.QUOTE_NO
WHERE (((Quotes.[Date Closed])>#12/31/2003#) AND ((Quotes.[RFQ
Status])="X"));

The error I'm getting is - Between operator without And in query expression
'quote_2.dolvol_tot between DollarRange.FromThis'

I guess I've written the code incorectly! Can you offer any suggestions?

Scott
 
Hi,


You can either try to add parentheses :

ON ( quote_2.dolvol_tot between
DollarRange.FromThis And DollarRange.ToThis )

either to move the condition in the WHERE clause, you can do it in the
graphical part of the editor :


start where you were before adding the suggested modification,
add the new table,
drag the field quote_2.dolvol_tot in the grid and under it, add the
criteria:
BETWEEN [DollarRange].FromThis AND [DollarRange].ToThis


(I added [ ] since, sometimes, Access thinks we meant string constant
rather than tables and fields, but [ ] removes that ambiguity, ....
generally).


That solution is a little bit slower than using a join, but you keep the
graphical capabilities of the designer. :-)



Hoping it may help,
Vanderghast, Access MVP


Scott said:
Michel,

I do all of my queries in Design View so I'm not at all familiar with SQL
code but that didn't stop me from trying to add your code to mine!

Here's what I have...

SELECT Quotes.[E- Number], Quotes.[Date Closed], Quotes.[Cust Grade],
Quotes.Material, QUOTE_2.DOLVOL_TOT AS Dollars, [Quote Status].Feedback,
IIf([feedback]="L",1,0) AS L, IIf([feedback]="M",1,0) AS M,
IIf([feedback]="R",1,0) AS R, IIf([feedback]="Z",1,0) AS Z
FROM Quote_2 LEFT JOIN DollarRange ON quote_2.dolvol_tot between
DollarRange.FromThis And DollarRange.ToThis, (Quotes INNER JOIN [Quote
Status] ON Quotes.[Quote Status] = [Quote Status].[Quote Status]) INNER JOIN
QUOTE_2 ON Quotes.[E- Number] = QUOTE_2.QUOTE_NO
WHERE (((Quotes.[Date Closed])>#12/31/2003#) AND ((Quotes.[RFQ
Status])="X"));

The error I'm getting is - Between operator without And in query expression
'quote_2.dolvol_tot between DollarRange.FromThis'

I guess I've written the code incorectly! Can you offer any suggestions?

Scott



Michel Walsh said:
Hi,


A table with three fields

QuotesCodes ' table name
FromThis ToThis Code ' fields name
0 999.99 A
1000 24999.99 B
25000 99999.99 C
.... data sample




Then


SELECT *, QuotesCodes.Code
FROM myOriginalTable LEFT JOIN QuotesCodes
ON myOriginalTable.Amount BETWEEN QuotesCodes.FromThis AND
QuotesCodes.ToThis




The advantage is that all data is in a table, so you can change the "scales"
without having to change your code. There are solutions where the data will
be embedded in the code, but that makes modifications ... modifying the
code... and in many place of the code (in many queries, probably). So,
Click to expand...
it
is
always preferable to put data where it should be, in a table.




Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Michael,

I used your suggestion that was desinged for the "graphical capabilities of
the designer".

1) I added the DollarRange table to my query without any links to any other
tables.
2) I added the field quote_2.dolvol_tot to the grid
3) I added the criteria Between [DollarRange]![FromThis] And
[DollarRange]![ToThis] to the field quote_2.dolvol_tot
4) I added another column and added the field DollarRange.Code to the grid

I stared at the grid and couldn't figrure out how the query should work. I
clicked on the Run button anyway and it does work! How about that!

The right side of my brain definately needs some exercise because I still
can't figure out why the solution works but, I'm glad it does. Thanks for
your help, Michael!

Scott


Michel Walsh said:
Hi,


You can either try to add parentheses :

ON ( quote_2.dolvol_tot between
DollarRange.FromThis And DollarRange.ToThis )

either to move the condition in the WHERE clause, you can do it in the
graphical part of the editor :


start where you were before adding the suggested modification,
add the new table,
drag the field quote_2.dolvol_tot in the grid and under it, add the
criteria:
BETWEEN [DollarRange].FromThis AND [DollarRange].ToThis


(I added [ ] since, sometimes, Access thinks we meant string constant
rather than tables and fields, but [ ] removes that ambiguity, ....
generally).


That solution is a little bit slower than using a join, but you keep the
graphical capabilities of the designer. :-)



Hoping it may help,
Vanderghast, Access MVP


Scott said:
Michel,

I do all of my queries in Design View so I'm not at all familiar with SQL
code but that didn't stop me from trying to add your code to mine!

Here's what I have...

SELECT Quotes.[E- Number], Quotes.[Date Closed], Quotes.[Cust Grade],
Quotes.Material, QUOTE_2.DOLVOL_TOT AS Dollars, [Quote Status].Feedback,
IIf([feedback]="L",1,0) AS L, IIf([feedback]="M",1,0) AS M,
IIf([feedback]="R",1,0) AS R, IIf([feedback]="Z",1,0) AS Z
FROM Quote_2 LEFT JOIN DollarRange ON quote_2.dolvol_tot between
DollarRange.FromThis And DollarRange.ToThis, (Quotes INNER JOIN [Quote
Status] ON Quotes.[Quote Status] = [Quote Status].[Quote Status]) INNER JOIN
QUOTE_2 ON Quotes.[E- Number] = QUOTE_2.QUOTE_NO
WHERE (((Quotes.[Date Closed])>#12/31/2003#) AND ((Quotes.[RFQ
Status])="X"));

The error I'm getting is - Between operator without And in query expression
'quote_2.dolvol_tot between DollarRange.FromThis'

I guess I've written the code incorectly! Can you offer any suggestions?

Scott



Michel Walsh said:
Hi,


A table with three fields

QuotesCodes ' table name
FromThis ToThis Code ' fields name
0 999.99 A
1000 24999.99 B
25000 99999.99 C
.... data sample




Then


SELECT *, QuotesCodes.Code
FROM myOriginalTable LEFT JOIN QuotesCodes
ON myOriginalTable.Amount BETWEEN QuotesCodes.FromThis AND
QuotesCodes.ToThis




The advantage is that all data is in a table, so you can change the "scales"
without having to change your code. There are solutions where the data will
be embedded in the code, but that makes modifications ... modifying the
code... and in many place of the code (in many queries, probably). So,
Click to expand...
it
is
always preferable to put data where it should be, in a table.




Hoping it may help,
Vanderghast, Access MVP



I'm suffering from brain fade and I could use some help! I recall creating
this query a couple of years ago but now I can't remember how I did it...

Each quote that I send out has a dollar value assigned to it. I have a
table
of dollar ranges and range codes. I want to use this table of ranges to
assign letter codes to each quote (i.e. quotes between $25,000 and
$100,000
would be assigned the letter "C").

Later on I will group letter codes into sums and percentages to see how
we're doing on our quotes.

What's the most efficient way to lookup and assign the correct
Click to expand...
letter
code
to each quote?

Scott
Click to expand...
Click to expand...
Click to expand...
 
Back
Top