Lookup? Help please.

[Alan Mitchell]

I am using Access 2000.

I have 2 tables in my database.

Table "Sales" contains daily sales information for a number of sales people.
It includes these fields:
REGID - unique identifier for a single sale
KAMID - identifier for sales person
CreationDate - date of sale.

Table "Targets" contains monthly targtes for each sales person. It contains
USERID - unique identifier for sales person
JAN - individual sales target for January
FEB - individual sales target for Feb
etc
etc

I can run a query to produce total sales results by salesperson by month.

My problem is I also want to add the correct sales target fopr the
appropraite month for the appropriate salesperson in order to be able to
calculate variances.

As a relative newbie this is causing me considerable difficulty.

I cannot figure out if
a) I need to learn how to use DLOOKUP or
b) My table structure is wrong or
c) I am on the totally wrong track.

Any help would be hugely appreciated.

TIA

 

[Ken Snell [MVP]]

Your Targets table is not normalized, which means that it's hard to find the
right month without programming or the use of a UNION query to normalize it
for your search.

The table should look like this:
UserID
MonthID (best if 1 through 12 to correspond to the months)
TargetValue

Then you could use DLookup to find the right record because you can have two
criteria in the DLookup function: one for the UserID and the other for the
MonthID.

Otherwise, you'll need to create a UNION query that normalizes your Targets
table:
SELECT UserID, Jan, 1 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Feb, 2 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Mar, 3 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Apr, 4 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, May, 5 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Jun, 6 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Jul, 7 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Aug, 28 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Sep, 9 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Oct, 10 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Nov, 11 AS MonthNum FROM Targets
UNION ALL
SELECT UserID, Dec, 12 AS MonthNum FROM Targets;

Then, after saving this query (perhaps it's named qryUnionTargets), then use
it in the DLookup function.

 

[Alan Mitchell]

Ken

Thnaks for your very prompt and helpful (as usual) repsonse.

Its the end of a long working day here in NZ, but guess what I'll be doing
first thing tomorrow.

One last, small, request. Can anyone point me at any decent referencing
material (perferably web) on normalisation. I really need to get my head
round this.

 

[Ken Snell [MVP]]

There are many good books on normalization.

As for web resources, none come to the top of my mind right now. But you
could start at www.mvps.org/access and follow some links from there. Also, I
have some links to other MVPs sites at www.cadellsoftware.org that may help.

Please note that I have a typo in the UNION query that I wrote .. the 28 for
the Aug line should be 8.
--

Ken Snell
<MS ACCESS MVP>