Trying to total amount from auto populated combo box

[johnomaz]

Ok, I'm trying to make a database for my wife for our small home business. I have drop down boxes that have items we sell. When you select one, it will auto fill another field with the cost of item we pay. If a customer buys 5 items, 5 fields will auto populate. I want to be able to get the total of those auto populated fields at the bottom so we can see the difference between our cost and what we are charging. The image attached is what I'm talking about. The "Our Cost" column cannot be typed into. I want a total at the bottom of that column. I am trying to use =[OurCost 1]+[OurCost 2]+[OurCost 3]+[OurCost 4]+[OurCost 5]+[OurCost 6]+[OurCost 7]+[OurCost 8]+[OurCost 9]+[OurCost 10] but what it displays is: 4.994.994.994.994.994.99 AND all 10 entries must have a value. I want it to skip empty fields and calculate the total. My table for our costs does have it set to currency.

Here is a link to a screenshot of the results: https://dl.dropbox.com/u/114466/example.PNG



Also, same in the Customer Price column. Is there a way I can have it calculate if there isn't a 0.00 in the field? If not, whatever. We can live with that. I just set it to default value = 0

[pbaldy]

For starters, your design isn't normalized. The normal setup for invoices is 2 tables: one has the header info (invoice number, customer, date, etc) and the other is a detail table with the products (invoice number, product, quantity, price). They are related one-to-many on the invoice number field. If an invoice has 3 products on it, there would be 3 records in that detail table. That lets you have 1 product or 1000 on an invoice.

To your issue, the fact that the cost has populated all the way down is likely due to it being unbound, which might also explain the result (it's being treated as text rather than currency).

[johnomaz]

Ok, I see what you mean. I think I need to start over then. I have VERY little Access experience. I work in an IT department, but databases aren't really part of my job. I'm doing this as an informative tool for my and my wife's eyes only. She's having issues staying consistent with pricing and we're having issues actually turning a profit because she isn't pricing stuff correctly. I want her (and myself) to see the actual cost to us compared to what she is charging the person.

I do however see what you mean with the invoice number being related to the two tables so it pulls the info from both tables when it is entered. I also found the Add Existing Field button which in turn brings up the relation window. The last time I made a database, we were using Access 2003 and things were very different. I shall restart (thankfully I only have an hour or two in this one) and see where that goes.

[pbaldy]

Here's one of a gazillion links on the topic:

http://www.deeptraining.com/litwin/d...asedesign.aspx