Creating Simple Relational Database Example Step by Step

[jaryszek]

Hi,



this is cross posted from here:

https://www.access-programmers.co.uk...d.php?t=294303

In attachment there is an example database.
In MainForm there is possibility to choose Person and all documents for this Person will be displayed in subform.

My Model:
1. Employees table - Person and Job Part (work time manually inputed by User) with ID_Primary as one in one to many relationship with table EmployeeDocuments
2. EmployeeDocuments : ID_Primary, DocumentId, DocumentName
3. DocumentsMoney: DOcumentID, MonthDate, Money

The key problem here is that One person can have multiple documents, and for each document can be at least 6 months (or more).
1. How can i do relationship between my table in order to get:
https://ibb.co/djxR7k

2. I want to add possibility to User to choose date range on form. for example from 01.2017 to 06.2017. After that i want to have on my subform in MainForm all MonthDates already written by Access automatically. User could input only money for each document and for each month.
Example:

OUTPUT TABLE:
DocumentName, MonthDate, Money
document 1 , 01-01-2017
document 1 , 01-02-2017
document 1 , 01-03-2017
document 1 , 01-04-2017
document 1 , 01-05-2017
document 1 , 01-06-2017

Please help Gurus,
maybe you have some ideas to do that in best efficient way.
Maybe append queries ? or VBA only ?

I will be grateful for any help,
Best Wishes,
Jacek Antek

[June7]

Suggest rename ID_Primary in Employees to EmpID and ID_Primary in EmployeeDocuments to EmpID_FK. Also, change names of the two DocumentID fields so they aren't exactly same.

To batch create Records in DocumentsMoney table use VBA looping code executing an SQL INSERT action. An alternative is code automatically increments the date value when user inputs the Money. User would enter MonthDate and Money for first record and code would set the MonthDate textbox DefaultValue property so on subsequent records the MonthDate would automatically populate as soon as the Money is entered.

Also, for a search functionality, review http://www.allenbrowne.com/ser-62.html

[jaryszek]

Hello June7,

thank you very much for you answer.
I am going for holidays tomorow morning so i will answer as soon as I come back.

I have a few questions for your ideas.

In my database there is no relations between EmployeeDocuments table and DocumentsMoney table. I could not do it... (i am learning Access) could you please advise me how to do it the relationships like in image in attachment (one to many relationship between these 2 tables are faked in Paint...)

To batch create Records in DocumentsMoney table use VBA looping code executing an SQL INSERT action.

Ok this is simple. Thank you. User can choose range of dates and use VBA insert into to create records in DocumentsMoney table. Very clever! User can have even button "Add dates to database" and with this simple way can choose what dates he wants to add.
One question here. How to add records only for document 1? And secondly user have to have choice to use new date range for document 2?
I can not imagine it yet.

User would enter MonthDate and Money for first record and code would set the MonthDate textbox DefaultValue property

This is a part what i dont understand exactly. Could you please explain it in details?

User is inputing Money and MonthDate for first month. And what does it mean "and code would set the MonthDate textbox DefaultValue property"
So thanks to setting default property in Access you can automatically add date + one month in application?

So for example textbox will be as default 01-2017. you can set default properties to text box and Access will add one month automatically for each new record for each document ?
What about documents? For each of them User can have possibilty to choose date ranges.

Best wishes,
Jacek Antek

[June7]

What do you mean by 'could not do it' - why not, what happens?
I thought the link between EmployeeDocuments and DocumentsMoney is the two DocumentID fields. That is what is demonstrated in the image.

For the batch create method the INSERT action would include the DocumentID. Code something like:

Dim dteDate As Date
dteDate = Me.tbxStart
While dteDate <= Me.tbxEnd
     CurrentDb.Execute "INSERT INTO DocumentsMoney(DocumentID, MonthDate) VALUES(" & Me.DocumentID & ", #" & dteDate & "#"
     dteDate = DateAdd("m", 1, dteDate)
Wend

For setting DefaultValue property, code like.

Me.tbxDate.DefaultValue = DateAdd("m", 1, Me.tbxDate)

The real trick is figuring out what event to put code into. For the batch process could be EmployeeDocuments form AfterUpdate event. For the DefaultValue, would be the date textbox AfterUpdate event.

[jaryszek]

Hi Guys,

sorry for my late answer. I came back recently from holidays.

What do you mean by 'could not do it' - why not, what happens?
I thought the link between EmployeeDocuments and DocumentsMoney is the two DocumentID fields. That is what is demonstrated in the image.

1. Image is specially faked in windows paint.
I do not know how to create one to many relationship between EmployeeDocuments table and DocumentsMoney table.

I can create relationship between EmployeeDocuments.ID_Primary and DocumentsMoney_Document_ID but it will be one to one relationship type...and this is what i don't want to do.

I wan to create one to many relationship between EmployeeDocuments and DocumentsMoney because each employe document can have a lot of dates...

