How do I recreate Excel formula in Access query? Help plz

[alinka]

Here's my formula in Excel:

=(YEAR(AF2)=2003)*SUM(AP2:INDEX(AP2:BA2,1,MONTH(AF2)))+(YEAR(AF2)>2003)*SUM(AP2:BA2)


In Excel,
1) AF2 is a date

2)AP2:BA2 are columns which represent each month of the year, i.e. Jan - Dec. These columns have financials.


In an Access query that I want to design, I will be querying on a table that will contain dates (AF2) and will also contain a field for each month (Jan through Dec). How do I recreate the same formula as above in the expression that needs to be created in my access query?

THANK YOU SO VERY MUCH IN ADVANCE.

 

[Russell Hauf]

Re: How do I recreate Excel formula in Access query? Help pl

What other data do you have? Basically, you shouldn't set up your table in Access like it is in Excel. Can you provide an example of how your data is set up in Excel?

Thank you,

Russell

 

[alinka]

Re: How do I recreate Excel formula in Access query? Help pl

Dear Russell,

Thank you very much for responding. I have sent sample data in excel to you to your Email address.

 

[Russell Hauf]

Re: How do I recreate Excel formula in Access query? Help pl

Well, unfortunately this is not easily done in Access they way that you currently have the data. A better way would be to have 2 tables, one like this:

<table border="1"><tr><td>Person</td><td>End 50% Share Date</td><td>End 25% Share Date</td></tr><tr><td>Person1</td><td>02-Dec</td><td>03-Dec</td></tr><tr><td>Person3</td><td>04-Jan</td><td>05-Jan</td></tr><tr><td>etc.</td><td>.</td><td>.</td></tr></table>

And one like this:

<table border="1"><tr><td>Person</td><td>Date</td><td>Amount</td></tr><tr><td>Person1</td><td>01/01/2003</td><td>0</td></tr><tr><td>Person1</td><td>02/01/2003</td><td>175</td></tr><tr><td>Person1</td><td>03/01/2003</td><td>112</td></tr><tr><td>Person1</td><td>04/01/2003</td><td>-287</td></tr><tr><td>...</td><td>.</td><td>.</td></tr><tr><td>Person3</td><td>01/01/2003</td><td>0</td></tr><tr><td>Person3</td><td>02/01/2003</td><td>1,417</td></tr><tr><td>Person3</td><td>03/01/2003</td><td>1,103</td></tr><tr><td>etc.</td><td>.</td><td>.</td></tr></table>

I know that's not really what you wanted to hear, but Excel tables don't always transfer nicely to Access so that you can query them (hence all of the nifty Excel functions and the formulas that you use them in). If you decide that you want to convert your data into a format like this, let me know and I'll help you write the query.

 

[alinka]

Re: How do I recreate Excel formula in Access query? Help pl

Hi Russell,

I am totally willing to hear your advice. I'm sure you know the best. Is there an easy way to split the table that I gave you in the sample data file into the two tables that you described? I know there's something called the "crosstab query" or "append query" in access - but i can't really figure out how to use them.

Also,

Would you be interested in a little consulting project and there's slightly more to it, but the formulas are the hardest part. If so, can you give me a price quote? Thanks so very much.

 

[Cosmos75]

Re: How do I recreate Excel formula in Access query? Help pl

You can use the table analyzer to split your table into two. Go to Tools-> Analyze -> Table. Just follow the wizard instructions and choose "No, Let me decide." when it asked you wheter or not you want the wizard to decide how to split it. Be sure to have a backup of your database BEFORE you attempt this just in case it doesn't work and you need to go back to your original copy to try it again.

If you click the table while in the Wizard you can also set the name of the new tables.

You'll probably need at least three tables.

tblPerson
PersonID (Primary Key)
Person - Text field

tblAmount
AmountID (Primary Key)
Person (Foreign Key)
Amount - Currency field

tblShareDateType
ShareDateTypeID (Primary Key)
ShareDateType - Text field (50%, 25%....)

I am not quite sure what you are trying to accomplish but I'd be willing to help if you can post more detail on what exactly you are trying to accomplish.


Edit: One more table

tblPersonShareDate
IndividualShareDatesID (Primary Key)
PersonID (Foreign Key)
ShareDateTypeID (Foreign Key)
ShareEndDate - Date field

You can email me your spreadsheet at azli@gbpackersfan.com

 

[Russell Hauf]

Re: How do I recreate Excel formula in Access query? Help pl

You forgot the date part of it. Check her post in the Excel forum for an example of how her data looks now. Currently she has a column for each month of the year...so splitting it out won't really help here...

 

[Cosmos75]

Re: How do I recreate Excel formula in Access query? Help pl

tblAmount
AmountID (Primary Key)
Person (Foreign Key)
AmountDate - Date field
Amount - Currency field

You don't really need a field for each month. Just store the date and retrieve the month.

If you absolutely must store months, I'd create a table to store them.

tblMonth
MonthID - Number (Primary Key)
Month - Text (JAN, FEB....)
- Make sure the MonthID matches the number for the month.

Now for the monthly finacials.

tblMonthlyFinancials
MonthlyFinancialsID (Primary Key)
PersonID (Foreign Key to tblPerson)
MonthID (Foreign Key to tblMonth)
Year - Number field (or just create a table to store ONLY years)
MonthlyFinancials - Number field

Here's my understanding of her formula.

Say date is 06/06/2003
1) Take year of date for the current record. (2003)
2) Sum from Jan to Month of date for the current record. (Jan to June - 1 to 6)
3) If year of date for the current record > 2003 (or are you actually wanting to compare it to the the current actual year? - i.e. next year you want to compare to 2004?), if so then sum Jan through Dec.

Formula = 1) * 2) + 3)??

Create a non-continuous form to view only one record at a time, let's refer to it as frmView for now (Hopefully this will work for your purpose).

In a Select query you can use the criteria to show only for the current Person shown on form AND Month(AmountDate) <= Form!frmView.[AmountDate] AND Year(AmountDate) <= Form!frmView.[AmountDate]. Use this query as your recordsource to show details of calculation which can be performed with a control on the form.

 

[Cosmos75]

Re: How do I recreate Excel formula in Access query? Help pl

Alinka,

Did you ever get this solved??