Adding up different fiields

[Derek H]

I have an issue that i cant work out how to do it. I have a database set up for a motorcycle group, part of the data collected is about members bikes. While most members have only one motorcycle, there are members who have up to 7 bikes. The information that i need to extract is the bike models, we only have one make of bike but have 6 types of model. From time to time we have a meeting where we give out the total amount of bikes in the club and also a breakdown of how many of the different models we have.



I have set up the bikes as bike1, bike2,....bike7. I have then created a quiery for all 7 bikes and used the count function to give me a list of the 7 bikes and how many models of each we have. So we have a quiery:

Bike 1 qty bike 2 ............... etc
model a 50
model b 34
model c 33
model d 12
model e 15
model f 7

I have 7 of these quieries, one for each bike that one person could have. What i need to do is add up all the model a, and b etc. But i dont know how to add them up or if it is possible. Help would be appreciated, also have i done this the wrong way to begin with

[orange]

What does your table(s) look like?

While most members have only one motorcycle, there are members who have up to 7 bikes.

What other info do you want to retrieve/query from this database?
How do you relate bike to member?

It sounds like you want to Count not Add????

[pbaldy]

I have set up the bikes as bike1, bike2,....bike7.

have i done this the wrong way to begin with

Yes, you've done it the wrong way. You'd lay out a spreadsheet that way, not a relational database. The bikes should be in a related table with 1 record per bike owned. It would have fields for owner and model (and anything else related to each bike). If I owned 3 bikes, I'd have 3 records in that table. You'd typically display it with a form (owner)/subform (bikes). The concept is called normalization.

[Micron]

Or perhaps more normalized would be a table with only member info and a related table as tblMemberBikes?
Suggest you get some background in this and other subjects before going very far down the wrong paths. Orange has some good links too.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[davegri]

MCC-davegri-v01.zip
Have a look at this. It's a Many-to-Many setup with 3 tables.
tblMembers
tblBikes
tblMemberBikeLink

A form (based on tblMembers) and a subform (based on tblMemberBikeLink)
The subform has a combobox to enter the bike for the member on the main form.

2 queries to count bikes and members.

[Derek H]

What does your table(s) look like?



What other info do you want to retrieve/query from this database?
How do you relate bike to member?

It sounds like you want to Count not Add????

some queries we do are total number of members, by county, members by region, positions in club, type of membership, single, joint, family and so on, most of these i have sorted out, its just the adding of the bikes. At the moment i have created a form, which asks for name address phone email etc and then it goes into bike details, model, type ie solo, trike,combo, reg number. This is then repated another 6 times. Its because they are in different fileds i am having issues adding the same models up in the 7 bike fileds.

[pbaldy]

Post 6 was moderated, I'm posting to trigger email notifications.

[Derek H]

thanks for that, i think thats the way i need to go on this

[Derek H]

MCC-davegri-v01.zip
Have a look at this. It's a Many-to-Many setup with 3 tables.
tblMembers
tblBikes
tblMemberBikeLink

A form (based on tblMembers) and a subform (based on tblMemberBikeLink)
The subform has a combobox to enter the bike for the member on the main form.

2 queries to count bikes and members.

Yep looked at that and can see what i need to do now, thanks

[Derek H]

What does your table(s) look like?



What other info do you want to retrieve/query from this database?
How do you relate bike to member?

It sounds like you want to Count not Add????

We need total members and type ie single joint family, members by region, members by name, members by county, and so on. At the moment i have the bikes as a field one after the other on the same form i add details.