When I am refereing EmployeeDocuments.DocumentID with DocumentsMoney.DocumentID there is only unspecified type of relationship... It is a good aproach? This will be not one to many relationship...or maybe it will be be without enforcing data integrity? It is correct?

So maybe we should start here...please help Guys.

Thank you in advance for your help,
Best Wishes,
Jacek Antek

[June7]

This is basic relational database concepts. Have you studied an introductory tutorial?

Need a field in DocumentsMoney for storing EmployeeDocuments ID_Primary value as a foreign key. The relationship link would be on these fields.

Build a form/subform arrangement for data entry/edit.

[jaryszek]

Thank you June7,

yes i have studied basic ralational concepts. Watching films and tutorial but in practice - you have to gain some experience on live models

Ok it was very simple i do not know why i could not did this... (below screen)
https://ibb.co/gvahja

I have created a form and subform (MainForm in my sample database in attachment).

1. Question one: my new number DocumentsMoneyID is a number field.
If user wants to add new range of dates this number should be filled automatically i guess yes? (to know which document has dfferent date ranges and money). For now this field is empty...so i suppose that i have miss something here...

2. Question second:
Firstly, i would like to figure out the DefaultValueProperty method.
I added textbox tbxDate into Form (it will be hidden later- visibility = false).
Now when user is inputting DocumentName and MonthDate and Money i am setting the defaultvalue property with this code:

Private Sub Form_AfterUpdate()

Form_MainForm.tbxDate.Value = Me.MonthDate.Value


Me.MonthDate.DefaultValue = DateAdd("m", 1, Form_MainForm.tbxDate.value)


End Sub

Example:
User inputed documents5 in new record,month date as 2017-07-14 and money 300 zł.
Code above should be setting up new record with monthDate + 1 (2017-08-14).

monthDate is very strange: "1905-06-17"

why Access is setting up defaultValueProperty bad? what i am doing wrong?

Problem with this metod is that user have to write all documents manually here. He has to put for example 5 times the same document name for each date range...
but i want to learn this so I am asking about help...

Thank you once again!
Best wishes
Jacek Antek

[orange]

For clarity, can you step back and tell us a little about the business process in plain English --no jargon?
You have Employees, Documents and Money. Where does JobPart fit into this picture?
Could you give us a step by step description of what an Employee does --in business terms -- during a work day?

[jaryszek]

Hi orange,

thank you for your answer.

Okey.

Clients are sending paper documents which are for each Employee.
So for example for John Smith there will be 2 piece of papers with:
1 page - document name 1 and money and date ranges and jobPart to input
2 page - document name 2 and money and date ranges and jobPart to input

job part will be the same on both pages.

that is it,
now user has to input these data into Access database.

Best wishes,
Jacek

[June7]

Suggestions:
Rename DocumentID in EmployeeDocuments to EmpID_FK
Set DocumentsMoneyID as PK in DocumentsMoney
Rename DocumentsID to EmpDocID_FK
Link ID_Primary from EmployeeDocuments to EmpDocID_FK in DocumentsMoney
Delete existing records from DocumentsMoney

Don't need the saved query objects. Build SQL directly in the form RecordSource.

You might want a form/subform/subsubform arrangement.

Main form bound to Employees, subform bound to EmployeeDocuments and subsubform bound to DocumentsMoney.


You are entering multiple values into JobPart as CSV string. This might cause you lots of pain if you want to do sort and filter on this data.

[jaryszek]

Hello June7,

thank you for your help and support. I have corrected my model with your tips.

https://ibb.co/dCGJaF

Also JobPart I set as decimal field.

In attachment please see the model.
I have built also form Employee and added EmployeeDocuments as subform and subsubform is DocumentsMoney.
Problem is that i cant do it via wizard (wizard is not working here - i do not know why) so I have to add object record manually...:

https://ibb.co/iEHfpa

I have read about this problem and tried to add trusted located but this did not work. Also reinstalled Access and this alsi did not work.
doesnt matter. I created forms based on my tables and drag them into my Main form.

In attachmet there is my sample database.
Please look at this.

So now,
i have Main form (EmployeeForm) with 1 subform: documentsMoney form and subsubform EmployeeDocumentsForm. Wow.

So now maybe it is the time to do a code for your first metfod with defaulevalueproperty June7?

Best Wishes,
Jacek Antek

[orange]

Clients are sending paper documents which are for each Employee. 
So for example for John Smith there will be 2 piece of papers with:
1 page - document name 1 and money and date ranges and jobPart to input
2 page - document name 2 and money and date ranges and jobPart to input

job part will be the same on both pages.

So here are a few questions to help clarify what you are dealing with/trying to support: Some may not be relevant, but worth understanding where they fit in your overall processes and business. Remember we only know what you tell/describe to us. If you were hiring a consultant to "develop your database application" they would need answers and details and confirmation. You have to do the analysis before working with physical database.

