Just starting out - confused by tutorials

[DHFE]

It's been years (probably even nearly multiple decades) since I made any sort of use of Access, and even then it wasn't a proper database.



So I generally learn better by breaking apart something which does much of what I need, and then reconstructing it to do what I actually need, learning as I go and developing the extra parts though additional sources; and as a result I'm struggling to develop my database from tutorial videos I can find.
What I'm hoping for here, is for someone one (or multiple people) to give me the building blocks of what I'm trying to do. I'm not expecting anyone to make this for me (I won't learn otherwise).

I have a matrix (of sorts) which details employees in our company and their training records. Each of the different aspects of training need to be refreshed after a certain number of months (differs dependent on the specific training). I'm trying to create a database of this information, rather than the unrulily Excel spreadsheet form it's currently in.

I'm struggling to understand when I need data in separate tables, and when it can be in the same table. I'm also struggling in other aspects, but this could be because I'm falling at the first hurdle.
I know this should be fairly straightforward, but my head's struggling to comprehend how to lay things out.

So we need to record the following information;

• Employee Name
• Department - I want this to be a pre-determined list for the user to select from (a list that can be added to)
• Job Title - I want this to be a pre-determined list for the user to select from (a list that can be added to)
• Employed (Y/N) - so people can be removed/hidden from the database
• Certificate Name - approx 50 different certificates need to be listed (can be added/removed from list)
• Certificate length - number of months before training needs to be refreshed
• Valid From - completion date of training (updateable)
• Valid To - expiry of training certificate based on certificate length (not manually editable)

This also won't be a database I will personally make use of, so I was hoping to make it so that it presents to the end user without the database itself visible (although accessible to make any background changes), I know that the end users aren't quite a proficient with using computers, so I wanted to make it as simple for them as possible.

The user will need to be able to add new employees and their information to the database. They should be able to select if they are still employed - both the employed and unemployed lists should be separately viewable, and former employees should be able to be reinstated
Both the Department and Job Title lists should be editable in the same manner - adding new ones, and hiding/reinstating old ones if things are restructured
Again the same sort of thing with the Certificates themselves and their information, add/remove as required

The end user will need to view the certificates an individual employee has/ does not have; as well as which employees do/don't have individual certificates.
There should also be a way to quickly view any certificates which are due to expire in 90 days (3 months), 60 days (2 months) and 30 days (1 month) - as well as those which have already expired.

I think this is everything we will need for now, hopefully if I can put this together from your help I should be capable to amend anything further down the line.

Thanks in adance for any help.

[Welshgasman]

Might want to start here then?
https://support.microsoft.com/en-us/...8-1afcfc91a9cb

Plus these from Micron
https://www.accessforums.net/showthr...553#post510553

[davegri]

The table relationship you need is Many-to-Many. See this Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)
Think of the events as your certificates and the DB should get you started.

[DHFE]

Might want to start here then?

Is there a specific template you would recommend? Because I did have a look at them, but unless I know which ones are useful for what I'm trying to do it's going to be very time consuming looking through each one.

[Welshgasman]

Is there a specific template you would recommend? Because I did have a look at them, but unless I know which ones are useful for what I'm trying to do it's going to be very time consuming looking through each one.

Students or Contacts might be a good place to start. Perhaps even Northwind, as though that is for invoices and the like, the structure could be reused.
Only you know your business. The Northwind has been redeveloped by professionals and should show all good practices inside the DB.

[Bob Fitz]

It's been years (probably even nearly multiple decades) since I made any sort of use of Access, and even then it wasn't a proper database.

So I generally learn better by breaking apart something which does much of what I need, and then reconstructing it to do what I actually need, learning as I go and developing the extra parts though additional sources; and as a result I'm struggling to develop my database from tutorial videos I can find.
What I'm hoping for here, is for someone one (or multiple people) to give me the building blocks of what I'm trying to do. I'm not expecting anyone to make this for me (I won't learn otherwise).

I have a matrix (of sorts) which details employees in our company and their training records. Each of the different aspects of training need to be refreshed after a certain number of months (differs dependent on the specific training). I'm trying to create a database of this information, rather than the unrulily Excel spreadsheet form it's currently in.

I'm struggling to understand when I need data in separate tables, and when it can be in the same table. I'm also struggling in other aspects, but this could be because I'm falling at the first hurdle.
I know this should be fairly straightforward, but my head's struggling to comprehend how to lay things out.

So we need to record the following information;

