Totals by month for table with multiple dates

[RJB2324]

I am new to Access and would like some advise on creating a monthly report showing totals for revenue and spend with different dates, where the totals are for the month the revenue or spend occurred.


The application I want to create is to track furniture restoration projects. I have two tables one for Furniture and the other Overheads (see attachment).
I would like to create a report with Furniture Cost, Furniture Labour, Revenue etc. by month
I am not a programmer but willing to try. Grateful for any advise. Thanks

[ranman256]

make a form, say frmRpts. On it have 2 text boxes for dates, txtStartDate, txtEndDate.
When the user selects a begin and end dates, use some code to fill in the date boxes. (or manuall fill)

Then the base query Q1, that reads the boxs for criteria...
select * from table where [DateEvent] between forms!frmRpts!txtSTartDate and forms!frmRpts!txtEndDate

now you can make another query , Q2, that sums Q1 data
(the month or Qtr, etc)

make a report on Q2.

[mike60smart]

Hi
Would 1 Customer only ever purchase 1 piece of furniture?

[CJ_London]

could really do with you explaining what the fields represent because it looks more like an excel approach rather than a database approach

a few questions:

are customers 'regular' or are they all 'one offs'?
what is the relevance of 'date labour'? Surely these restorations can take more than a day?
does the labour rate vary from job to job? if so, why?
what about sundry materials? or are they insignificant?

there is no need to store your calculated costs, this would be done in a query
better not to use attachment fields, they take up a lot of space - store in a folder and use a text field to store the link

[orange]

Further to the responses posted, since you are new to Access, many will advise you to NOT use names with embedded spaces nor special characters \ and -. Also it is helpful in communications with readers to include an overview of your business and its major processes.
Do you have different categories of restoration projects? When I think about restoration projects involving a variety of different objects, I conjure up a Customer with an object of some general type, a description of the potential work involved, the steps required and their sequence, estimated and actual time effort and costs. But I'm not in the business.

Seems you could start with a database template for a generic service and adjust as needed.

Lots of database planning and design (and other) articles in the link in my signature.

Welcome to the forum.

[RJB2324]

Thank you all for your prompt response. I will try and answer some of the questions you have posed.
The business is a part time start up that buys old items of furniture, repairs and generally paints them to give them a modern look.
So far each customer has only purchased one item, hence the customer details with the furniture rather than a separate table. The furniture is catagorised into Tables, Chairs, Sideboard etc.
The Date Labour is to give an indication when the refurbishment took place. Typically it will take several days but a single date along with the approximate number of hours is sufficient. The Labour Rate is defaulted at the moment but there is an option to override this. The overheads Table is for the general items such as ironmongery, paint, rent, tools etc. that do not belong to a specific item of furniture.

Thank you for the tips regarding attachments and naming conventions, I will take these on board.

I will take a look at the suggested approach to gather the dates and totals this afternoon.

[Welshgasman]

So far each customer has only purchased one item, hence the customer details with the furniture rather than a separate table

Well let's think about the customer liking what they have and coming back for more?

So it makes sense to get the design correct at the start, else just more work further down the road?

[Micron]

Not to mention that it's just plain incorrect design - lacks normalization. You don't build for what is, you build for what can be. Research db normalization - don't get off on the wrong foot. Even if you never have a repeat customer, that design approach will permeate throughout your db and eventually, you will probably pay for it in terms of difficulty getting information in and out.

[RJB2324]

I have now updated the Furniture Table and also tried to explain in more detail what I would like to achieve. The Furniture Table has three cost and Revenue items and 3 corresponding dates. For a single item of furniture the cost, labour and revenue could all be in separate months.

I have attached some sample data to illustrate this.

What I would like to be able to do is by Year and Month show how much was spend on buying furniture, the cost of labour and the revenue. From these a Profit and Loss per month is calculated. This is DIFFERANT from the Profit/Loss on a specific item of furniture. I have put an example of the report I would like to create based on the sample data.

Thank you all for the general advise and best practice. Now, any help on how to create this report would be appreciated.

Many thanks

[mike60smart]

Hi
Can you upload the actual database?

[mike60smart]

Hi
As Micron has already indicated, your Furniture table is NOT normalised.

[Welshgasman]

Create a MonthYear field and group on that with Sums for your various columns.
That would be a workaround.

[RJB2324]

Thank you for the advise regarding MonthYear but I can't see how this will work as I will have to select a single date to base this on. Hence, other dates may be in the wrong month.

I have set up the Furniture table to reflect that this is the most important part of the DB. However, I am happy to try other designs if it is better practice.

I have attached a cut down version of the DB.

Thanks

RJBTest1.zip

RJBTest1.zip

[Welshgasman]

Thank you for the advise regarding MonthYear but I can't see how this will work as I will have to select a single date to base this on. Hence, other dates may be in the wrong month.

I have set up the Furniture table to reflect that this is the most important part of the DB. However, I am happy to try other designs if it is better practice.

I have attached a cut down version of the DB.

Thanks

RJBTest1.zip

RJBTest1.zip

No, you just group on the month and year, so every date in a particular month and year gets aggregated?

[CJ_London]

the problem is your data is not normalised

you need to use a union query to get the relevant data into a normalised form, then you can do your grouping

Something like

SELECT "Cost" as TranType, T_Furniture.Date_Bought as TDate, T_Furniture.Furniture_Cost as TValue
FROM T_Furniture
UNION SELECT "Revenue", T_Furniture.Date_Sold, T_Furniture.Furniture_Revenue
FROM T_Furniture;

call this Qry1

then you can use a crosstab query

TRANSFORM Sum(Qry1.TValue) AS SumOfTValue
SELECT Format([TDate],"yyyy-mm") AS yyyymm, Sum([tvalue]*IIf([trantype]="Cost",-1,1)) AS Net
FROM Qry1
GROUP BY Format([TDate],"yyyy-mm")
PIVOT Qry1.TranType

which produces this result

yyyymm	Net	Cost	Revenue
	0		0
2021-10	0	0
2021-11	-30	30
2021-12	240	10	250
2022-01	140	160	300
2022-02	-90	90