Excel type "lookup"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am probably looking at this the wrong way so I thought I would post here to
see if someone can set me straight!

I have a database that records people booking space at a show, depending on
how much space they book decides what free passes they will get, so eg if
they have 3m booked the will get 2 badges, 1 pass and 1 ticket, for 6m they
get 3 badges, 2 passes and 1 ticket etc.

They do not HAVE to book 3m, 6m, 9m etc and if they have booked between the
"set" values then they will get the higher one - so someone booking 4m will
get the 6m allocation.

I will need to run the "allocation" from a form and in various reports - but
at the moment I need a point in the right direction since I can only get a
query to work if the values are equal....

Thanks for any help
 
Please supply some information about how relevant tables look. Then we might
be able to help.
 
Hi Tom,

I'm not really sure that this will help but here goes:

The Exhibitor table (keyed by ExhibitorID) contains all the usual info -
address details etc. The important field here is the Frontage. The value in
this will determine what they will get "allocated". Currently this is done
manually, so the "Allocation" table can look however you like - at the moment
it has no key but has fields as follows:

Allocations.Frontagemin, Allocations.CompTickets, Allocations.PreVP,
Allocations.ShowVP

The Frontagemin is the field that I want to "lookup" on and then to retrieve
the values in the other fields.

As I said I can do this in Excel (although it will give me the lower number
if not matched) using a VLookup function....

The values that I get back will probably go into calculated fields, not
stored, since they could change.

Does that help??
 
You need to use a SubQuery to determin which Allocations Record to use.

Try the SQL something like:

****
SELECT E.ExhibitorID, ..., A.CompTickets, A.PreVP, A.ShowVP
FROM Exhibitors AS E, Allocations AS A
WHERE A.FrontageMin =
(
SELECT Min(Sub.FrontageMin)
FROM Allocations AS Sub
WHERE Sub.FrontageMin >= E.Frontage
)
****

There may be more efficient SQLs out there ... but if the posted SQL works,
at least it a start.
 
Many thanks - that certainly seems to be doing the trick. Now I have the
"answer" I just have to work out how to implement it :-)

One thing - why do you do the "Exhibitors AS E, Allocations AS A"

Is it just to save on typing?
 
For posting, I use Aliases to shorten the SQL String, mainly for clarity.
An SQL String with lots of long Table references doesn't help showing the
logic of the SQL.

I also use Aliases for added distinction of the different copies of the same
Table in the SQL. The Alias "Sub" in the SubQuery / SQL is an example.

Note also I used new lines and indents to help with the logic in the SQL.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top