Please Help... Making Comparison Between 2 Years

[No Spam]

Dear Access 2000 users,

We have two tables, named 2003 and 2004. Each table contains 3
fields. User Name, Period (numbered from 1 to 26), and Amount. We'd
like to compare amounts from period to period for the two years for
each user.

Here's my problem - some of the users don't have all 26 periods (e.g.
they started in period 3 of 2003). Is there any code to go through
and see if period 1 exists for each user, and if it doesn't, add a new
record with the user name and a $0.00 amount in the Amount field? And
then check period 2, and 3, etc.?

If it helps, I have a seperate table of all the user names.

Thanks a million in advance!

Kev

[Salad]

Re: Please Help... Making Comparison Between 2 Years

No Spam wrote:[color=blue]
> Dear Access 2000 users,
>
> We have two tables, named 2003 and 2004. Each table contains 3
> fields. User Name, Period (numbered from 1 to 26), and Amount. We'd
> like to compare amounts from period to period for the two years for
> each user.
>
> Here's my problem - some of the users don't have all 26 periods (e.g.
> they started in period 3 of 2003). Is there any code to go through
> and see if period 1 exists for each user, and if it doesn't, add a new
> record with the user name and a $0.00 amount in the Amount field? And
> then check period 2, and 3, etc.?
>
> If it helps, I have a seperate table of all the user names.
>
> Thanks a million in advance!
>
> Kev[/color]

Create a temp, junk table. Call it tmpPeriod...1 field called
Period...enter values 1-26.

Create another query will select all employees. QEmp

Create another query. Add table Period and Qemp. Drag the Period and
EmpID or Name to the columns. Make this a MakeTable (from Menu
Query/MakeTable). New Table name is tmpPeriodEmp. This is a cartesian
join query that will create 26 records for each employee and store the
results in table tmpPeriodEmp.

Now you create another query. Add the tables 2003 and tmpPeriodEmp.
You sould make this query via the FindUnmatchedWi zard unless you know
how to do that. Now drag down the field names Period, EmpID/Name from
tmpPEriodID and make this an AppendQUery (from Menu, Menu/Append. You
can then fill in the dollar amounts later with an update query.

Delete any tmp tables and Qemp when done. Make a copy of 2003 before
you run.

[Pieter Linden]

Re: Please Help... Making Comparison Between 2 Years

No Spam <nospam@earthli nk.net> wrote in message news:<deed90llh pftvvoq5ki4fjri 16quc37oeu@4ax. com>...[color=blue]
> Dear Access 2000 users,
>
> We have two tables, named 2003 and 2004. Each table contains 3
> fields. User Name, Period (numbered from 1 to 26), and Amount. We'd
> like to compare amounts from period to period for the two years for
> each user.
>
> Here's my problem - some of the users don't have all 26 periods (e.g.
> they started in period 3 of 2003). Is there any code to go through
> and see if period 1 exists for each user, and if it doesn't, add a new
> record with the user name and a $0.00 amount in the Amount field? And
> then check period 2, and 3, etc.?
>
> If it helps, I have a seperate table of all the user names.
>
> Thanks a million in advance!
>
> Kev[/color]

1. use the wizard to create a union query of 2003 and 2004.
2. create a table of Periods. Insert values 1-26.
3. create a cartesian product of period X username. (all combinations
of (username,perio d), so you'll have some multiple of 26.)
4. run the find unmatched query wizard and use the result of step 3
with the result of step 1.
5. turn that into an append query...
6. run said query...

(well, that's the short answer.)


SELECT User Name, Period, Amount, Year
FROM tbl2003
UNION ALL
SELECT User Name, Period, Amount
FROM tbl2003