When you say area, do you mean the geographic regions we discussed early on in this thread? If so, it might be better to use a multiselect list box based on the region table, that way it will automatically grow as you add new regions. I'm not sure if the checkboxes you currently have will do that. You can then use code to loop through the list box and use that to filter the record source of the form. I've attached an example database that illustrates this.
Customers and Orders tables - simple relationship
- Thread starter MikeLeBen
- Start date
When you say area, do you mean the geographic regions we discussed early on in this thread? If so, it might be better to use a multiselect list box based on the region table, that way it will automatically grow as you add new regions. I'm not sure if the checkboxes you currently have will do that. You can then use code to loop through the list box and use that to filter the record source of the form. I've attached an example database that illustrates this.
Luckily I managed on my own, I guess the more I get into access the more I need to learn some VB 
My boss just asked me to implement a new feature into the database:
some customers pay by cash, and some others have a prescription (since we make products for people with coeliac disease, there's a state aid in the form of monthly prescriptions). These prescriptions have fixed worths, they come in amounts of 99 or 140€ or the half of each of these figures.
Ideally I'd like to implement a way to keep track of these payments and since the customers normally take a while to use up all of their prescription money, different amounts would have to be subtracted from the total worth each time they place an order.
I figured I'd do a tblPrescription and relate it to tblOrders and tblCustomers, but I'm not sure how to manage the calculation to subtract the money periodically as they buy products. I'm guessing via a form, but some help will be really appreciated.
My boss just asked me to implement a new feature into the database:
some customers pay by cash, and some others have a prescription (since we make products for people with coeliac disease, there's a state aid in the form of monthly prescriptions). These prescriptions have fixed worths, they come in amounts of 99 or 140€ or the half of each of these figures.
Ideally I'd like to implement a way to keep track of these payments and since the customers normally take a while to use up all of their prescription money, different amounts would have to be subtracted from the total worth each time they place an order.
I figured I'd do a tblPrescription and relate it to tblOrders and tblCustomers, but I'm not sure how to manage the calculation to subtract the money periodically as they buy products. I'm guessing via a form, but some help will be really appreciated.
The prescription is related to the customer.
Can a customer have many prescriptions?
Do prescriptions have a date range for which they are valid?
Is each prescription only applicable to 1 product or many?
Basically, you would need to store the initial value of the prescription somewhere and it will remain stagnant.
You would need to total the orders of the applicable products and then subtract that from the prescription value. This would be done dynamically via a query since order will span a period of time.
The customers who have prescriptions are given a new one each month, and some of them have it directly delivered to us. Sometimes new customers come to the place and give us their prescription.
Sometimes (we're in Italy and it's already a miracle such a system exists) customers show up with a split prescription: let's say they are entitled to have 99€, but the public health system gives them two worth 49,50 for whatever reasons; what really matters is the total worth they are entitled to, each month.
Typically, they can legally use it for the corresponding month and the following one (even though this is not so strict and exceptions apply).
Each prescription can be used to purchase any and all of the products we offer.
Sometimes (we're in Italy and it's already a miracle such a system exists) customers show up with a split prescription: let's say they are entitled to have 99€, but the public health system gives them two worth 49,50 for whatever reasons; what really matters is the total worth they are entitled to, each month.
Typically, they can legally use it for the corresponding month and the following one (even though this is not so strict and exceptions apply).
Each prescription can be used to purchase any and all of the products we offer.
Since a customer has many prescriptions, that's a one-to-many relationship
tblCustomerPrescriptions
-pkCustPresID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-PrescriptionNumber
-PresValue (value of the prescription)
Since a prescription has a time for when it is valid assuming a start and end date, that is also a one-to-many relationship. So for each prescription you will have 2 records in the following table corresponding to a beginning date and end date.
tblCustomerPrescriptionPeriod
-pkCustPresPeriodID primary key, autonumber
-fkCustPresID foreign key to tblCustomerPrescriptions
-dteValid
-datetype (start or end)
Others might put start and end date fields in the tblCustomerPrescriptions table, but technically speaking it would violate normalization rules. I'll leave that one to you.
Initially I was thinking that you would not tie a particular prescription to an order, but if you must provide traceablity to public health officials then you probably would need to tie specific prescriptions to an order. Assuming that a customer could conceivably have multiple prescriptions open at a time and that the customer may apply more than one prescription to an order, then you would need a juction table.
tblOrderPrescriptions
-pkOrderPrescriptions
-fkOrderID foreign key to tblOrders
-fkCustPresID foreign key to tblCustomerPrescriptions
-AppliedAmount
tblCustomerPrescriptions
-pkCustPresID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-PrescriptionNumber
-PresValue (value of the prescription)
Since a prescription has a time for when it is valid assuming a start and end date, that is also a one-to-many relationship. So for each prescription you will have 2 records in the following table corresponding to a beginning date and end date.
tblCustomerPrescriptionPeriod
-pkCustPresPeriodID primary key, autonumber
-fkCustPresID foreign key to tblCustomerPrescriptions
-dteValid
-datetype (start or end)
Others might put start and end date fields in the tblCustomerPrescriptions table, but technically speaking it would violate normalization rules. I'll leave that one to you.
Initially I was thinking that you would not tie a particular prescription to an order, but if you must provide traceablity to public health officials then you probably would need to tie specific prescriptions to an order. Assuming that a customer could conceivably have multiple prescriptions open at a time and that the customer may apply more than one prescription to an order, then you would need a juction table.
tblOrderPrescriptions
-pkOrderPrescriptions
-fkOrderID foreign key to tblOrders
-fkCustPresID foreign key to tblCustomerPrescriptions
-AppliedAmount
You have a strict logic and always get to the point with the design, I hope to get just about half as skilled as you are and I'd be fine 
The database is for internal use only, no information about it and the customers' actions is supposed to ever be used in other contexts. We put barcode stickers for the items purchased by customers on the prescriptions that we then return to the health system to be accredited the money - that's all the traceability we need to provide.
To be honest it doesn't even matter the barcode stickers match the exact items a given customer has purchased, as long as the total worth matches; therefore the only use I could see in relating tblOrder to tblCustomerPrescriptions (be it via a junction table or not), is in the case it might help with the subtract routine.
The database is for internal use only, no information about it and the customers' actions is supposed to ever be used in other contexts. We put barcode stickers for the items purchased by customers on the prescriptions that we then return to the health system to be accredited the money - that's all the traceability we need to provide.
To be honest it doesn't even matter the barcode stickers match the exact items a given customer has purchased, as long as the total worth matches; therefore the only use I could see in relating tblOrder to tblCustomerPrescriptions (be it via a junction table or not), is in the case it might help with the subtract routine.
If traceability is not a requirement, then you will need a query or queries to determine the total of already placed orders and the total of prescription amounts within the validity dates of each prescription, and the difference thereof.
Jzwp22,
let's say we got the customers from 1 or more regions using the form and code you provided, is there a way to add a button to that form that lets the user enter order details on another form or subform?
Like let's say user filters 3 names with their phone numbers, then calls them and one of them places an order. So user would ideally click on his record then on a 'Add Order for selected customer' button.
let's say we got the customers from 1 or more regions using the form and code you provided, is there a way to add a button to that form that lets the user enter order details on another form or subform?
Like let's say user filters 3 names with their phone numbers, then calls them and one of them places an order. So user would ideally click on his record then on a 'Add Order for selected customer' button.
Definitely possible and commonly done. You would just need to pass the primary key value of the selected customer from the current form to the new form. An alternative is to hide an order subform in your current form and just make it visible when the button is clicked. There are multiple ways of doing it, so it comes down to your preference.
That of the subform would be very neat.
Also, as you told me in your earlier posts, I better avoid lookup fields in my tables, but I can't find a valid alternative so that entering order details is not a pain and doesn't require user to type productsID's rather than comfortably selecting them from a dropdown
Also, as you told me in your earlier posts, I better avoid lookup fields in my tables, but I can't find a valid alternative so that entering order details is not a pain and doesn't require user to type productsID's rather than comfortably selecting them from a dropdown
I am unable to get the after update event to work properly, I get an error message after the update and the field is filled with "#Name?".
I'm afraid there might be multiple causes to that, I checked that the column in the combo box corresponds to the row where the value is stored (in my case the 3rd, so I put a 2 among the parentheses).
After several hours, though, my shift is over and i'll see to that tomorrow. Once again thank you, I'd be using an abacus if it wasn't for you.
I'm afraid there might be multiple causes to that, I checked that the column in the combo box corresponds to the row where the value is stored (in my case the 3rd, so I put a 2 among the parentheses).
After several hours, though, my shift is over and i'll see to that tomorrow. Once again thank you, I'd be using an abacus if it wasn't for you.
I'm so upset I'm tempted to ask you if you could check out what's wrong with my db.. I just can't wrap my mind about it. I checked control names, column sources, I don't know what stops the code from putting that value in the proper field.
Thanks for providing the 2000 version; I only have Access 2003 here at work (2010 at home).
The main thing I noticed is that you have the quantity field in the order_products table as text, it shoud be a number (long number if you do not expect fractional quantitites, otherwise single or double precision). After I changed the data type of the quantity field to a number, I deleted the subform and rebuilt it. It is working in the attached database.
The main thing I noticed is that you have the quantity field in the order_products table as text, it shoud be a number (long number if you do not expect fractional quantitites, otherwise single or double precision). After I changed the data type of the quantity field to a number, I deleted the subform and rebuilt it. It is working in the attached database.
