Best way to design table for (gender & province) (1 Viewer)

calvinle

Registered User.
Local time
Today, 07:47
Joined
Sep 26, 2014
Messages
332
Hi,

I need to reproduce a table as per below and I would like some feedback on how to design such table?

Province of Employment | Participating employees | Male | Female
BC
AB
SK
MB
ON
QC
NB
NS
NL
PE
Federal

I'm thinking 2 tables:
The first one with 12 columns, one for each province + PK id.
The second table with 4 columns (male, female, PK id, and province_id which is related to the first table).

or my second thought will be:
Only 1 table with 23 columns (11 province x 2 genders, and PK id).
So basicaly, BC_male, BC_female, etc..

Any issue doing 2nd option?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:47
Joined
Oct 29, 2018
Messages
21,358
Hi. What kind of information are you trying to store in your table? Are you trying to count the number of employees in each category?
 

calvinle

Registered User.
Local time
Today, 07:47
Joined
Sep 26, 2014
Messages
332
The number of male and female per province.
Province is already predetermined.

Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
This is a spreadsheet layout. Why not do it in Excel?

If you want to do it in Access, then change the layout so the table is normalised.

Assuming you are recording names then have one table with fields
EmpID -autonumber PK
LastName
FirstName
Gender
Province

If you are just recording employee numbers then one table with fields
ProvinceID - autonumber PK
Province
TotalEmployees - number
Gender

All fields text unless otherwise stated

Either way, you would run a crosstab query to get a spreadsheet like layout
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:47
Joined
Jul 9, 2003
Messages
16,244
A spreadsheet layout in MS Access is fine if you just want to look at it. However, if you want to do anything with it like extract useful information, count things, anything slightly complicated, then, as others have said you need to to look at normalisation.

I have blogged about the issue of bringing spreadsheets into MS Access see this link here:-

https://www.niftyaccess.com/excel-in-access/

Where I go through the process in video and text.

There's also a free tool for converting spreadsheet like data into a format suitable for MS Access.

Sent from my Pixel 3a using Tapatalk
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
If you are just recording employee numbers then one table with fields
ProvinceID - autonumber PK
Province
TotalEmployees - number
Gender
Not quite right. Technically, you need three tables. One to define provinces:
tblProvince
ProvinceID (autonumber PK)
Province

A second to define Gender values. You can limit this to Male and Female but the Woke are going to lobby for it to be 57 ( or 27,000) values.
tblGender
GenderID (autonumber PK)
Gender

The third is the junction table (not recommended) OR a query of the first table that Colin described
tblProvinceGenderTotals
ProvinceID (PK field1)
GenderID (PK field2)
PersonCount

It is rarely recommended to create summary tables. However, they are common in data warehouses. But since data warehouses are rarely created using Access, your best option is a query that summarizes the detail employement records on the fly.
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
Not quite right. Technically, you need three tables. One to define provinces:
tblProvince
ProvinceID (autonumber PK)
Province

The OP stated that Province was 'predetermined'. I assumed that meant either that a Province table already existed or that the province code was all that was required.

A second to define Gender values. You can limit this to Male and Female but the Woke are going to lobby for it to be 57 ( or 27,000) values.
tblGender
GenderID (autonumber PK)
Gender
Hmm...oh really????? :rolleyes:
I have NEVER created a tblGender.
Normally I use a value list in form comboboxes to include M, F and other transgender/intersex values where required. If it ever gets to a large number being needed then a table would be a good idea but at the moment I think its superfluous
 

Mark_

Longboard on the internet
Local time
Today, 07:47
Joined
Sep 12, 2017
Messages
2,111
Hmm...oh really????? :rolleyes:
I have NEVER created a tblGender.
Normally I use a value list in form comboboxes to include M, F and other transgender/intersex values where required. If it ever gets to a large number being needed then a table would be a good idea but at the moment I think its superfluous

How gender biased of you! I figured you'd not post something like that lest the police find out and incarcerate you for posts hurting others feelings!

@OP, something that may be relevant to your structure, are there cases where you know someone is employed but don't have gender information on them?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
If you are just recording employee numbers then one table with fields
ProvinceID - autonumber PK
Province
TotalEmployees - number
Gender
If ProvinceID is the autonumber for a Province then you can have only a single gender value per province. A more appropriate name for the PK would be ProvinceGenderID since the ID is the junction of two attributes.

I also don't make tables for Gender because in my world there are only three options, but we live in strange times.
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
If ProvinceID is the autonumber for a Province then you can have only a single gender value per province.

Definitely not true.
Its name may have been unintentionally misleading but the autonumber ID field will not prevent you having two (or more!!) records for each province for male/female ...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
Its name may have been unintentionally misleading but the autonumber ID field will not prevent you having two (or more!!) records for each province for male/female ...
I disagree. If ProvinceID is the ID for a Provence, then you can have only ONE gender count for the province. if this is just a naming issue and ProvinceID is not really the Province PK, then you can have duplicate records unless you have also created a unique index based on the Province and Gender fields.
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
@Pat
Not sure this exchange is helping the OP but one of us may be missing the point here....
Screenshot shows table design and example data.



In view of the previous comments I deliberately included some non binary gender values. If it was a real database I would try to shorten to one or two letters e.g. N, Q, FB

I particularly liked the term QUILTBAG
For anyone else who, like me, didn't know any of those, see https://www.mic.com/articles/52001/9-gender-and-sexuality-acronyms-you-should-learn
 

Attachments

  • Capture.PNG
    Capture.PNG
    61.9 KB · Views: 247
Last edited:

Mark_

Longboard on the internet
Local time
Today, 07:47
Joined
Sep 12, 2017
Messages
2,111
Colin,

But you forgot all about those Yiffing Furries! How could you?!?! So exclusionary....
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
Mark
I do apologise for excluding you from my example ;)
If we include sexual preferences as well, the list would be endless.

Anyway, I've learned something new that I hope I can quickly forget ...
 

Mark_

Longboard on the internet
Local time
Today, 07:47
Joined
Sep 12, 2017
Messages
2,111
Colin,

Not my thing, but there are soo many people who claim to identify in strange ways its hard to keep up with. Have to make sure that this individual doesn't feel excluded, now don't we....

:p
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2002
Messages
42,970
OK, now that we've determined that ProvinceID doesn't have anything to do with Province
Please try to add
Province = BC
Employees = 3
Gender = M

Clearly my description was confusing but it doesn't make sense to have two instances for Province = BC and Gender = M which is why I said that if the PK isn't compound, a unique index on Province + Gender is required to enforce the business rules.
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
Mark
That's even worse...
I'm going to try NOT to click on any more links you provide on this topic. :eek:

Pat
I've no disagreement with that point about indexing. Obviously you don't want 'duplicate records' in any table

Calvinie
Are you still there or has this veered into realms you don't want to get involved with?
 

Users who are viewing this thread

Top Bottom