Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-09-2019, 07:04 PM   #1
calvinle
Newly Registered User
 
Join Date: Sep 2014
Posts: 317
Thanks: 26
Thanked 4 Times in 4 Posts
calvinle is on a distinguished road
Best way to design table for (gender & province)

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

calvinle is offline   Reply With Quote
Old 09-09-2019, 08:09 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Best way to design table for (gender & province)

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-09-2019, 08:11 PM   #3
calvinle
Newly Registered User
 
Join Date: Sep 2014
Posts: 317
Thanks: 26
Thanked 4 Times in 4 Posts
calvinle is on a distinguished road
Re: Best way to design table for (gender & province)

The number of male and female per province.
Province is already predetermined.

Thanks

calvinle is offline   Reply With Quote
Old 09-09-2019, 09:48 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,104
Thanks: 110
Thanked 2,746 Times in 2,505 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to design table for (gender & province)

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-09-2019, 10:39 PM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Best way to design table for (gender & province)

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
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 09-10-2019, 07:52 AM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Best way to design table for (gender & province)

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-10-2019, 12:34 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,104
Thanks: 110
Thanked 2,746 Times in 2,505 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to design table for (gender & province)

Quote:
Originally Posted by Pat Hartman View Post
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.

Quote:
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?????
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-10-2019, 01:58 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Best way to design table for (gender & province)

Quote:
Originally Posted by isladogs View Post
Hmm...oh really?????
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?
Mark_ is offline   Reply With Quote
Old 09-11-2019, 01:38 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Best way to design table for (gender & province)

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-11-2019, 01:48 PM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,104
Thanks: 110
Thanked 2,746 Times in 2,505 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to design table for (gender & province)

Quote:
Originally Posted by Pat Hartman View Post
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 ...
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-11-2019, 02:44 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Best way to design table for (gender & province)

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-11-2019, 11:29 PM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,104
Thanks: 110
Thanked 2,746 Times in 2,505 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to design table for (gender & province)

@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...u-should-learn
Attached Images
File Type: png Capture.PNG (61.9 KB, 41 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 09-12-2019 at 02:10 AM.
isladogs is offline   Reply With Quote
Old 09-12-2019, 08:14 AM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Best way to design table for (gender & province)

Colin,

But you forgot all about those Yiffing Furries! How could you?!?! So exclusionary....
Mark_ is offline   Reply With Quote
Old 09-12-2019, 11:19 AM   #14
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,104
Thanks: 110
Thanked 2,746 Times in 2,505 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best way to design table for (gender & province)

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 ...
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-12-2019, 11:30 AM   #15
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Best way to design table for (gender & province)

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....


Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Gender Neutral? Uncle Gizmo The Watercooler 6 08-08-2019 03:13 PM
Count by age range AND gender ncub Reports 1 07-08-2015 11:21 PM
Shall i create another table for gender in payrolls database? Harrold General 1 11-01-2011 07:15 AM
Matching title and gender 1961templar Tables 113 11-27-2009 10:09 AM
Countries,state/province,region patrickd123 General 1 03-03-2007 07:46 PM




All times are GMT -8. The time now is 08:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World