MSACCESS 2010 SumProduct (Expression or VBA)

[pholness]

I am new to MrExcel, but have gotten ALL my issues resolved simply by searching threads, however this one !@#

*This has been migrated from EXCEL to MSO ACCESS 2010

I have a database that uses the days of the week for its field names. Each row has similar data. Row 1 has the same data as Row 2 with one exception.

Row 1 is based on original commitment and Row 2 is based on incurred. I have two field names: YTD_Commitment_Incurred and Remaining_Commitment_ETC

YTD_Commitment_Incurred ='SUMPRODUCT((T2:NT2)*($T$1:$NT$1<TODAY()))'
Remaining_Commitment_ETC ='SUMPRODUCT((T2:NT2)*($T$1:$NT$1>=TODAY()))'

I am required to perform the same SUMPRODUCT VAlue in these fields for each row in my table

The test file: TA2013_PR_TrackingDB.accdb (too large for upload)

You guys/gals are great... experts and I lean to your wisdom on this. Thanks!

 

[xenou]

Can you provide some sample data and state in plain English what needs to happen? The Excel formula doesn't help since we don't know what data it points to (and it's not even a valid Excel formula). Are you trying to calculate YTD Commitment Incurred or Remaining Commitment, or something else? What is the definition of the calculation?

ξ

Would be better to go back to Excel probably - you don't have a proper structure here for a database table, just a spreadsheet in a database table, which would be better off in a spreadsheet ... Sometimes the powers that be think that by just putting something in Access it's going to be "bigger and better".

 

[pholness]

Yes, correct. I would like to send you an export from access into excel. How do I send attachment?

Plain English:

Column(field) NAMES are 1/1/2013 through to 4/30/2013.
Each Purchase Order contains one row for Commitment and another for Incurred

I need column EAC to sumproduct 1/1/2013 through to 4/30/2013 < Today()

Hope that hepls!!

 

[xenou]

I need column EAC to sumproduct 1/1/2013 through to 4/30/2013 < Today()

What is column EAC?

Posting some dummy data might help (an example of the calculation -- doesn't have to be 100 columns).

 

[SydneyGeek]

The structure needs to be turned around, so you have PurchaseDate (your current column headings), PO_number, Commitment and Incurred as 4 fields with multiple rows.

Then it's possible to build a query that gives you the 2 calculated fields you require.

Denis

 

[pholness]

The structure needs to be turned around, so you have PurchaseDate (your current column headings), PO_number, Commitment and Incurred as 4 fields with multiple rows.

Then it's possible to build a query that gives you the 2 calculated fields you require.

Denis

Thank you for he advice! I actually figured it out today! I am using VBA to create my own SUMPRODUCT which works perfectly! Now onto my next and final issue....I'm not sure if you or others would appreciate it here - so i'll create another thread Thansk!