How

[Ports]

How to create a table with autopopulated information

Edit: Apologies for the generic title 'How' - looks like I pressed Submit before finishing writing the full title



Hi


I'm new to Access. I have been using it for a few months producing simple queries on data generated by the college's funding returns. Although I am new to Access/MSSQL/VBA, I don't mind getting under the hood (if need be) as I've got programming background.



I have two linked tables ("Learners" and "Courses" - 2 excel spreadsheets that are generated monthly). I will replace them on a monthly basis to include new students.


The "Learners" table contains learners details including their unique learner ID. The "Courses" table contains a learners ID and the details of corresponding courses they are undertaking.


What I'm trying to do is create a table that would (via a form) have the learners % progress / status on each of the courses.


The problem is that I'd need a unique identifier of each learner's course. How I usually do it (in queries) is to concatenate learner's id with their course, for example:


One learner would have the following records (learnerID-courseID):
001-C23
001-A12
001-A09
That means that learner with an id of 001 is doing 3 courses (C23, A12, A09). That way there's a unique identifier of each course each learner does.



So, I'm trying to create a table that would have updates about each particular course. Is there a way of creating a table that would automatically populate the first field with all the learnerID-courseID data from my Learners and Courses linked tables? I know how to do it in a query but the problem is that I want it to be followed with editable/updatable "Teacher", "Progress %", "Overall status" fields (That's why, my understanding is, it'd have to be a table, not a query)


My table would have the following fields:
UniqueID = automatically generated list of concatenated LearnerID and CourseID to be taken from my Learners and Courses tables (eg. 001-A12, 001-C23, 002-A12, 002-B32, 003-A12, 004-C11, etc)
Teacher - assigned/updated via a form
Progress - assigned/updated via a form
Status - assugned/updated via a form



I hope it makes sense. Please advise.

 

[Gasman]

I think you should have 3 tables.
Learners
Courses
LearnerCourses

The latter is a junction table that links Learners to Courses, as a Learner can have many Courses, and a Course can have many Learners?

HTH

 

[June7]

Edits can be done in queries, however, data entry/edit should be done via form, not directly with tables or queries.

No, table could not automatically populate its field. I don't see need to have these two values concatenated to single field.

 

[Ports]

Edits can be done in queries, however, data entry/edit should be done via form, not directly with tables or queries.

No, table could not automatically populate its field. I don't see need to have these two values concatenated to single field.

I would still need a third table with the "Progress", "Teacher", "Status" fields, as they are not part of the original linked tables, right? Now by what unique value would I make reference to the original tables? A course has multiple learners/cohorts, a learner has multiple courses.

 

[Ports]

I think you should have 3 tables.
Learners
Courses
LearnerCourses

The latter is a junction table that links Learners to Courses, as a Learner can have many Courses, and a Course can have many Learners?

HTH

Yes, a course have multiple learners and a learner multiple courses.


The Courses table that is given to me has (among others) the following fields:


LearnerID, CourseID, CourseStartDate, CourseEndDate, etc.
001,C12,...
001,B14,...
002,C12
002,A12



So as such, it's not a list of courses but the actual list of learners enrolled on courses.

 

[Gasman]

Personally I would be using the autonumber ID field to link all my tables.
However if you already have data to import you would need to tweak the data to allow for that.
Good reason to get the structure correct in the first place.

How much data are we talking about.? 1000 learners, 30 courses?
Creating the Learners and Courses table would be easy. Then a query/VBA to create the junction table.

Would a course have a different code if it was offered in two different years?, or is C12 always C12 which might be English Literature etc?

You really need to get a good structure to support how you work.

 

[June7]

So are you importing the Excel data into Access local table? No reason that table couldn't have additional fields for data entry.

 

[mike60smart]

Hi

My train of thought is similar to June7.

Can you get rid of the Excel spreadsheets altogether and just do Data Input of New Students directly into the database??

 

[Ports]

