Apache OpenOffice Community Forum

I have three tables in this database; Clients, Products, Transactions.
Clients: Has one column containing the name of clients.
Products: Has two columns for Product name and corresponding Price.
Transactions: Has three columns for Client, Product, and Price.

I have one form in the database title TransactionEntry. When I select the client and product I need the form to auto fill the price field and populate client, product, and price into the Transactions table.

From the TransactionsEntry form I input clients name (populated by the Clients table) and the product (populated from the Products field.) I need the price field to auto update based on the Products table. All three fields need to be entered into the Transactions table.

With databases and tables like transactions, you keep only 'references' to the other tables and might add a transaction date in the transaction table. In your case the reference is also the 'semantic' data. That is somewhat unusual.
But from the transaction table you have the reference to the Product, so implicit also its price. It is a good habit not to copy (replicate) data in a database.

It looks like you are rather new to Databases, so please try to read some tutorial etc before you continue to develop and application.

Hi,

I have a question about this thread.. clarification
1.) The price for the transactions is reflected on the product?
-the price for the Product 1 is equal to -10 then if i choose Product 1 in transaction the price will update to -10

2.) The price for the product is reflected on the transaction?
-the price for the product 1 is equal to the latest price that input to the transaction

I think you must delete one column, either price for the transaction or price for the product since the query will copy of compute for the price.

Hello nicromx,
there is a possibility to do nearly what you want without need to use a macro. Look at the attached example, analyse the form structure with the form navigator to understand it. The advantage is, that you're able to change prices in table products, but keep old prices in table transactions.
R

path32 wrote: 1.) The price for the transactions is reflected on the product?
-the price for the Product 1 is equal to -10 then if i choose Product 1 in transaction the price will update to -10
2.) The price for the product is reflected on the transaction?
-the price for the product 1 is equal to the latest price that input to the transaction

The price is not reflected in the product name. I should have used Product A, Product B, Product C in the example. I appreciate your reply. I think my question has been answered.

F3K Total wrote:Hello nicromx,
there is a possibility to do nearly what you want without need to use a macro. Look at the attached example, analyse the form structure with the form navigator to understand it. The advantage is, that you're able to change prices in table products, but keep old prices in table transactions.
R

I think this what I was looking for. I actually started down the road of referencing product IDs. Good point on price changes though. I think your suggestion(attachment) is going to work better for my project. Thank you.