Unit price increase and how to report historic sales as well as present sales - help needed please

[anniehall]

I run a very small cottage industry making and selling handmade cards. I've created a database (Access 2010) to manage this, which holds details of my cards (400 ish), all my sales, delivery info, invoice templates, etc. I've decided it's time to increase the prices slightly (probably by 50p per unit) as they've been the same for over 5 years.


I need advice on how to increase them throughout the database; however, I have reports I run to show, for example, the total sale of cards to my local farm shop, folksy, etc. If I increase my prices from, say, 1st November 2016, I need these reports to show sales totals prior to my price rise as well as showing current sales.
Can anyone help please? Annie

[ranman256]

the PRICE must be added to the tOrder detail table:
OrderID, itemID, Qty, Price

this Price comes from the tPrice table at that time. Then when prices increase on the tPrice table, it does not affect items already purchased.
But all NEW purchases get the new price.

[aytee111]

Make changes to your database that will handle this situation next time as well.

1 - store the price with your order, that way it will never change and you can always know what your sales were
2 - store the price in one other place only, associated with the product
- either the price can be directly on the product table
or
- you can have a price table which can contain a date - each product will be associated with a certain price (including a date may be overkill for your requirements, however)

[anniehall]

the PRICE must be added to the tOrder detail table:
OrderID, itemID, Qty, Price

this Price comes from the tPrice table at that time. Then when prices increase on the tPrice table, it does not affect items already purchased.
But all NEW purchases get the new price.

Thanks for your suggestion but I need a little more help than that.

I'll tell you a little about my database. The main table (Cards Record) holds information such as card name, size, photo, ID number and price. Another table (Card Movement Record) records date card sent, and retrieved and date card sold amongst other things. I have a form based on Tbl CardsRecords for inputting new cards with two subforms, (based on Tbl Card Movement Record) one which I complete when I send cards to my outlets (so I can run a report of which cards each outlet has). The other subform is where I record sales - ie I input the outlet and the date of sale. From these forms and subforms, I have queries which input data into various reports - "cards sold between * and *", "cards currently at [outlet]", "cards sold from [outlet]" and invoices to outlets, for example.

I don't have an Order table, nor do I have a Price table. My orders come by email from Folksy (a craft website) and when I deal with the farmshops and local outlets, I decide what I'm putting on the shelves and when I sell anything, I invoice the shop owners for unit price less 50p/per card.

Can you help further?

[ranman256]

MAKE a price table.

[orange]

See post #6 in this thread
https://www.accessforums.net/showthr...434#post326434

I have written about AgreedToPrice and Loyalty programs. You could search for these terms, if th link I mentioned isn't helpful.

You could post your database but remove personal/private info. You could make a copy and change names to PorkyPig, Donald Duck, Heeza Payne....

Good luck.

[anniehall]

MAKE a price table.

Could you explain how a price table would help please. As you can tell I have only a basic knowledge of database writing!

[anniehall]

See post #6 in this thread
https://www.accessforums.net/showthr...434#post326434

I have written about AgreedToPrice and Loyalty programs. You could search for these terms, if th link I mentioned isn't helpful.

You could post your database but remove personal/private info. You could make a copy and change names to PorkyPig, Donald Duck, Heeza Payne....

Good luck.

Thanks Orange. I'll have a look

[anniehall]

See post #6 in this thread


You could post your database but remove personal/private info. You could make a copy and change names to PorkyPig, Donald Duck, Heeza Payne....

Good luck.

It's pretty huge. Would it work if I stripped out most of the data?

[anniehall]

I've attached a stripped out copy of my database. Can you take a look and explain in simple terms how I can achieve what I want. The switchboard shows the reports and forms I use most frequently.
Thanks
Ann

[aytee111]

As mentioned above, your have no price history. This must change, the price must be added to your orders (Card Movement). It is also missing off your invoices table, there is no amount on there. That is what is known as keeping history and is really the point of a database as opposed to doing it all on a spreadsheet.

1 - add price to your Movement table
2 - run an update query that will update all the records in the Movement table from the Cards table
3 - now you can change the price on the Cards table without it affecting your history

[anniehall]

As mentioned above, your have no price history. This must change, the price must be added to your orders (Card Movement). It is also missing off your invoices table, there is no amount on there. That is what is known as keeping history and is really the point of a database as opposed to doing it all on a spreadsheet.

1 - add price to your Movement table
2 - run an update query that will update all the records in the Movement table from the Cards table
3 - now you can change the price on the Cards table without it affecting your history

Thanks for your suggestion aytee111. I've added a price field to my Card Movement Record table. Do I need to add it in to the Card Movement Record subform (which appears on my Card Entry form)? And going on from that, would I need to enter the price every time I select a card to take to an outlet? Or is this just a procedure I need to follow when I change my prices?

[aytee111]

Yes, if you want to keep history then you have to enter it, otherwise you will have the same problem in another 5 years!

Here is one way of doing it - when you add a new record in your subform it will default the price from the Cards table. The VBA code is in the BeforeInsert event - time to get your feet wet!

[anniehall]

Yes, if you want to keep history then you have to enter it, otherwise you will have the same problem in another 5 years!

Here is one way of doing it - when you add a new record in your subform it will default the price from the Cards table. The VBA code is in the BeforeInsert event - time to get your feet wet!

Hi aytee111
That worked brilliantly with a bit of tweaking (I decided to rename the Price field in the Cards table to reflect the updated prices so that initially caused a bit of a problem). One problem I've still got is that if I run any of the reports on the switchboard (which cover both price periods), the items from the new pricing period, which are now defaulting the new price from the Cards table, do not show a price on the report.
I can't work out how to resolve this.
Can you help? As before, I've attached a copy of the database with just one or two records remaining as examples.
Thanks

[aytee111]

Please be more specific. Which report exactly is it and which field name? Everywhere I looked the price was being displayed correctly.