I cant work out why this:

Total: [Sum]=([Applications]![Price])

Isn't working, it either prompts me to enter a value for sum or just plain doesn't work.

What I'm basically trying to do is add up a load of prices from different tables. Something like:

Sum([Applications]![Price]+[Cases]![Price]+[CPU]![Price]+[GPU]![Price]+[HDD]![Price]+[Keyboards]![Price]+[Memory]![Price]+[Mice]![Price]+[Monitors]![Price]+[Motherboards]![Price]+[Networking]![Price]+[OS]![Price]+[Prebuilt_Computer]![Price]+[Printers]![Price]+[PSU]![Price]+[Removeable Media]![Price]+[Removeable Storage]![Price]+[Security]![Price]+[Sound Cards]![Price]+[Speakers]![Price])

I got it to work once, but as soon as I changed one of the prices, it stopped working, and now I cant get it to work again.

I've looked about for an answer, and see most problems being solved by simply entering this in a 'Total' section, but I dont have one of these :|

All I have is:
Field, Table, Sort, Show, Criteria, Or

Can anyone help?

if you right click on the field you can show totals

1. Your database design is not normalized and therefore will cause you great pain when trying to do things like this.

2. You can't just set a control source to sum values in different tables. You would need to have the form's RECORDSOURCE be a query that pulls the prices from the tables and then you can sum them.

3. In this case it would look like you would need a gigantic UNION query to pull all of the prices from each table. I don't envy you a bit. If this was designed right it would not be a problem at all because you would have ONE field in a single table that would have that information.

http://support.microsoft.com/kb/283878

1. Your database design is not normalized and therefore will cause you great pain when trying to do things like this.

2. You can't just set a control source to sum values in different tables. You would need to have the form's RECORDSOURCE be a query that pulls the prices from the tables and then you can sum them.

3. In this case it would look like you would need a gigantic UNION query to pull all of the prices from each table. I don't envy you a bit. If this was designed right it would not be a problem at all because you would have ONE field in a single table that would have that information.

http://support.microsoft.com/kb/283878

Eeep, I've looked at that link and am not sure how I could normalize my data, I've got different types of field in each menu apart from the price. I suppose then I've got to take the price, manufacturer and model out of each of the main tables and put them into their own tables, then relate them to the information in the main tables?

If you can post your db or your table structure, I think we can make some alternative suggestions as to how to normalize it.

Presenting the most horrific database you ever saw :D

whoa..... it says relationships..... but i dont see any.... you need some major work. first of all. how are you gonna knw what ID came from what table? you should name them accordingly!! thats just a start thouhg.. you need to majorly rethink this design

Aside from re-naming the ID's, any other ideas? I just don't know how I'm going to solve this.:confused:

take a look at the fields you have in the tables. i noticed (quick glance) that you have many of the same fields in many of the tables, one way to start normalizing would be to combine them into a single table and use some sort of component_ID to label it as a mouse, monitor, speaker etc..

How about I keep all the ID fields named as ID, but change the autonumber to text, so it would be, for example, Mouse01, Mouse02, Monitor01 etc.

And then relate them to a manufacture table, a model table, and a price table?

Or am I still thinking in the wrong direction?

its a good idea to keep the ID fields of your tables as autonumber and meaningless. you can come up with some other field in the table to add those names to.

did you read the article that boB posted? i think you should

Yea, but I cant see how I'm able to make the database into first normal form, I don't have any repeating fields inside any one table, but instead split into the relevant tables.

So,

"Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2. "

Assuming that my database is in first normal form ( :s ) then I should perhaps follow into 2NF of:

• Create separate tables for sets of values that apply to multiple records.
• Relate these tables with a foreign key.

Which I should do with the Autonumber ID right?

I am in the process of creating a sample for you. It will take me a little while but you should get the idea. The main thing is that each equipment piece has attributes. So, we can change it so that there is an attributes table and a junction table to store the link between an attribute and an item (along with the attribute value).

Wow thanks :D

Okay, here's the sample. It's kind of simplified but essentially you should be able to see what I'm doing and what I was talking about. You can add all the attributes you could ever need and select the applicable ones for each item.

Let me know if you have questions.

Thanks Bob :D

I'm working on it now.

For multiple attributes, should I have fields like Attribute1 Attribute2 or should I make an entirely new table for storing groups of multiple attributes?

Did you see that each item you can add multiple attributes. You just select the attribute from the list and the subform (in datasheet view) will let you add as many as there are.

Ah yea, I see.

Have a few more questions though

In what table should I put a yes/no field to show if the user has ordered the item or should this be a new table? CustomerOrderID [Autonumber], ItemOrdered [FK - ItemID], Price of item [Taken from ItemID] ?

What would be the easiest way of tying this together in a form?

Database now looks like this.

http://img204.imageshack.us/img204/8046/releationships2lj2.png

Need help regarding post above.

Think I've sorted it.

Think I've sorted it.

Glad to hear :)