Personally I would be using the autonumber ID field to link all my tables.
However if you already have data to import you would need to tweak the data to allow for that.
Good reason to get the structure correct in the first place.

How much data are we talking about.? 1000 learners, 30 courses?
Creating the Learners and Courses table would be easy. Then a query/VBA to create the junction table.

Would a course have a different code if it was offered in two different years?, or is C12 always C12 which might be English Literature etc?

You really need to get a good structure to support how you work.

You're right. I really want to get the structure right before doing anything. Please bear with me guys.


How much data? I'd say around 500 learners across (500 records in the Learners table) 1200 course instances (1200 records in the Courses table). It's probably around 30-40 unique courses.



C12 would be a subject ID eg. English Literature Level 3, however, even within one year there would be a few cohorts of learners unertaking this course (another field). A combination of LearnerID and CourseID is enough to make it unique for each instance.

 

[Ports]

So are you importing the Excel data into Access local table? No reason that table couldn't have additional fields for data entry.

The background story is that: we have a parent organisation doing the data entry for us. On a monthly basis, they provide us with a set of fixed spreadsheets/reports.


For that reason, I don't want to do data entry again. The problem with the those reports is that they are box standard and there's no flexibility in amending them. For that reason, I want to import them into access, add some bits (eg. teacher allocation, status of the course, etc) and produce reports that we need.


The decision to link the spreadsheets (not import them as tables) is to easily replace the data on a monthly basis (by replacing the spreadsheets). If I were to import them into access as local tables (not linked spreadsheet). How could I conveniently update them on a montly basis when a new set of spreadsheets gets sent to us?

 

[June7]

Depends. I am guessing the spreadsheet data is not cumulative. Are there edits to records previously imported?

A primary key is not even needed in the junction table if there is not a dependent table. Use a compound index to prevent duplicate combinations of learner/course/year.

So the question is whether to use your learner and course identifiers as keys or to use autonumbers for linking Learners and Courses to the junction table. That is assuming you even have other info about learners (name, address, phone, etc) and courses (title, credits, etc).

 

[Ports]

Depends. I am guessing the spreadsheet data is not cumulative. Are there edits to records previously imported?

Not sure I understand this point. The monthly report data is the current state of things. I'd want to start with the latest import and build on that.

A primary key is not even needed in the junction table if there is not a dependent table. Use a compound index to prevent duplicate combinations of learner/course/year.

I'll research a compound index. Not sure what it means.

So the question is whether to use your learner and course identifiers as keys or to use autonumbers for linking Learners and Courses to the junction table. That is assuming you even have other info about learners (name, address, phone, etc) and courses (title, credits, etc).

Yes, the Learners table does have those details about learners.



I'm sorry I'm probably missing something or not expressing the situation clearly (or most probably both). I still cannot see how you could link a table where I'd have my form-updatable fields (teacher, course progress, etc) with any of the linked tables (Learners, Courses). I'm in the Database Tools => Relationships and there's nothing that would be common to draw a relationship.

 

[June7]

What does 'current state' mean? Does the Excel contain all records from beginning (cumulative) or you are given only new records? Say you already imported record for John Doe/Course A/2018 - would that record still be in next month spreadsheet?

 

[Ports]

What does 'current state' mean? Does the Excel contain all records from beginning (cumulative) or you are given only new records? Say you already imported record for John Doe/Course A/2018 - would that record still be in next month spreadsheet?

Yes, it is cumulative. Each month it is the current and full snapshot of the data. For example in September's Courses report it'd be:


September report



John Doe/Course A


October report:


John Doe/Course A
Joe Bloggs/Course B


November report:



John Doe/Course A
John Doe/Course B
Joe Bloggs/Course B


December report:


John Doe/Course A
John Doe/Course B
Joe Bloggs/Course B
Bill Smith/Course A




Then the Learners monthly report would be:


September:


John Doe/DOB/etc.


October:


John Doe/DOB/etc
Joe Bloggs/DOB/etc.


