Showig differences in Month to Month data from data in the same ta

[John]

I am not a guru with access and this problem has me puzzled...

I have a table wiht multiple months of data. The key fields are a cost
code, report date and then several values (Budget, Actual, Forecast, etc.)
All data is in the same table. I would like to create one query that shows a
comparison between two months, say April and March. Some records in March
may be gone from April, in which case the April values should show 0. Here's
a sample what I have:

Date, Code, Budget, Actual
3/30/08, 8000, 34000, 400
3/30/08, 9001, 50000, 500
3/30/08, 18000, 60000, 1200
3/30/08, 21100, 70000, 4600
3/30/08, 34000, 80000, 7600
4/30/08, 8000, 34000, 600
4/30/08, 9001, 50000, 1500
4/30/08, 18000, 60000, 2200
4/30/08, 21100, 150000, 14200

What I would like to see is one query that shows:

Code, BudgetApr, BudgetMar, BudgetDelta, ActualApr, ActualMar, ActualDelta
8000, 34000, 34000, 0, 600, 400,
200
9001, 50000, 50000, 0, 1500, 500,
1000
18000, 60000, 60000, 0, 2200, 1200,
1000
21100, 150000, 70000, 80000, 14200, 7600,
7600
34000, 0, 80000, -80000, 0, 7600,
-7600

I can do this with several queries in a sequense of steps, (one to create a
prior period tabel, one to add the unmatch records as "dummy" activities from
prior period table to current period, and one that joins the tables). This
two complicated for my intended audience. I need a query that they can just
double-click, it asks them for "current" period, and "prior" period and shows
them the data. I have a UNION query that pulls the correct records, but I
can't seem to find a way to pull a query from the UNION query that sets up
"current" and "prior" period columns, given that they change from month to
month.

Any help would be great!!!

 

[strive4peace]

Hi John,

make a query and put the table, which I will call 'Data' since you did
not specify a name, up twice

when you use the same table more than once, Access will label the second
copy like this:

Data_1

link the two tables on Code

on the grid:

field --> Code
table --> Data

field --> YrMo1: format([Data].Datefieldname,"mmm yy")
criteria --> [Enter Month1 (mmm yy), ie: Jan 08]

field --> YrMo2: format([Data_1].Datefieldname,"mmm yy")
criteria --> [Enter Month2 (mmm yy), ie: Feb 08]

field --> Budget1: [Data].Budget

field --> Budget2: [Data_1].Budget

field --> BudgetDelta: nz([Data].Budget,0) - nz([Data_1].Budget,0)

etc

this will only show records where they exist in BOTH copies of the
table. so, if there is a code which has no record for one of the
months, it will not show in the output.
~~

do not use DATE as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[John]

Crystal,

Thanks for the suggestion. I had tried this avenue once before and could
not get it to work. As you point out if the recored is not in BOTH months,
neither one shows up.

I did find a solution that is working, although I think there should be a
better way...

For month1 I created a Select Query that asked for month like: [Enter
CURRENT Month:]
For month2 I created another Select Query that asked for month like: [Enter
PRIOR Month:]

In Both of these queries, I added a field -- "Mnth: 1" in the current month
query and "Mnth: 2" in the Prior month query.

I then copied the SQL from the first query in a UNION query and added "UNION
ALL" and then copied the SQL from the second query below it.

This query will ask the user for the current / prior months and return the
record set with ALL records and each record is taged with a "1" or "2".

I could at this point, deleted the first two queries as they were there to
generate the SQL statement.

Finally, I created a 4th query, using the UNION query as the data source,
which has the fields I need like Code, Code Description, Budget, Actual,
Percent, Forecast, etc. The key is now the following:

Group on Code, and change all the number fields to an "EXPRESSION" in the
"Total" row. In each field I used:

CurrentBudget: SUM(IIF(Mnth=1, Budget, 0))
PriorBudget: SUM(IIF(Mnth=2, Budget,0))
BudgetDelta: CurrentBudget - PriorBudget

I tested this out and it works fine. The user only has to run the 4th
query, and supply the two dates.

