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]

No Spam wrote:

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

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 FindUnmatchedWizard 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]

No Spam <no****@earthlink.net> wrote in message news:<de********************************@4ax.com>. ..

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

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,period), 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

[Bob Quintal]

No Spam <no****@earthlink.net> wrote in
news:de********************************@4ax.com:

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

Why go to all that trouble?

Create a query, with tables 2004 and 2003
join the tables on user name -> user name, and period -> period.
Double-click on each join line. change the join type to show ALL
Recods from table 2004 and only tohose from 2003....

Now fill in the grid with the three fields from 2004.

In the fourth field, enter the following statement:
2003Amt: nz([2003].[amount],0). Open the properties popup and set
format to currency, decimal places to 2.

save and run the query.

Bob Q.

[No Spam]

Thanks for all the help, I got it! I really appreciate it!

Kev
On Mon, 03 May 2004 21:34:56 GMT, No Spam <no****@earthlink.net>
wrote:

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