If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Aggregate function
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. |
#2
|
|||
|
|||
Aggregate function
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" wrote in message ... 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. |
#3
|
|||
|
|||
Aggregate function
Michel,
Thank You for your assistance Greg "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|