• Employee Name
• Department - I want this to be a pre-determined list for the user to select from (a list that can be added to)
• Job Title - I want this to be a pre-determined list for the user to select from (a list that can be added to)
• Employed (Y/N) - so people can be removed/hidden from the database
• Certificate Name - approx 50 different certificates need to be listed (can be added/removed from list)
• Certificate length - number of months before training needs to be refreshed
• Valid From - completion date of training (updateable)
• Valid To - expiry of training certificate based on certificate length (not manually editable)

This also won't be a database I will personally make use of, so I was hoping to make it so that it presents to the end user without the database itself visible (although accessible to make any background changes), I know that the end users aren't quite a proficient with using computers, so I wanted to make it as simple for them as possible.

The user will need to be able to add new employees and their information to the database. They should be able to select if they are still employed - both the employed and unemployed lists should be separately viewable, and former employees should be able to be reinstated
Both the Department and Job Title lists should be editable in the same manner - adding new ones, and hiding/reinstating old ones if things are restructured
Again the same sort of thing with the Certificates themselves and their information, add/remove as required

The end user will need to view the certificates an individual employee has/ does not have; as well as which employees do/don't have individual certificates.
There should also be a way to quickly view any certificates which are due to expire in 90 days (3 months), 60 days (2 months) and 30 days (1 month) - as well as those which have already expired.

I think this is everything we will need for now, hopefully if I can put this together from your help I should be capable to amend anything further down the line.

Thanks in adance for any help.

Take a look at the attached file. Just an example of what I think your tables might look like.

[Micron]

I'm struggling to understand when I need data in separate tables, and when it can be in the same table.

Understanding normalization is the key to that. Basically, one table for each entity (person or thing), one field for each attribute.

Employee Name - LName | FName; don't put both in one field. Middle name or initial if desired.