November:


John Doe/DOB/etc.
Joe Bloggs/DOB/etc


December:


John Doe/DOB/etc.
Joe Bloggs/DOB/etc
Bill Smith/DOB/etc.






Thank you.

 

[June7]

Both reports have LearnerID?

Is it possible that reports could have edits to some fields of records that were in previous report such as for John Doe/Course A ?

 

[Ports]

Both reports have LearnerID?

Is it possible that reports could have edits to some fields of records that were in previous report such as for John Doe/Course A ?

Yes, both have LearnerID


Yes, it's possible that some details will be amended (eg. updated contact details in the Learners table, or some course details fields in the Courses table.


What will remain always the same is the LearnerID in both tables and the CourseID in the Courses table


Since I'd like to add (and then update) some details about a particular course of particular learner, the concatenation of LearnerID-CourseID seems the only unique identifier in the existing data.




Last night I was playing with it (probably going in a wrong direction altoghether):
- created a query (based on the courses table) with just one field: a concatenation of LearnerID-CourseID calling it UniqueID (eg. 001-C07)
- I then created a 'make table' query from it
- The newly created table would have the following fields: UniqueID (the previously concatenated values). I also added the fields that I'd need to update via a form, eg. Teacher, Progress, etc

- When I tried to create a form, it (the form wizard) doesn't let me use sources from the linked tables and the table I created saying:
"You've chosen from record sources that the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

 

[June7]

I still don't see why you need the composite UniqueID. You added your custom fields to same table as imported data. Then you have the Learners table which needs to link to Courses table on the LearnerID fields.

Why would you need to include linked worksheets in form RecordSource since data was imported to table?

The monthly process needs to import new data as well as accommodate edits to existing records. This can be a tricky process with your new table that includes the additional fields. Review https://stackoverflow.com/questions/6199417/upserting-in-ms-access

An alternative is to keep your custom fields in a separate table. Then just keep links to worksheets. LearnerID and CourseID don't have to be combined into a single composite UniqueID field - links can be established with multiple fields.

 

[Ports]

I still don't see why you need the composite UniqueID. You added your custom fields to same table as imported data. Then you have the Learners table which needs to link to Courses table on the LearnerID fields.

Why would you need to include linked worksheets in form RecordSource since data was imported to table?

The monthly process needs to import new data as well as accommodate edits to existing records. This can be a tricky process with your new table that includes the additional fields.

An alternative is to keep your custom fields in a separate table. Then just keep links to worksheets. LearnerID and CourseID don't have to be combined into a single composite UniqueID field - links can be established with multiple fields.

Ok, now I see where I'm failing to communicate it. My custom fields ARE in a separate table. I don't want to amend (is it even possible) linked report data so I can just replace them (the linked excel report files) on a monthly basis.

 

[June7]

No, my bad, I just read your earlier post again and now I follow.

If you continue with linked Excel sheets and not import, then your local table can have LearnerID and CourseID fields and use compound linking in queries. Creating the composite UniqueID field is not required but it is an option.

No, cannot edit linked Excel dataset - can query, filter, sort, include in report but not edit.

 

[Ports]

No, my bad, I just read your earlier post again and now I follow.

If you continue with linked Excel sheets and not import, then your local table can have LearnerID and CourseID fields and use compound linking in queries. Creating the composite UniqueID field is not required but it is an option.

No, cannot edit linked Excel dataset - can query, filter, sort, include in report but not edit.

Ok. Thanks. But that still leaves me with the question of how to create the relationship between the linked dataset and my custom table. My custom local table has no records yet and no learnerID or CourseId. In one of my previous posts I asked if it is possible to autopopulate the records of my custom table with the records from the linked tables. The answer was that it isn’t. As a workaround I tried to do this make table query (that I mentioned in a precios post (it did not work though in terms of creating a form). Hope I am making sense.

In other words, how do I automatucally populate my local table with Learner Id and CourseID?