Calculating costs

Question

I am using Access 2021 Dutch (Netherlands) on a Windows 11 computer.

I have a database with a Family main form and a Children subform. Obiously each family can have more than one child in the database. Parents can order a Tshirt, which is registered (by me) in the Children subform. I figured out how to add up how many Tshirts were ordered for all the children in a family, and put that in the Family main form directly as "[Aantal T-shirts] * 11,95" (Aantal = number of...; I don't know what that is in English). Anyway, that works.

Now I want to add the option to order a picture book. Parents can order one for each of their kids or decide to order it for only one of their kids. I tried copying what I did for the Tshirts, but it isn't working. The Tshirt main field is a dropdown menu of sizes. So I have in the Children Subform a radio button (Yes/No) for Photo book and a non visible field for the Aantal Photobooks field. In the main form I put a field "[Aantal Photobooks] * 6". The result is: "#Name?". As far as I can figure out, that error is just to unclear / cryptic to help somebody like me fix it.

Other than not performing any of the financial calculations in a Query, and putting the results of said Query in my form(s), what am I missing? I am not that good at Access. I created this database more than 2 decades ago, with even less knowledge of Access than I have now. It has evolved and gotten more complicated over the years (but it mostly works). Converting the entire set of forms to be populated by a Query is a daunting task, I hesitate to undertake especially with the "if it ain't broke, don't fix it" principle in the back of my mind. I know, somebody will tell me: "But David, it IS broke!"

On the family form I also have three different discounts that are possible. One of them is a "Stafmember discount" of €10 per child in the family of a stafmember. I used to have a Yes/No box next to that field called "Stafmember". If it was checked (by me) then the discount was automatically calculated and put in the field "Stafdiscount" and later added to the total of Discounts. Last year that suddenly stopped working. How should I approach this?

David

Answer 1

Tom,

Well you have GOT to be Dutch, but I will continue this string in English!

When I set up this database, I did study the Northwind files, which is how I learned how to do most of what I have done so far. Just to be clear, I am not really in the ordering product business, I run the administration for a Summer Camp and mostly we "sell" Children's Camp and Teen camp, but in addition we started offering Tshirts and this year a Photobook (hats are actually not a bad idea).

Family info is in one Table. Camper data (Children) is in another. They are properly linked so the whole thing works. There is also a table for the date of camp (to calculate age in camp from DOB), and a table containing the name of the camp (Kinderkamp / Tienerkamp / None), the price of each camp, and the amount of the multiple child discount (meerkindskorting).

I populate the form (formulier) from the Tables.

I enter the amount for Registration fee from our Internet banking (more recently Tikkies) manually. As said the multiple child discount is calculated and entered autmatically. I used to be able to check a box for the Staf discount, but that stopped working, but the amount was filled in by entering the calculation in the field of the form. Can't figure out why it stopped working, but there are only about 5 stafmembers who have kids in camp, and I'd have to manually check their box, so why not just fill in the amount myself?

For the Tshirst there is a dropdown menu in the Child form, that allows me to enter the size of the Tshirt from the Google registration form. The field "Aantal Tshirtmaat" calculates how many Tshirts have been ordered by the family. In the family form that field is multiplied by 11,95 and shown (and added to the total Camp cost). The only place where I have to edit the price when it changes is in that one field. Sure, I could go get that price from the tabel where the other prices are located, but I would still have to change it there, so what's the difference.

All of this works to the extent that I think "it ain't broke".

What does not work is this year's addition of a Photobook. I have a checkbox (radiobutton / yes/no) in the child form (and table) called "Fotoboek". I have the same not visible field [Aantal Fotoboek] like I do for Tshirts, but it doesn't work. Since I don't actually understand the "Aantal [field]" command (insufficient understanding of Access, I can't make it work. I have a cel in the table for Family and a field in the Family form that is supposed to multiply Aantal Fotoboek * 6,5, but it gives me #Name? I don't know what that means, so I can't fix it. I will gladly put all this in a seperate table, where eventually I could also put a hat and include the Tshirts, but I don't know how to get the number of Photobooks ordered into the table or the form. Here again, my way of doing it would only require me to change the price of the Photobook in one place. Obviously, parents with 3 Children's camp participants may order only one book (so for one child), parents with kids in Teen and Children's camp may order one for a Teen and another for a Child (different books) and any other combination. 2 kids in teencamp and 2 in Children's camp may result in ordering 2 books, 1 book or 4 books. I need that added up correctly.

Once I get the data in a table (or query, which I know is just a table) I can get them into the form just fine.

In the end, I need to be able to do simple adding / subtracting with all these financial numbers. Adding up costs and discounts, and subtracting the total of discounts from the total of costs and the total of what they have paid (registration fee and final amount). Some of that is being done in a Query, a few in the form. That is an easy fix (I think).

My database has several forms, tables (as mentioned, and another one for Stafmembers) and many, many querries to mailmerge with Word, make lists or reports (like how many of each Tshirt to order and another one to distribute the Tshirts during camp). Also a few "bijwerk querries" to change data based on specific criteria. Over the years it has become quite complicated (for me), but most of all, it all works; so it ain't broke (in my opinion) :-).

I would like to know how to get the photobook orders for indivual campers added up per family.

I would like to know how I can fill the Staff discount cell (in the table) and thus the corresponding field in the form, based on the checkmark in the Family form.

When children "grow up" the AVG (GRDP) and our Privacy statement requires me to delete them, since they are no longer of an age where they are campers. When they do have children of their own and send them to camp (which is happening now), I have to enter them as a new family. That makes sense (at least to me) since the only thing that stays the same is their first name and for men: their last name. All the other data for a family record changes (address, phone numbers, children's names) etc. So aside from being required to remove them when they "age out", I have to enter them as a new record anyway. That's because they are not customers, but parents of children. They are also not ordering products, but sending children to our camps.

David

Answer 2

Scott,

I am afraid you are assuming too much understanding of Access on my part. :-(

So here goes:

As far as the Tshirt orders, this is working just fine, so since it ain't broke, I would prefer not to fix it.

As far as the PhotoBook.

There is no order number, nor date.

The keyname is what links the Family Table to the Children Table, so that would be the family name?

You mention the Order Detail Table and suddenly it has an Order ID as a foreign ID? Not clue what that means. The Child ID would in my case be the Child's first name (since keyname is the key and last name can be different from the parent's last name (stepchild, fosterchild, and such). You also indicate that the table should contain Quantity (of Photobooks orderen), but that is precisely the number I cannot get from the form, when I check the box "Photobook".

As said: I just barely understand Access, and what I do understand I learned in Dutch, but there is no adequate NL MSAnswers site for Access. For my questions, they refer me here. Since I am an American, English is not a problem, other than Access / Querry language, which I do all in Dutch. So go easy on me!?

I should probably have explained that the discount has nothing to do with the Tshirt or Photo Book orders. This database is for camp registrations. So the form records how many kids from each family go to which camp, calculates the Family's Campcost, calculates a multiple child discount. Some parents are on staff and get a €10 per child discount. A registration fee is paid for each child and a few weeks before camp the parents pay the balance in full (after a mailmerged letter from me) All of this is calculated on the form to the final balance owed.

David

Answer 3

Great effort David, but this is not how to do it. It IS indeed broken.

If the price of a T-shirt ever changes, are you really going to change all 11,95 to say 10,95? I don't think so. That is a nightmare. Also, if there ever is another product, say a Hat, are you really going to add a [Aantal Hoeden] field whereever it is needed, and add the calculation with its price. No, no, no, that is not the relational way.

The reason your efforts break down is not just that you don't know topic A or B of Access programming, but you have the wrong approach. Set it aside and start anew. Painful, but necessary.

Download *AND STUDY* the Northwind 2.0 Templates found at https://support.microsoft.com/en-us/office/featured-access-templates-e14f25e4-78b6-41de-8278-1afcfc91a9cb Watch the videos. Study the documentation. Watch follow-up videos on YouTube. Then decide you can use this as a starting point for your project. What Northwind calls a Company is your Family. Northwind has Products such as your T-shirt, Picture Book, and Hat. The quantity of the order detail can be used to say how many need to be ordered. Not everything is solved (if you study Northwind you will read and hear several times it is a showcase, not designed to run any specific business), but that's what you can address with pointed questions in future posts, such as how to specify an optional Attribute (such as Size) of a T-shirt, or how best to store the family children, and what should happen if those children grow up, have children of their own, and want to order products from you.

Answer 4

Orders should be kept in a separate table., actually 2 tables. The Orders table should have the Order no, date and the Family placing the order. The Orders detail tables should have the OrderID as a foreign key, the ChildID, ItemID, quantity and Unit Price . You can record the discount on either the Details level or for the whole order.

Order totals should be calculated, not stored.