Inserting data

[Rudolf14]

Hi All,



I need help.
Im creating a report writer in access.

My table is stetted as follows:
ID. Area. Section. Machine. Component. Date. Severity. Temperature. Work order Nr. Finding. Description. Recommendation. Feedback.

My database works as follows in excel.
Area: Section: Machine: Component: Date: Severity: Temperature:
North P19 P19/1 JIb LH 2019/09/05 Normal 28
JIb RH 2019/09/05 Borderline 32
Snub LH 2019/09/05 Urgent 75
Snub RH 2019/09/05 Critical 89
Drive LH 2019/09/05 Urgent 65
Drive Rh 2019/09/05 Normal 21
Now, Im building in this in to access, because of the amount of data and size of the spreadsheet.
Currently in access each time I analyse I need to put in the area, Section and machine.
What must i do to have it inserted automatically untill In change the area/section/ machine?

Regards,
Rudolf

[pbaldy]

Try this in the after update event of each control:

http://www.theaccessweb.com/forms/frm0012.htm

[pbaldy]

Oh, and if you need it:

http://www.baldyweb.com/FirstVBA.htm

[accesstos]

Hi Rudolf!

Initially, you need to normalize the database.
You have to create (at least) six tables for: Areas, Sections, Machines, Components, Machine Componets and Analyses.

For example:

tblAreas

AreaID: Autonumber

AreaName: Text

tblSections

SectionID: Autonumber
SectionName: Text
AreaIDfk: Long (Foreign key to tblAreas.AreaID)

tblMachines

MachineID: Autonumber
MachineName: Text
MachineImage: Text

tblComponents

CompID: Autonumber
CompName: Text
CompDescription: Text
CompImage: Text

tblMachineComponets

MachCompID: Autonumber
MachineIDfk: Long (Foreign key to tblMachines.MachineID)
ComponentIDfk: Long (Foreign key to tblComponets.CompID)
MachCompCode: Text
MachCompDesc: Text

tblAnalyses

AnalysisID: Autonumber
AreaIDfk: Long (Foreign key to tblAreas.AreaID)
SectionIDfk: Long (Foreign key to tblSections.SectionID)
MachineIDfk: Long (Foreign key to tblMachines.MachineID)
MachCompIDfk: Long (Foreign key to tblMachineComponets.MachCompID)
AnalysisDate: Date/Time
Severity: Text
Temp: Single

AnalysisNotes: Memo

Presently, I think is enough.


After that, you need to create the relationships, the properly queries, a subform for analysis measurements (severity, temperature and other details) and the parent form for the analysis details (area, section, machine, component and date).


I hope that it is not a daunting task for you.

Cheers,
John

P.S.: Perhaps I forgot the table for the work orders and the relevant field in tblAnalysis.
I think that many analyses can are parts of one work order.

[ArviLaanemets]

And a table tblMacineSection too (this will make a couple of FK fields in tblAnalyses obsolete).
tblMachineSection: MachineSectionD, MachineIDFK, SectionDFK, FromDate