Like I said, this is still kind of "clunky", but it works. The database wil
have about 260,000 records in it by the end of the job, so I am worried about
database performance. We are currently up to about 5,000 and it is running
fine...

--
Thanks - John

Hi John,

make a query and put the table, which I will call 'Data' since you did
not specify a name, up twice

when you use the same table more than once, Access will label the second
copy like this:

Data_1

link the two tables on Code

on the grid:

field --> Code
table --> Data

field --> YrMo1: format([Data].Datefieldname,"mmm yy")
criteria --> [Enter Month1 (mmm yy), ie: Jan 08]

field --> YrMo2: format([Data_1].Datefieldname,"mmm yy")
criteria --> [Enter Month2 (mmm yy), ie: Feb 08]

field --> Budget1: [Data].Budget

field --> Budget2: [Data_1].Budget

field --> BudgetDelta: nz([Data].Budget,0) - nz([Data_1].Budget,0)

etc

this will only show records where they exist in BOTH copies of the
table. so, if there is a code which has no record for one of the
months, it will not show in the output.
~~

do not use DATE as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I am not a guru with access and this problem has me puzzled...

I have a table wiht multiple months of data. The key fields are a cost
code, report date and then several values (Budget, Actual, Forecast, etc.)
All data is in the same table. I would like to create one query that shows a
comparison between two months, say April and March. Some records in March
may be gone from April, in which case the April values should show 0. Here's
a sample what I have:

Date, Code, Budget, Actual
3/30/08, 8000, 34000, 400
3/30/08, 9001, 50000, 500
3/30/08, 18000, 60000, 1200
3/30/08, 21100, 70000, 4600
3/30/08, 34000, 80000, 7600
4/30/08, 8000, 34000, 600
4/30/08, 9001, 50000, 1500
4/30/08, 18000, 60000, 2200
4/30/08, 21100, 150000, 14200

What I would like to see is one query that shows:

Code, BudgetApr, BudgetMar, BudgetDelta, ActualApr, ActualMar, ActualDelta
8000, 34000, 34000, 0, 600, 400,
200
9001, 50000, 50000, 0, 1500, 500,
1000
18000, 60000, 60000, 0, 2200, 1200,
1000
21100, 150000, 70000, 80000, 14200, 7600,
7600
34000, 0, 80000, -80000, 0, 7600,
-7600

I can do this with several queries in a sequense of steps, (one to create a
prior period tabel, one to add the unmatch records as "dummy" activities from
prior period table to current period, and one that joins the tables). This
two complicated for my intended audience. I need a query that they can just
double-click, it asks them for "current" period, and "prior" period and shows
them the data. I have a UNION query that pulls the correct records, but I
can't seem to find a way to pull a query from the UNION query that sets up
"current" and "prior" period columns, given that they change from month to
month.

Any help would be great!!!

 

[strive4peace]

hi John,

the other option is this:

assuming you have a table where you define Codes...

add the Codes table to your query

do a left-join from Codes to each copy of the Data table (link on code,
choose to show ALL records from Codes)

get Code from the Code definition table

then try this:

field --> YrMo1: format([Data].Datefieldname,"mmm yy")
criteria --> [Enter Month1 (mmm yy), ie: Jan 08] or Is Null

field --> YrMo2: format([Data_1].Datefieldname,"mmm yy")
criteria --> [Enter Month2 (mmm yy), ie: Feb 08] or Is Null

"kind of "clunky", but it works"

well, if you have something that works, don't change it unless your
performance goes south <smile>. If you have intermediate queries, you
may want to consider prefacing the query name with "usys" so they won't
show up in the database window unless system objects are showing

(Access 2003 and below) to show MSys and USys objects:
(from the menu) --> Tools, Options...
check --> System Objects

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thanks for the suggestion. I had tried this avenue once before and could
not get it to work. As you point out if the recored is not in BOTH months,
neither one shows up.

I did find a solution that is working, although I think there should be a
better way...

For month1 I created a Select Query that asked for month like: [Enter
CURRENT Month:]
For month2 I created another Select Query that asked for month like: [Enter
PRIOR Month:]

