Newbie Needs Help (1 Viewer)

spr1nger

New member
Local time
Today, 14:49
Joined
Jun 13, 2022
Messages
6
I have a project I've been wanting to do for several years and I believe Access is the way to go with it. Problem is, I do not know how to write code & Access really confuses me. I usually can figure out computer programs but this one has my mind twisted. Let me tell you a little about the project I need help getting started with.

I work in Quality Control at a metals manufacturing company. One of my responsibilities is our Calibration System. Unfortunately, the calibration records are recorded in an Excel Workbook. (I inherited it this way :confused: ). The workbook has a tab for each month, and a tab named Used IDs. The Used IDs tab is a list of tools, IDs & who owns them (sorted by Column A, then by Column B).

Calibrations are done on an annual basis & recorded in the Cal WB. A record of the calibration is kept for 3 years. Some tools are not calibrated annually but done every 3 years, but there are only a few.

Each month I have to go through each tool (gage ID), copy the rows of the last two years (on each ID) and past it on top of the oldest record. Then I will change the last entry to mm-22 mm-23, in columns L & M respectively, delete the entries in the Standards Used & Measurements for the 22-23 year & enter the new data when I am able to do the calibration. If that sounds exhausting to you, that's because it is!! 😩 I've attached a copy of the workbook to this post. If you look at the June tab, you'll see what I'm talking about

My question(s) is/are, can you tell me where to start? I initially started by importing the information from the Used IDs tab into Access, but that is where I get a bit stuck. I don't know what's next & the wizard doesn't help me very much. I don't know what the different tables should be named, how many there should be or what data should be in each them. Nor do I know what would be the best primary key for which table, or which table to relate to which table, and... 🤬😤. It's so overwhelming to me.

I envision being able to create a form to fill out after the db is set up, but I just can't get there 😔. Can anyone dumb this down for me?
 

Attachments

  • AccessWorldHelp.zip
    403.2 KB · Views: 62

RogerCooper

Registered User.
Local time
Today, 11:49
Joined
Jul 30, 2014
Messages
218
You need at least 3 tables: Gauge, User, Calibration

Gauge would be all the information you to describe the gauges. Your current Gauge ID# field would be the primary key.

User is the Employee Names you already have.

The Calibration table would be information you already have in columns D through N of your individual monthly tabs. You would need to create an Autonumber field as primary key. You should probably add a column for the Certificate_ID. You could create a field to actually store the certificates in the table.
 

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
4,451
Have you studied any tutorials on relational database concepts? Have you studied a tutorial on Access functionality? Get an introductory book on Access that has a chapter on database concepts.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:49
Joined
Sep 21, 2011
Messages
10,853
If you think your copy and paste is difficult, wait until you start with Access. :)

You could just as easy, easier really, if you have a mental block on Access, just do it in Excel with some vba.
That is what I have done for one project of mine. I copy the data to a master file, add various formulae, and then clear the workbook ready for the next month.
 

spr1nger

New member
Local time
Today, 14:49
Joined
Jun 13, 2022
Messages
6
You need at least 3 tables: Gauge, User, Calibration

Gauge would be all the information you to describe the gauges. Your current Gauge ID# field would be the primary key.

User is the Employee Names you already have.

The Calibration table would be information you already have in columns D through N of your individual monthly tabs. You would need to create an Autonumber field as primary key. You should probably add a column for the Certificate_ID. You could create a field to actually store the certificates in the table.
you make it sound so easy, lol. any hints at importing the information into access from excel?
 

spr1nger

New member
Local time
Today, 14:49
Joined
Jun 13, 2022
Messages
6
Have you studied any tutorials on relational database concepts? Have you studied a tutorial on Access functionality? Get an introductory book on Access that has a chapter on database concepts.
I have watched several video tutorials over the last 3 years, but should probably watch one again. I've started & stopped on this project for 3 years now. Time to invest in it was a factor, but I really would like to get this working.
 

spr1nger

New member
Local time
Today, 14:49
Joined
Jun 13, 2022
Messages
6
If you think your copy and paste is difficult, wait until you start with Access. :)

You could just as easy, easier really, if you have a mental block on Access, just do it in Excel with some vba.
That is what I have done for one project of mine. I copy the data to a master file, add various formulae, and then clear the workbook ready for the next month.
i know less about code than I do Access.😳
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:49
Joined
Feb 19, 2013
Messages
14,695
Away from my computer but hints for importing.
On the access external data tab select excel and follow the prompts. Because you need to split the data up a bit I would link to excel and write an append query to append the data you want in the relevant tables
 

CarlettoFed

Member
Local time
Today, 20:49
Joined
Jun 10, 2020
Messages
51
You have to explain step by step in detail how the activity you would like to register in the database takes place.
Also you should say for each column:
- what kind of data it should contain
- if the entry is mandatory
- if the data must be unique
 
Last edited:

mike60smart

Registered User.
Local time
Today, 19:49
Joined
Aug 6, 2017
Messages
1,235
i know less about code than I do Access.😳
This would be one way to set up your Data Input Form.

It allows you to enter a Calibration Date in the Main Form

Then in the subform add all of the relevant Guages to be Inspected with related data.
 

Attachments

  • QC.JPG
    QC.JPG
    157.2 KB · Views: 56

spr1nger

New member
Local time
Today, 14:49
Joined
Jun 13, 2022
Messages
6
This would be one way to set up your Data Input Form.

It allows you to enter a Calibration Date in the Main Form

Then in the subform add all of the relevant Guages to be Inspected with related data.
i'm sorry i had to take an extra long lunch. can i write you tomorrow?
 

Users who are viewing this thread

Top Bottom