Type of relationship in Access and setting primary keys...

[njnewuser]

Hi,


I have not used Access in years but here is my problem.
I have an Excel spreadsheet with the following headings:

Class

Date

Last Name

First Name

Employer

Public or Private?

Job Title

Work City

County

Employer could be a private firm, a government agency (and there are different types, etc)

The classes represent 5 different, unique training classes that are given on multiple dates over the course of a year (since 2011).

After looking at the book The Excel Analysts Guide to Access, I know that this would work far better in Access (I'm not even that great in Excel). The funder may want to know different things from us, like how many public agencies and of what type took which class from which county, etc etc.

A person could take multiple classes.

We are an outside training agency and when we give the report back to the funder, they want it looking like Excel.

I've imported the Excel data into access and want to make sure I have the keys right. Is this a one to many relationship (if I consider each person or employer as "one" in relation to # of classes).

And am I correct in letting Access set the primary key?

I'm approaching 1,700 rows of data in Excel and it is getting cumbersome, which is why I would like to relearn Access for this project.
Thanks for any assistance,

Ally

[June7]

Access is appropriate application.

Using autonumber field as primary key is probably easiest but in no way the only approach.

I expect each person/employer can be associated with more than one class and each class can be associated with more than one person/employer. This is a many-to-many relationship. Requires a junction table.

[njnewuser]

You are right - maybe someday the funder would want to know how many or which employees the Township of X sent to all of our classes, for example?
I would kill myself if I had to do that in Excel....

So now to look up junction tables....

[June7]

It wouldn't be THAT bad with Excel. Access has its challenges as well, mostly with getting the data structure right. After that, should be all fun and games! LOL

[recyan]

@njnewuser,
Are you indicating ?
1) There are various Unique "Classes".
2) Each "Class" might be conducted multiple times on various "Dates".
3) There are many unique "Employers".
4) Each "Employer" may have multiple "Employees".
5) There is a chance that an "Employee" might belong to more than One "Employer" over a period of time.
6) Each "Employee" can take multiple "Classes".
7) There is a chance that an "Employee" may take the same "Class" again over a period of time.

Thanks.

[njnewuser]

@njnewuser,
Are you indicating ?
1) There are various Unique "Classes".

Hi - yes, there are five of them..


2) Each "Class" might be conducted multiple times on various "Dates".

Yes - so let's say there is a "How to Win Grant Funds" class - it might be offered 5 times over the course of a year.


3) There are many unique "Employers".

Yes - mostly private firms, individuals who are self employed, and different layers of government such as small towns, counties, commissions, and state government.

4) Each "Employer" may have multiple "Employees".

Yes - right now I have not been asked to provide or track that info, but I figure I might as well set this up for future needs.

5) There is a chance that an "Employee" might belong to more than One "Employer" over a period of time.

Yes, but right now we aren't tracking that (but again, you never know).

6) Each "Employee" can take multiple "Classes".

Yes.

7) There is a chance that an "Employee" may take the same "Class" again over a period of time.

Yes, but we have only been noting the most recent class that they took and ignoring the older ones. It happens rarely.

Thanks.

Thanks for any further insight you can give!

[njnewuser]

This is an example of what I have started doing so far. There are some concatenated fields in the Excel sheet, so I think one showed up here as ClassDate. Would I keep that Excel field on import or delete it?

[June7]

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Don't have a [First Name Last Name] field unless it is a Calculated type.

ClassDate is just a date value?

A person is typed as public or private? How many records are in Table7? If there are only two choices, Table7 seems unnecessary.

[njnewuser]

Hi,
ClassDate is Name of Class : Date it ran (the concatenated field from Excel) . There are 57 records in that table.

An employer is typed as public or private (or nonprofit) - meaning private firm or government entity. It is a column in the Data table.

[June7]

You have the PublicOrPrivate table linked to People, not employer.

My preference would be to save the descriptor value (public or private), not ID.

What do you mean the date is concatenated from Excel? The date parts are in separate cells in Excel?

[recyan]

@njNewUser,
At a basic level, this is the way I see it.

Thanks

[njnewuser]

Yes - in Excel - the dates are in one column, the lists of classes are in another, and then I combined or joined this in Excel...

[June7]

Why join date with class into a single field? These are two distinct values and should probably remain in separate fields.

[njnewuser]

Hi: I am trying to change things so that EmployerCategory (nonprofit, public, private, or left blank) is joined with the Employer table and not class schedule as was pointed out to me. When I do this and make it a one to many relationship I get this message: "Relationship must be on the same number of fields with the same data types." Both of the ID fields are autonumbers so I don't understand. I also get this:

What am I doing wrong?

[June7]

Why are you trying to link CategoryID and EmpID?

CategoryID and EmpID are not same data. Neither should save the value of the other.