In Both of these queries, I added a field -- "Mnth: 1" in the current month
query and "Mnth: 2" in the Prior month query.

I then copied the SQL from the first query in a UNION query and added "UNION
ALL" and then copied the SQL from the second query below it.

This query will ask the user for the current / prior months and return the
record set with ALL records and each record is taged with a "1" or "2".

I could at this point, deleted the first two queries as they were there to
generate the SQL statement.

Finally, I created a 4th query, using the UNION query as the data source,
which has the fields I need like Code, Code Description, Budget, Actual,
Percent, Forecast, etc. The key is now the following:

Group on Code, and change all the number fields to an "EXPRESSION" in the
"Total" row. In each field I used:

CurrentBudget: SUM(IIF(Mnth=1, Budget, 0))
PriorBudget: SUM(IIF(Mnth=2, Budget,0))
BudgetDelta: CurrentBudget - PriorBudget

I tested this out and it works fine. The user only has to run the 4th
query, and supply the two dates.

Like I said, this is still kind of "clunky", but it works. The database wil
have about 260,000 records in it by the end of the job, so I am worried about
database performance. We are currently up to about 5,000 and it is running
fine...

 

[John]

This woks much better! Thanks...

On more thing you said that peaked my interest... "If you have intermediate
queries, you may want to consider prefacing the query name with "usys" so
they won't show up in the database window unless system objects are showing."
I understand this to mean if I name my query "usys_xyz" the query will not
show up unless system objects are showng? If so, how do I turn system objects
on/off? I can't seem to find any documentation on this...

--
Thanks - John

hi John,

the other option is this:

assuming you have a table where you define Codes...

add the Codes table to your query

do a left-join from Codes to each copy of the Data table (link on code,
choose to show ALL records from Codes)

get Code from the Code definition table

then try this:

field --> YrMo1: format([Data].Datefieldname,"mmm yy")
criteria --> [Enter Month1 (mmm yy), ie: Jan 08] or Is Null

field --> YrMo2: format([Data_1].Datefieldname,"mmm yy")
criteria --> [Enter Month2 (mmm yy), ie: Feb 08] or Is Null

"kind of "clunky", but it works"

well, if you have something that works, don't change it unless your
performance goes south <smile>. If you have intermediate queries, you
may want to consider prefacing the query name with "usys" so they won't
show up in the database window unless system objects are showing

(Access 2003 and below) to show MSys and USys objects:
(from the menu) --> Tools, Options...
check --> System Objects

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thanks for the suggestion. I had tried this avenue once before and could
not get it to work. As you point out if the recored is not in BOTH months,
neither one shows up.

I did find a solution that is working, although I think there should be a
better way...

For month1 I created a Select Query that asked for month like: [Enter
CURRENT Month:]
For month2 I created another Select Query that asked for month like: [Enter
PRIOR Month:]

In Both of these queries, I added a field -- "Mnth: 1" in the current month
query and "Mnth: 2" in the Prior month query.

I then copied the SQL from the first query in a UNION query and added "UNION
ALL" and then copied the SQL from the second query below it.

This query will ask the user for the current / prior months and return the
record set with ALL records and each record is taged with a "1" or "2".

I could at this point, deleted the first two queries as they were there to
generate the SQL statement.

Finally, I created a 4th query, using the UNION query as the data source,
which has the fields I need like Code, Code Description, Budget, Actual,
Percent, Forecast, etc. The key is now the following:

Group on Code, and change all the number fields to an "EXPRESSION" in the
"Total" row. In each field I used:

CurrentBudget: SUM(IIF(Mnth=1, Budget, 0))
PriorBudget: SUM(IIF(Mnth=2, Budget,0))
BudgetDelta: CurrentBudget - PriorBudget

I tested this out and it works fine. The user only has to run the 4th
query, and supply the two dates.

Like I said, this is still kind of "clunky", but it works. The database wil
have about 260,000 records in it by the end of the job, so I am worried about
database performance. We are currently up to about 5,000 and it is running
fine...

 

[strive4peace]