Department - I want this to be a pre-determined list for the user to select from (a list that can be added to)
Known as a lookup table (not to be confused with a lookup field, which you shouldn't use). The autonumber ID field for this would go into tblEmployee.Dept field, which would be of type Long.

Job Title - I want this to be a pre-determined list for the user to select from (a list that can be added to)
Ditto. ID value goes in Role (or Title if you prefer, but not Job Title - no spaces in object names). If people can have more than one title or role at a time, you need a junction table as well.

Employed (Y/N) - so people can be removed/hidden from the database
Date fields (e.g. TerminationDate) are much more useful than Y/N fields

Certificate Name - approx 50 different certificates need to be listed (can be added/removed from list)
Certificate length - number of months before training needs to be refreshed
Both of those can go in the same table about certificates (the entity). You'd need a junction table for employee/training records

Valid From - completion date of training (updateable)
Valid To - expiry of training certificate based on certificate length (not manually editable)
Here's where lack of knowledge about the business leads to suggestions that may not apply. I'm thinking a field for training date in the training table and the Long value from the certificate field to link that to the cert table. However, the cert table holds the Duration, either as a number of months, weeks or whatever is appropriate. This means you may need a field for Units as well:

CertName	Duration	Unit
Cert1	6	Month
Cert2	3	Week

Having Valid To makes that field a calculated one which is something you should avoid. Perform calculations on the fly in reports, forms and queries. Let's say that in the future a cert changes from 3 to 6 months. All Valid To values for that cert are now invalid - but not if a form/report adds the duration to the date of training.

[ArviLaanemets]

...
Having Valid To makes that field a calculated one which is something you should avoid. Perform calculations on the fly in reports, forms and queries. Let's say that in the future a cert changes from 3 to 6 months. All Valid To values for that cert are now invalid - but not if a form/report adds the duration to the date of training.

In case OP wants to filter valid/closed certs in employees subform, then there must be ValdiTo field present in employee's certificates table. The one way to avoid using calculated field for this is, to have ValiTo field linked to combo box, and to make the selecting it obligatory (the record is not saved, when ValidTo is empty). After the record is saved, the combo must be disabled. The selection list will always contain a single date, i.e. the user selects ValidTo date which is valid for this cert at date the record was created (Btw. this setup allows to change cert durabilities for new records, without affecting earlier entries, simply by editing the durability in certificates table).

[Micron]

And the reason you cannot return records where the ValidTo value equals or is less than
- 6 months from the date of training (e.g. 09/21/2023)

by adding 60 days to the date of the cert training is? As in
WHERE (TrainingDate + CertDuration) < #03/21/2024#

What am I missing? Have to run out now so will review again later.

[xps35]

I assume that not every employee needs all (50) certificates. It is likely that it depends on his (current or futere?) job. So be sure to facilitate that in your datamodel and queries.

[DHFE]

Right I think I'm starting to get the hang of it. I've not totally given up on tutorials and have recently found some more which appear to be better that what I'd used previously.

I've worked out that the calculated field I need to create in a query for the certificate expiry date would be DateAdd("m",[ExpiryMonths],[CompDate]) - so this uses the data which states how many months a certificate lasts for, and adds those months to the completed date which the user will enter.

Next I want the user to be able to search for all completed/not completed certificates of a certain type - or vice versa search which certificates an employee has completed/not completed.
I think I need to use the criteria in a query to allow for the user to select what data to run the query on - is that right? Otherwise I'd need to make a separate query for each employee and each certificate, and if more get added I'd need to go back in and make new ones for them.
So the query itself would be the same each time, it's just the criteria that changes, and the user should be able to select that themselves.

I'm just unsure how to actually do that.
I have the employees/certs names - is there a formula which would basically create a drop-down box for the user to select from?

Edit;
I've found this elsewhere;
https://stackoverflow.com/questions/...drop-down-list
Which would probably do what I want, though I think I need dependencies.

So there would be an initial drop list with all the certificate/employees listed, and then a second drop list with the criteria - All Completed Certs, All Incomplete Certs, Expired Certs, Expiring Certs.

Is this possible? Also I can't figure out the if statement, the post doesn't go into detail.

[ArviLaanemets]

...I have the employees/certs names - is there a formula which would basically create a drop-down box for the use to select from?

There is a control named Combobox

When you have a table of employees like:
tblEmployees: EmployeeID, ForeName, LastName, ...

Then in form you want to select an employee, you insert the Combobox named like cbbEmployee, and:
set EmployeeID as its ControlSource Property;
set Table/Query As RowSourceType Property;
set RowSource Propery as query like "SELECT EmployeeID, ForeName & " " LastName As FullName FROM tblEmployees ORDER by 2 ASC";
set BoundColumn Property as 1 (i.e. first column of RowSource query - BoundColumn andControlSource must match);
set ColumnsWidth property like "0;2.5" (i.e. first column of RowSource is not visible, the second one is visible).

[DHFE]

Sorry, I was trying to edit my post and it seems to have been deleted; here's the original.

Right I think I'm starting to get the hang of it. I've not totally given up on tutorials and have recently found some more which appear to be better that what I'd used previously.

I've worked out that the calculated field I need to create in a query for the certificate expiry date would be DateAdd("m",[ExpiryMonths],[CompDate]) - so this uses the data which states how many months a certificate lasts for, and adds those months to the completed date which the user will enter.

Next I want the user to be able to search for all completed/not completed certificates of a certain type - or vice versa search which certificates an employee has completed/not completed.
I think I need to use the criteria in a query to allow for the user to select what data to run the query on - is that right? Otherwise I'd need to make a separate query for each employee and each certificate, and if more get added I'd need to go back in and make new ones for them.
So the query itself would be the same each time, it's just the criteria that changes, and the user should be able to select that themselves.

I'm just unsure how to actually do that.
I have the employees/certs names - is there a formula which would basically create a drop-down box for the user to select from?

[DHFE]

There is a control named Combobox

When you have a table of employees like:
tblEmployees: EmployeeID, ForeName, LastName, ...

Then in form you want to select an employee, you insert the Combobox named like cbbEmployee, and:
set EmployeeID as its ControlSource Property;
set Table/Query As RowSourceType Property;
set RowSource Propery as query like "SELECT EmployeeID, ForeName & " " LastName As FullName FROM tblEmployees ORDER by 2 ASC";
set BoundColumn Property as 1 (i.e. first column of RowSource query - BoundColumn andControlSource must match);
set ColumnsWidth property like "0;2.5" (i.e. first column of RowSource is not visible, the second one is visible).

Thanks,
I was also able to find this;
https://stackoverflow.com/questions/...drop-down-list

It seems to do what I want, the problem is I don't know how to write the if statement. I also think I'll need dependent combo boxes, one for a certificate/employee list, and one for the detailed criteria i.e. "All complete certs", "All incomplete certs", "Expired Certs", "Expiring in 30 days", etc

Edit:
That is unless I made the detailed criteria options as different command buttons and just use the one combo box. (i think)

[Micron]

You have a junction table for employees and their required certs (as well as one for the employee and certs achieved)? If you add a new requirement and run a query against both tables you will find records that are in one and not the other to find certs required but not taken.
https://support.microsoft.com/en-au/...d-eaf10a5d1cb4

the user should be able to select that themselves.

A combo can provide a list of search options - better than a bunch of controls to do the same IMO.
You may find it easier to use 2 queries - one for certs not taken, one for certs and their due/overdue records. There is also this sort of thing
http://allenbrowne.com/ser-62.html