multiply across tables and columns based on date (1 Viewer)

[sankumar27@hotmail.c]

I have two tables: (a) Calendar dates (b) Monthly Expenses budgeted

Calendar_dates table field are
(a) Day (Calendar day for 4 months)
(b) Daily rates %

Monthly expenses budgeted table fields are
(1) Business line1
(2) Business Category
(3) Business Grooup
(4) Month1 expense budgeted amount
(5) Month2 expense budgeted amount
(6) Month3 expeense budgeted amount
(7) Month4 expnense budgeted amount

i need to multiply daily rates% for a particular day with the corresponding month expense budgeted amount in Monthly expense table. The output should be in following format:
(1) Calendar day (from days table)
(2) Business Line1
(3) Business group
(4) (Calendar_Dates.Daily rates%) * corresponding month expense budgeted amount

I am able to multiply all the daily rates for a single month expense budgeted amount in a simple query. If someone could help me to multiply based on the corresponding month criteria it would be very helpful

 

[theDBguy]

Hi. Can you create an Excel worksheet with some sample data and show us the outcome you're trying to get out of it? It would help us understand your situation better and come up with a possible solution.

 

[sankumar27@hotmail.c]

output file is attached.

 

[vba_php]

kumar,

you're already getting what you want in your xl file. the field "Expected outocome" has all the right values in it. if you have all this data in access structured in the proper way (oh my! I'm turning into a "proper" engineer like everybody else here!), I wouldn't see why you couldn't query it out in the right way. but you uploaded an xl, instead of an accdb. I would bet you someone here would show you how to query it based on what the data looks like in an accdb. how about uploading that extension instead?

there's 2 cents worth of unorthodox suggestions for you....

 

[Pat Hartman]

The problem of course is that the table is not constructed properly. If you have repeating columns in a table such as Dec19, Jan20, Feb20, and Mar20, you have EXACTLY the same problem you have in Excel. Every column requires a separate calculation. When you add a new column or drop and old one, ALL your calculations will need to change. When you move from Excel to Access so that you are now using a relational database, you need to take off your Excel hat and think in relational terms.

The format of this table should be:
SomeUniqueID (autonumber(
BusinessLine1 (fld1 of unique idx)
BusCategory (fld2 of unique idx)
BusGroup (fld3 of unique idx)
BudgetPeriod (fld4 of Unique idx)
BudgetAmt.

Budget period will be a date field holding 12/1/19 or 1/1/20, etc. Use either the first day of the month (simpler) or the last day. When displaying the data, add a formatted column in your query but ALWAYS sort by the actual BudgetPeriod rather than the formatted data.

This way, you can add months in your budget WITHOUT changing ANYTHING.

Use a form with selection criteria. Enter the StartDate and in your criteria, use
Where BudgetPeriod between Forms!yourForm!StartDate and DateAdd("m", 4,Forms!YourForm!StartDate) -- if you want a four month rolling view. If you want more months, use something other than 4.

If you want to have multiple options for month, you can create the SQL on the fly or create multiple queries. qRollingFourMonth, qRollingYear, qCalendarYear

Having fixed queries makes reporting easier. You would have a different report depending on what query. Although qRollingYear and qCalendar year could use the same report format since the number of columns would be the same.

If you want to see the results in a horizontal format as you do in Excel, use a crosstab query to pivot the data. Remember, if you want the columns in order, you must pivot on the BudgetPeriod. We'll help you to standardize the column headings when you get to that point.