Update Price change without changing price in past records, HELP! (newbie here)

[radian89]

Hi All,



I'm newbie in access, I have a product table with sell price for each item, and have invoice subforms for transactions. what i expected is, how to update price change without changing price in past records (so when i create reports/view past invoice for previous sell history, the price displayed are those that valid at that time).

from what i search in google, i need to create another table, which records ItemsID, StartDate, and Price. which will call the updated price base on date.
I don't know how to do it. much appreciate for step by step help.


thanks a lot for the help

[orange]

Since you are new to Access, there are some videos here that will guide you on database design and the "price on old invoices". You are missing an OrderDetails or InvoiceProduct table. The videos will help.

See post #3 here for more info about the "AgreedToPrice".

Good luck.

[radian89]

Since you are new to Access, there are some videos here that will guide you on database design and the "price on old invoices". You are missing an OrderDetails or InvoiceProduct table. The videos will help.

See post #3 here for more info about the "AgreedToPrice".

Good luck.

Hi Orange,

Thanks for the videos and the post #3, this is what i get, CMIIW, so in my :
1. product table there's sell price
2. I already have invoiceproduct table (as a subform in Invoice form, master : Invoicenumber, child : Invoicenumber).
3. I need to put "AgreedToPrice", or Price that happened at the order --> which leads me to think like, copy the price in product table and paste as value in AgreedToPrice field in InvoiceProduct table (just happened to do it every time in excel). so it can record from time to time

so if it was like that, how do i do the step number 3? how & where to write the formula?

thanks a lot for the help

[orange]

In the Product table is the current selling price of the Product. It can be changed at any time.

Get your tables and relationships designed, tested and vetted before moving to forms etc.
Can you post a jpg of your relationships window? Extend each of the tables to expose all fields.
Or post a copy of your database.

Yes, you can copy the Price from the Product table, or you could reduce that Price if there was a Sale, or if the Customer was part of a loyalty program etc. The key point is to record the price you agreedTo in the OrderDetail (or InvoiceProduct) table. This makes that value a permanent record. It will not change when you adjust the Product's current price in the Product table.

[radian89]

In the Product table is the current selling price of the Product. It can be changed at any time.

Get your tables and relationships designed, tested and vetted before moving to forms etc.
Can you post a jpg of your relationships window? Extend each of the tables to expose all fields.
Or post a copy of your database.

Yes, you can copy the Price from the Product table, or you could reduce that Price if there was a Sale, or if the Customer was part of a loyalty program etc. The key point is to record the price you agreedTo in the OrderDetail (or InvoiceProduct) table. This makes that value a permanent record. It will not change when you adjust the Product's current price in the Product table.

here's my relationship table & database
Database11.zip

thankyou so much

[orange]

Normally when you are dealing with Invoices, there will be a Client/Customer involved. I don't see a Customer in your design, nor an Order.

Here is a free data model from Brian Williams' site re Customer and Invoices, it may give you some ideas.

Note: It is a generic model that illustrates many of the most common things dealing with Customer and Invoices. It may not deal with something specific to your requirements, or it may have more detail in parts than you need. It is intended as a guide - a starting point- that you can adjust as necessary.

I do not understand some of your tables dtbsBarangT, TambahstockT.

Also, I recommend you do not use names with embedded spaces. It will cause you syntax errors at some point.
Use only alpha, numeric and underscore(_) for field and object names.

I think it would be good for you and readers if you would write a 5-6 line description of the business your database is intended to support. It will help to understand your environment and "opportunity" and relate the various entities in a business context. Also, writing a 1 line description of your tables and fields as you begin makes for good communication and documentation.

For example: What exactly is a subInvoice?

[radian89]

Normally when you are dealing with Invoices, there will be a Client/Customer involved. I don't see a Customer in your design, nor an Order.

Here is a free data model from Brian Williams' site re Customer and Invoices, it may give you some ideas.

Note: It is a generic model that illustrates many of the most common things dealing with Customer and Invoices. It may not deal with something specific to your requirements, or it may have more detail in parts than you need. It is intended as a guide - a starting point- that you can adjust as necessary.

I do not understand some of your tables dtbsBarangT, TambahstockT.

Also, I recommend you do not use names with embedded spaces. It will cause you syntax errors at some point.
Use only alpha, numeric and underscore(_) for field and object names.

I think it would be good for you and readers if you would write a 5-6 line description of the business your database is intended to support. It will help to understand your environment and "opportunity" and relate the various entities in a business context. Also, writing a 1 line description of your tables and fields as you begin makes for good communication and documentation.

For example: What exactly is a subInvoice?

Hi Orange,

thanks for the advice & well noted, and i'm sorry the database i made is in different language,

here's the description

what i'm trying to made is an invoice where we do not need (in my case) a customer records, so the invoice will treat any customer as a regular one.

in the relationship table
InvoiceT = is where the invoice Table, contain Invoice number & date
SubinvoiceT = is the order items, contain BarcodeIDitem, discount, itemquantity & AgreedtoPrice
ProductItemT= is item database, contain item information, and sell price
AddstockT = is to record every time, the supplier send new item, it'll be stored there
ItemSupplier = is the record of supplier database
ItemCategory = is the item category