Who is the client? Why are they not represented in your Model?

Is John Smith an Employee or Client?

2 pieces of Paper-- there must be a clearer definition of this. Is this a Quote/Bill/Expense/Project??? Please give a more meaningful description.

Document --is that the pieces of Paper or Page 1 vs Page 2? A simple statement of what a document means to your business would help with context.

I'm sure Money has meaning in your business --details please. Is this a BillAmount, SaleAmount, Rent.....??? Tell us more info.

You say DateRanges but there is no Date range in your Model. What does MonthDate mean? Why not define the field with more meaningful name eg DateOfEntry, BillingDate or whatever it represents? And use a real Date with Month Day and Year. You can extract Month using the Month() function where and if needed. A Range implies a period of time with a Start and Finish/End.

You have used JobPart, but you have not told us what this is in your business. You have shown us how you have included it in your database, but we don't know what it is. If this is a JobPart, then what is a Job?

In order to design and develop a database to support some "business/processes", you have to understand the business/processes. If you can't describe it (what you are intending to support with automation) in simple, clear terms, then I suggest you have not done sufficient research/analysis to develop the database.

Here is an example of the kind/level of info required to start the design/development process. It is taken from a tutorial at RogersAccessLibrary and is meant to provide a sample of "business/process" to be supported by a database.

ZYX Laboratories requires an employee tracking database.
They want to track information about employees, the employee's job history, and their certifications.
Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address.
Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator).
Employees can also earn certifications necessary for their job.

Good luck.

[jaryszek]

Hi orange, thank you for your reply.

I am this consultant and I am creating the database

Business warning: in my company is a lot of things to order and set up. These things are connected with setting up new processes and doing standarization and in my company - everything is done in totally different way which is not always proper one.
Additionally i am doing only technical things - designing database - i am not business analyst which gather requirements, i have only guidelines which i have to do.

Clients does not matter here. John Smith is Employee which user has to input into database.

2 pieces of Paper

paper is simple an invoice for the employee.
Whole document contain 2 paper pages with data which need to be input.

I'm sure Money has meaning in your business --details please. Is this a BillAmount, SaleAmount, Rent.....??? Tell us more info.

It is an invoice amount for dates ranges so for example John Smith was working in company X for different amounts.
John Smith jan 2017 - 300 zł (50 $)
Jonh Smith feb 2017 - 100 zł (25 $)

What does MonthDate mean?

It means like in example above that John Smith was working from specific period of time.
Ok i will fix this field name.

You have used JobPart, but you have not told us what this is in your business. You have shown us how you have included it in your database, but we don't know what it is. If this is a JobPart, then what is a Job?

JobPart you can translate in Polish as "Wymiar Etatu" and it is only name for this topic. It means Part Time Job.

Thank you for the link you provided. I will read this.

Best Wishes,
Jacek

[orange]

If you are working in Polish, then describe what you are doing in Polish and use Google translate to show us the English.

Google translate shows part time job to be Praca w niepełnym wymiarze

Jak można zaprojektować bazę danych w sposób techniczny, nie wiedząc, jakie procesy biznesowe ma wspierać?

[jaryszek]

Hi orange,

i did the description what i want to achive. I also fixed my database as you wanted in order to improve my model. Thank you for that!

The model is in Polish but please don't be so picky - here i am asking about technical details , how to achive something not to translate words exactly.

what will be in this field Part Time Job or Praca w niepełnym wymiarze or Flexible Job Part all I need is to input this data manually.
So user has to write it into my database. From piece of paper. Manually.

MY model is clear, June7 is helping me and knows what i am doing here and what i want to achive.
If you still dont understand something - please ask as you did before or simply give your tips how can it be solved.

Problem here is how to achive inputting date ranges very quickly by user. To shorten user manually work.

Jak można zaprojektować bazę danych w sposób techniczny, nie wiedząc, jakie procesy biznesowe ma wspierać?

Are you Polish?

I will reply in Polish also:

Proces opisałem wcześniej. I nawet Ci odpisałem co jest w jakim polu. Jeśli czegoś nie rozumiesz - pytaj.
W mojej firmie praktyka jest taka, że czasami tworzę rozwiązanie techniczne przez 2 miesiące, baza danych i model działają przez miesiąc, a później muszę tworzyć całkowicie nowy model bo proces się zmienił.
I mimo, że o tym wiedziałem od początku i ostrzegałem menagera--> nie mogę z tym nic zrobić więc klepię jak taki dureń

================================================== ============================================>

So if you want to help please answer me:

1. How can i implement DefaultValueProperty (first June7 method here) into my last sample database?
This solution i could implement into my company envinronment later.

After this solution i will be try to find about the second method because i want to learn and develop myself

Thank you once again for your help,
Best Wishes,
Jacek Antek