Calculate difference between to records

[Guest]

I searched already on this question but I don't think it's going to help in
my case.
Each record is identified by company/year/month and the concatenation of all
three fields is the unique identifier for each record. The table could look
like this:

field1 field2 field3 field4 field5
field6 field7
cmp1_2005_june cmp1 2005 june 5000 4000 1000
cmp1_2005_july cmp2 2005 july 10000 9000 1000
cmp2_2005_june cmp3 2005 june 6000 4000 2000

wheres:
field1 - field2&field3&field4
field2 - company
field3 - year
field4 - month
field5 - sales
field6 - cogs
field7 - field5-field6

This database is capturing YTD amounts for several companies monthly and I
need to take the difference from the current month for a certain company and
the prior month so the activity for the current month can be derived.
Any ideas on how to do this using combo boxes so the customer can choose the
company and period?

 

[John Vinson]

I searched already on this question but I don't think it's going to help in
my case.
Each record is identified by company/year/month and the concatenation of all
three fields is the unique identifier for each record. The table could look
like this:

field1 field2 field3 field4 field5
field6 field7
cmp1_2005_june cmp1 2005 june 5000 4000 1000
cmp1_2005_july cmp2 2005 july 10000 9000 1000
cmp2_2005_june cmp3 2005 june 6000 4000 2000

wheres:
field1 - field2&field3&field4
field2 - company
field3 - year
field4 - month
field5 - sales
field6 - cogs
field7 - field5-field6

This database is capturing YTD amounts for several companies monthly and I
need to take the difference from the current month for a certain company and
the prior month so the activity for the current month can be derived.
Any ideas on how to do this using combo boxes so the customer can choose the
company and period?

A "Self Join" query would be the ticket here. Your field1 should not
exist; it is redundant with fields 2, 3, and 4; and Field7 should also
not exist in your table, since it can be calculated on the fly in your
query, but that doesn't much matter in this case.

Create a query by adding your table to the query grid TWICE. Join the
two instances by company and year; use criteria like

=[Forms]![yourformname]![cboMonth1]

on the month fields from the two tables (and similarly for the
company).

John W. Vinson[MVP]

 

[Guest]

Thanks John,
....self join. very clever.
Will this make a difference if it is done on a query? In actuality the
example I gave you was the results of a VERY complicated query using
tmp_tables, other queries and tbls. that's why you thought field1 and field 7
were redundant but in the final report that this is populating needs all the
information.

I searched already on this question but I don't think it's going to help in
my case.
Each record is identified by company/year/month and the concatenation of all
three fields is the unique identifier for each record. The table could look
like this:

field1 field2 field3 field4 field5
field6 field7
cmp1_2005_june cmp1 2005 june 5000 4000 1000
cmp1_2005_july cmp2 2005 july 10000 9000 1000
cmp2_2005_june cmp3 2005 june 6000 4000 2000

wheres:
field1 - field2&field3&field4
field2 - company
field3 - year
field4 - month
field5 - sales
field6 - cogs
field7 - field5-field6

This database is capturing YTD amounts for several companies monthly and I
need to take the difference from the current month for a certain company and
the prior month so the activity for the current month can be derived.
Any ideas on how to do this using combo boxes so the customer can choose the
company and period?

A "Self Join" query would be the ticket here. Your field1 should not
exist; it is redundant with fields 2, 3, and 4; and Field7 should also
not exist in your table, since it can be calculated on the fly in your
query, but that doesn't much matter in this case.

Create a query by adding your table to the query grid TWICE. Join the
two instances by company and year; use criteria like

=[Forms]![yourformname]![cboMonth1]

on the month fields from the two tables (and similarly for the
company).

John W. Vinson[MVP]

 

[John Vinson]

Thanks John,
...self join. very clever.
Will this make a difference if it is done on a query? In actuality the
example I gave you was the results of a VERY complicated query using
tmp_tables, other queries and tbls. that's why you thought field1 and field 7
were redundant but in the final report that this is populating needs all the
information.

The self join will work between queries just as well as between
tables. After I posted I realized that these were probably calculated
fields in a query.

You might run the risk of the dreaded "Query Too Complex" error if the
constituent queries are really complex; some getarounds would be to
move the self join earlier in the set of queries, or (if need be) to
change your complex query to a MakeTable or Append query, fill a temp
table with the results, and do the self-join on that temp table.

John W. Vinson[MVP]