Aggregate function

[Greg]

I need some general guidance on how to approach a problem I can not
personally solve.

Each month I recieve an excel row with about 40 columns in the row.

I manually reformat the date and do an import into an access table called
"monthly invoice details".

There are about 12 months of information in the table.

I have written a select query to pull out of the table the last 2 months of
invoice details ( I call the records) "this Mo" and "last Mo".

Now the question. I would like to write another query that will subtract
about 20 fields in the "this mo" record from the corresponding fields in the
"last Mo" record. I only need to deal with 2 records.

The aggregate function works fine if I make manually make all the fields
negative in one of the records.

I expect my results to come out positive and negative due to the nature of
the date.

Does anyone have any suggestions as to how to do a subtract through the
aggregate function, I know that in most cases you will not be dealing with
only 2 records.

Thank You in advance for any suggestions.

 

[Michel Walsh]

You can aggregate on expressions:



instead of
SUM( amount )



you can use
SUM( iiif( dateTimeField > = #3/1/2009# , 1, -1 ) *
amount )


as example, which will use ( - amount) if the dateTimeField is before
March the First, 2009, and + amount otherwise.



Vanderghast, Access MVP

 

[Greg]

Michel,
Thank You for your assistance
Greg

You can aggregate on expressions:



instead of
SUM( amount )



you can use
SUM( iiif( dateTimeField > = #3/1/2009# , 1, -1 ) *
amount )


as example, which will use ( - amount) if the dateTimeField is before
March the First, 2009, and + amount otherwise.



Vanderghast, Access MVP