Member Avatar for Zxander

Hello !
I'm having a university project where I have to make a database on MS Access .
The subject is :

We have a company that deals with trading. You need to make a database
to serve cost and product discounts for customers. The database must
contain :

-Nomenclature of customers with fields of your choice
-Nomenclature of items with fields of your choice
-Nomenclature, which represents individual prices for
certain items pertaining to specific customers.

When selecting a customer, the database must show all items with
the respective prices depending on whether a customer is using it
individual prices or not.

I am newbie in database management and its really hard for me at this moment,
so any help wil be appriciated ! :)

  • 2 Contributors
  • 4 Replies
  • 387 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by Zxander

Dani AI

Generated

Useful, practical plan to get the form working (answers follow-up posts by ; thanks to for the nudge to show what you tried).

Start with a single “Customer selector” control on the main form (combo or listbox) and a subform beneath it that shows every item with the price the selected customer will pay. Do the price logic in the subform’s record source so the UI simply requeries when the customer changes.

Example SQL for the subform RecordSource (replace form/control/table names with yours):

SELECT I.ItemID,
       I.ItemName,
       I.BasePrice,
       Nz(CP.Price, I.BasePrice) AS EffectivePrice
FROM Items AS I
LEFT JOIN CustomerPrices AS CP
  ON (I.ItemID = CP.ItemID AND CP.CustomerID = Forms!frmMain!cboCustomer);

Nz(...) returns the base price when no customer-specific price exists. If Access rejects the Forms reference in the ON clause, use the same condition in the ON but ensure you run the SQL from the form context (or create a saved query and reference the form control in the query’s criteria).

Code to refresh the subform when a customer is picked:

Private Sub cboCustomer_AfterUpdate()
    Me!subItems.Form.Requery
End Sub

Private Sub Form_Current()
    Me!subItems.Form.Requery
End Sub

Notes and troubleshooting

  • Use the subform control name in VBA (not the subform’s object name).
  • For a percent-discount column in the same query:
    DiscountPct: IIf([BasePrice]=0,0, Round(Nz(([BasePrice]-[Price])/[BasePrice]*100,0),2))
    (use Nz and check BasePrice<>0 to avoid divide-by-zero).
  • Avoid DLookup in row-by-row calculations; joins are far faster.
  • Index CustomerPrices on (CustomerID, ItemID) for performance.
  • If you still see duplicates or missing rows, paste your exact table names and the subform control name so you get a precise SQL/VBA tweak.

Recommended Answers

All 4 Replies

Member Avatar for Reverend Jim

The way this works is that before you get help you have to show that you have put in some effort to solve it yourself. It would help if you Read This Before Posting a Question.

Following that, please feel free to post your questions here.

Member Avatar for Zxander

Okay, I have my database with my relations, but I don't know how to make the form.
Have to make a list with the available client records and when I click a client to show above in another list the discounts for a specific product ?
Does anybody have an idea ?

Edited by Zxander
Member Avatar for Zxander

This is my database

Capture.PNG 26.32 KB
Member Avatar for Zxander

How can I calculate the % of a price, but taking one column from one table and one column from another ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.