Hi John,

any object that is prefaced with "Msys" is a Microsoft System object
any object that is prefaced with "Usys" is a User System object

You can choose to display or hide system objects.

~~~~~~~~~~~~~~~~~~~~~~~~~
if you are using Access 2003 or below:

from the menu --> Tools, Options...
and check or uncheck --> System Objects

~~~~~~~~~~~~~~~~~~~~~~~~~
if you are using Access 2007:

1. right-click the Navigation Pane titlebar
2. from shortcut menu --> Navigation options *

3. in the dialog box, look under 'Display Options' in the upper left
4. select or unselect Show System Objects
5. OK to save changes

* you can also get to this feature another way:
1. click the Microsoft Office button
(big round thing in upper left corner of the Access window)
2. in the bottom right corner, click the 'Access Options' command button
3. choose the 'Current Database' category
4. under 'Navigation', click 'Navigation Options'


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[John]

Crystal - I found the system object setting (Tools, Options). This is so
cool! I was going through all kinds of hoops to try to keep my user groups
from moifying key queries, tables, reorts, etc. I want them to have the
flexibilty of adding and creating their own queries and reports, but not edit
the basic ones we create from the mother ship ...


--
Thanks - John

hi John,

the other option is this:

assuming you have a table where you define Codes...

add the Codes table to your query

do a left-join from Codes to each copy of the Data table (link on code,
choose to show ALL records from Codes)

get Code from the Code definition table

then try this:

field --> YrMo1: format([Data].Datefieldname,"mmm yy")
criteria --> [Enter Month1 (mmm yy), ie: Jan 08] or Is Null

field --> YrMo2: format([Data_1].Datefieldname,"mmm yy")
criteria --> [Enter Month2 (mmm yy), ie: Feb 08] or Is Null

"kind of "clunky", but it works"

well, if you have something that works, don't change it unless your
performance goes south <smile>. If you have intermediate queries, you
may want to consider prefacing the query name with "usys" so they won't
show up in the database window unless system objects are showing

(Access 2003 and below) to show MSys and USys objects:
(from the menu) --> Tools, Options...
check --> System Objects

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thanks for the suggestion. I had tried this avenue once before and could
not get it to work. As you point out if the recored is not in BOTH months,
neither one shows up.

I did find a solution that is working, although I think there should be a
better way...

For month1 I created a Select Query that asked for month like: [Enter
CURRENT Month:]
For month2 I created another Select Query that asked for month like: [Enter
PRIOR Month:]

In Both of these queries, I added a field -- "Mnth: 1" in the current month
query and "Mnth: 2" in the Prior month query.

I then copied the SQL from the first query in a UNION query and added "UNION
ALL" and then copied the SQL from the second query below it.

This query will ask the user for the current / prior months and return the
record set with ALL records and each record is taged with a "1" or "2".

I could at this point, deleted the first two queries as they were there to
generate the SQL statement.

Finally, I created a 4th query, using the UNION query as the data source,
which has the fields I need like Code, Code Description, Budget, Actual,
Percent, Forecast, etc. The key is now the following:

Group on Code, and change all the number fields to an "EXPRESSION" in the
"Total" row. In each field I used:

CurrentBudget: SUM(IIF(Mnth=1, Budget, 0))
PriorBudget: SUM(IIF(Mnth=2, Budget,0))
BudgetDelta: CurrentBudget - PriorBudget

I tested this out and it works fine. The user only has to run the 4th
query, and supply the two dates.

Like I said, this is still kind of "clunky", but it works. The database wil
have about 260,000 records in it by the end of the job, so I am worried about
database performance. We are currently up to about 5,000 and it is running
fine...

 

[strive4peace]

you're welcome, John

for existing objects that you do not want to rename, you can do this:

right-click on an object in the database window and check the "Hidden"
checkbox

show or hide the same way as system objects except the checkbox in the
defaults is called 'Hidden objects'

"mother ship" ... like your term <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

ps

one reason I like using "usys", especially for queries, is because I can
see by the name what is necessary for the system. I also use the
Description property to indicate what uses it...

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*