Design (1 Viewer)

ewayne99

New member
Local time
Today, 18:20
Joined
Sep 30, 2021
Messages
8
I designed an access db that collects data on our archery match - 200 archers. I have a shooters table to account for the individual scores from 5 different individual events. My reports are filtered to display descending scores for each event. Then I have a report to show combined scores for all 5 events with descending scores... (highest score best). We also have team events. Each team is made up of 4 shooters. My team table is linked to my shooter table and tells me the scores from each team event there are two team events - but... and here is my problem. I need the best team report to show the sum of each of the shooters individual events plus the team events to give me a sorted Best Team report sorted highest to lowest. I have tinkered with this with calculated fields but the calculated fields in the report will not sort at all.

So I'm thinking my base design is wrong.

I know this is a lot to digest, but any advise would be appreciated.

Thanks, Wayne
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:20
Joined
Feb 28, 2001
Messages
27,001
To know whether your design is wrong, we would need to see your table layout for starters.
 

Ranman256

Well-known member
Local time
Today, 19:20
Joined
Apr 9, 2015
Messages
4,339
along with tShooters, & tTeams tables, you need tTeamRoster , and tEvents table:
*=key

tEvents tbl:
EventName*
TeamShooterID
Score


tTeamRoster:
TeamShooterID (auto)
TeamName*
ShooterID*


tTeams tbl:
TeamName*

tShooter table:
ShooterID*
ShooterName

a query is used to sum the totals of the tEvents by TeamShooterID
make any additional calculation in the query, (not the report)
 

ewayne99

New member
Local time
Today, 18:20
Joined
Sep 30, 2021
Messages
8
along with tShooters, & tTeams tables, you need tTeamRoster , and tEvents table:
*=key

tEvents tbl:
EventName*
TeamShooterID
Score


tTeamRoster:
TeamShooterID (auto)
TeamName*
ShooterID*


tTeams tbl:
TeamName*

tShooter table:
ShooterID*
ShooterName

a query is used to sum the totals of the tEvents by TeamShooterID
make any additional calculation in the query, (not the report)
Thanks Ranman256 I will give this a try. I was way off the mark having only a shooters table and a teams table.
 

ewayne99

New member
Local time
Today, 18:20
Joined
Sep 30, 2021
Messages
8
Thanks Ranman256 I will give this a try. I was way off the mark having only a shooters table and a teams table.
Ranman256... silly question ... what are the relationships in the 4 tables above.
 

ewayne99

New member
Local time
Today, 18:20
Joined
Sep 30, 2021
Messages
8
Thanks Ranman256 I will give this a try. I was way off the mark having only a shooters table and a teams table.
Ranman256... silly question ... what are the relationships in the 4 tables above.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:20
Joined
Feb 19, 2002
Messages
42,978
Please use some consistency when naming objects. You will have to think a lot less as you are coding later if you are disciplined when you define object names.

Although it is not wrong to use text names as primary keys. It does lead to the problem of how to handle updates that will affect multiple tables should a team decide they want to change their name. Best to just use autonumbers as the PK and FK and leave the names as data. Just define unique indexes so you can ensure that TeamName, EventName, and ShooterName are all unique.

If you think you might want to print certificates or send letters, etc, it is best to not mush all the parts of a name into a single field. At a minimum use FirstName and LastName. Also keep in mind that the combination of First and Last names will probably not be unique in a field of any size so decide how you will handle that. Will you keep street address and include that in the unique index also? or birth date?
 

Isaac

Lifelong Learner
Local time
Today, 16:20
Joined
Mar 14, 2017
Messages
8,738
Although it is not wrong to use text names as primary keys. It does lead to the problem of how to handle updates that will affect multiple tables should a team decide they want to change their name
I'll second what Pat said, but I'd go further: It is wrong to use those names as primary keys...Period.
 
Last edited:

slharman1

Member
Local time
Today, 18:20
Joined
Mar 8, 2021
Messages
467
I'll second what Pat said, but I'd go further: It is wrong to use those names as primary keys. Use autonumber type of keys with NO business meaning of any kind.
For the orders table I use OrderID; customer table, CustomerID; products table, ProductID. All are auto number and have no other value than to count the records. Is that wrong?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:20
Joined
Sep 12, 2006
Messages
15,614
Yes and no. They do have a value other than counting records.

The order table will have a field called OrderID
It will also have a field called CustomerID.

The CustomerID field in the Orders table relates Customers to Orders, so that 1 Customer ---- n Orders. A single customer can be linked to many orders, but an order can only belong to a single customer.

You also need an OrderLInes table, linked to the Orders Table. You also link the OrderLines table to the Products table.

You can't ever have a many-to-many link. You have to split a many-to many into two 1-to-many links.

The art/science is analysing your data to construct a series of tables that logically links together, and represents the data you are trying to model, so that no single piece of data gets duplicated, and that you also do not have repeating groups of data (which implies you need another subtable). That way you get an efficient database that's easy to develop and use, and easy to maintain.

Using numbers for these linking fields is better than text, because it's more efficient for for the database.

When you get things like the US state table, it becomes a bit moot as to whether you want to use a number just for the sake of it, or stick with the two character state postal code. I suspect they use TX for Texas, rather than a number, but I'm not sure.
 

Users who are viewing this thread

Top Bottom