Database Design

[JuniorExcel]

Hi All!
I trust you are well.
I would like to design an access database, and would like to know if anyone can help me do it, if I gave the functional description and management rules of the database?
Looking forward for your replies
Regards

 

[Micron]

Not sure what level of help you expect or might receive here or anywhere. Forums are usually for helping people work through issues or answer questions and I think many take the view that they will help those who are making an effort but won't repeatedly hand you a fish, so to speak. Access has a steep learning curve, which does not make a forum a great place for anyone to provide the level of help I suspect you'll need.

My recommendation would be that you at least review db normalization, and when you think you understand it, create some tables in Access and use the relationships tool to create the relationships and post a pic of that window and someone will review what you created. There are other tools you can use to design the relationships but Access is good enough for that IMO. Just know that you are not likely going to get it right the first time so be prepared to change things. Also, those who would provide guidance will need a good understanding of the business that the db must support.

Post 4 has links for normalization and for a lot of common traps people fall into when it comes to Access. Concentrate on normalization first, I think.

Best approach for multiple look ups?

So, I want create a form that will utilise multiple datasets and am looking for some advice on the best way to do this. In as basic an explanation as possible… Step 1: I enter a postcode Step 2: It automatically looks up the LSOA from a very large dataset (too large for one sheet, spreads...

www.mrexcel.com

 

[JuniorExcel]

I have a basic understanding of access. I can provide a model of the database started( but not completed) with the specifications, if it helps ?

 

[JuniorExcel]

I enclosed a template database started by an access user but not completed. I do not know if it is optimal, maybe a redesign is necessary. Below are the specifications:

1 - Context and definition of the project
The goal is to have a centralized management model via DBMS of authorizations/clearances that are listed on Excel, in order to better control their updates. I have synthesized the data in a double-entry table as below

Name	Surname	Entreprise	Entity	Prof ID	entitlement category 1	entitlement category 2	….	entitlement category 39
Name1	Surname1	Entreprise1	Entity1	XXXX	01/01/2020	01/01/2018	…	05/10/2022
….​	….​	…..​	…..​	…..​	….​	….​	…​	….​
NameN	SurnameN	EntrepriseN	EntityN	XXXX	01/01/2020	01/01/2018	…	05/10/2022

NB: The dates correspond to the purposes of validity of the authorization types.

2 - Project objective

• The database that I want to edit, will allow you to select in a form via the name and surname of a person, the type(s) of authorization(s) that he has and to enter his (their) expiry date(s).

3 - Functional description of needs

I Added three other modifications to the initial Excel table in the database:

o Change one: There is an “entitlement category”, which has a Family/Sub-Family relationship with the “entitlement type”. I would therefore like to have a field (or a table) which will record the different Categories (or Family) of authorization/clearances. After the choice of the person, this category will be selected, before selecting the type of associated clearance (Sub-family) to enter its expiry date

o Modification two: rather than having an end of validity date for the type of authorization, I would like to add the date of issue of this type of authorization/clearance and its end of validity date which will be a calculated field (date of issue + Year), X = 5 years typically or configurable depending on authorization/clearance.

o Modification Three: I want to add the authorization card number of each person in a field or a table. This number is specific to each person who has an authorization and must be able to be entered in numerical format [XXXX/XXXX] i.e. - Order No./Year, for example No. 0800/2022.

• I would then like to make requests in order:

o To highlight or extract expired authorizations:

• Either by company/Enterprise (ie If I select an Enterprise is it possible for the query to return all the people of that enterprise whose clearances are expired :name, surname, type of clearances date of expiry, etc)
• Or by entity (ie If I select an Entity is it possible for the query to return all the people of that entity whose clearances are expired:name, surname, type of clearances date of expiry, etc)
• Either per person

o Be able to highlight or extract the clearances that will expire in a certain period of time (either in number of days, or months or years)

o If possible, be able to make statistics on, for example, the percentage of authorization expired and/or not expired at a given moment (maybe current time or else)

Management rules of the DB :

1-A person has one or more clearances (a clearance is a kind of “authorization”, for instance the authorization to drive a car, a dozer, or a wheel loader)

2-An authorization can be possessed by one or more persons

3-a person can have several Clearances/authorizations which belong to a category. (A category is a kind of group of specific clearances or authorizations, for instance I can say that a dozer and a loader belong to the category of “heavy equipment” and that a car belongs to the “light vehicle” category)

4-There are several categories

5-Each category includes one or more authorizations/clearances

6- Also: a person can have one or more categories

7-Similarly, a category can be owned by one or several persons.

8-We must be able to update the database because:

o The list of authorization/clearances types and/or authorization/clearances categories must be able to be upgraded to add new ones.

o New people can be added or old people deleted with the corresponding information (company, entity, personnel number)