I don't input a customer records here.

thanks a lot.

[orange]

Interesting. Who pays the Invoice?

There is an issue with your link between ProductItemT and SubInvoiceT. You have IDItem (the PK) linked to BarCodeItemID. BarcodeItemID should be IDItem (the FK which links back to IDItem in ProductItemT).

What exactly is Discount in subInvoiceT? Isn't that accounted for in AgreedToPrice?

What is the specific issue at this time?

[radian89]

Interesting. Who pays the Invoice?

There is an issue with your link between ProductItemT and SubInvoiceT. You have IDItem (the PK) linked to BarCodeItemID. BarcodeItemID should be IDItem (the FK which links back to IDItem in ProductItemT).

What exactly is Discount in subInvoiceT? Isn't that accounted for in AgreedToPrice?

What is the specific issue at this time?

it'll be 1 invoicenumber = 1 customer paid by cash & No String Attached (I know it sounds silly, but somehow it works that way, I'll be making the second version with customer relationship)

The BarcodeIDItem relationship was made by lookup wizard, so it'll lookup to the ProductItem table, as IDitem and refer/display it to BarcodeIDItem.

Discount in subinvoiceT accounted for AgreedToPrice, it'll manually defined by user

The issue, is how to make the AgreedToPrice value as a permanent record that refer to each items in ProductItemT? do I use lookup wizard / write function / macro? if i do so, it'll still change if the SellPrice in ProductItemT change.

do i also need Total = [Itemqty] * [AgreedToPrice] * (1-[Discount]), field in subinvoiceT?

thanks so much

[orange]

Most developers will advise you to avoid the lookup wizard.

Do not use lookups at the table field level. You can have Lookup tables that can be used with forms.

Do not store Totals in a table. Always use a query to calculate the Total.
AgreedToPrice, in my view would be the final discounted price. However, if you want to use a Discount factor, and use AgreedToPrice as the ThenCurrentPrice of the Product, that's up to you. You just have to be consistent.

[radian89]

Most developers will advise you to avoid the lookup wizard.

Do not use lookups at the table field level. You can have Lookup tables that can be used with forms.

Do not store Totals in a table. Always use a query to calculate the Total.
AgreedToPrice, in my view would be the final discounted price. However, if you want to use a Discount factor, and use AgreedToPrice as the ThenCurrentPrice of the Product, that's up to you. You just have to be consistent.

So, what do i write in AgreedToPrice field? Do i use IIF? The AgreedToPrice field need to automatically show the price value when ItemID was inputted. The discount will be input manually by user, so it won't be calculated in AgreedToPrice.

Sorry Orange, I'm still in learning curve here... Thanks a lot..

[orange]

The Product table has the current Price. And that Price can/will change with time.
So, you could copy the Product Price to AgreedToPrice.

I don't know how you input/processing procedures work. The user will input the Discount???
Please tell us more.

This is one of those times where details of the process would be helpful to readers. It would provide business context.

To me, you sell X quantity of Product W, the Price at that time is S dollars per Item of W.
So the "price" is S. If you agree to a 10% discount for whatever reason, then the AgreedToPrice is .9S.

I don't understand how, after the fact (it seems), the user determines some sort of Discount??????

[radian89]

The Product table has the current Price. And that Price can/will change with time.
So, you could copy the Product Price to AgreedToPrice.

I don't know how you input/processing procedures work. The user will input the Discount???
Please tell us more.

This is one of those times where details of the process would be helpful to readers. It would provide business context.

To me, you sell X quantity of Product W, the Price at that time is S dollars per Item of W.
So the "price" is S. If you agree to a 10% discount for whatever reason, then the AgreedToPrice is .9S.

I don't understand how, after the fact (it seems), the user determines some sort of Discount??????

Yes that's correct. In this case the default discount is 0, so you will talk to & negotiate with the merchant to get the better price, and the merchant will input the discount, so it'll happened real time. It's different country with different culture & business type, so adjustment in the process is necessary, I know it sounds weird, but that what actually happened in traditional market.

So in AgreedtoPrice, manually copy the PriceItem from the ProductT ? Can it goes automatically?

[June7]

User selects product from a combobox - named cboProduct. The combobox can have a column with the product price. Code can grab that price and save to record. Refer to combobox column by index. If price is in second column, its index is 1.

Me!AgreedtoPrice = Me.cboProduct.Column(1)

This code would go in the AfterUpdate event of cboProduct. It is VBA syntax. There is macro equivalent.

User can edit the AgreedtoPrice in bound textbox if you want to allow that.

[orange]

Sure.

You need some processes to do certain things.

If you sell some Product X, you will need to record the current Price of Product X in AgreedToPrice.
You will need to record the quantity of Product X involved.

When you report or query your data, you will have to calculate

AgreedToPrice * Quantity (* Discount) to get Total

If you are intent on recording Discount, then you need some mechanism to record it in each record.
If you default to 1, then if a user wants/inputs 10%, then your logic will have to deal with the actual discount rate.
AgreedToPrice * Quantity (* Discount) to get Total

AgreedToPrice * Quantity (* (1-.1) to get Total where .1 represents 10% discount (or 90% of agreedToPrice)