Access Table Setup (1 Viewer)

cyliyu

New member
Local time
Tomorrow, 07:12
Joined
Jul 27, 2019
Messages
20
I am in the progress of setting up the Access table and need some advise before I expand it.

1) I have at least 13 products and each product has its own sheet in Excel. I was thinking should I just combine them in one Access table? Does any disadvantage or concern I need to take note of?
2) Almost all the fields contained the same information except 1 or 2 fields calculation will be different based on the product groups (A, B or C group), can Access handle this portion?
3) Is it possible if I create a Main Menu with the individual product buttons, so when the button was clicked, it goes into a sub-menu for data entry and the product name (based on the name of the button) will auto added into the table (product field)? and the calculation will auto take place base on product name? Thanks.
 

June7

AWF VIP
Local time
Today, 15:12
Joined
Mar 9, 2014
Messages
5,423
Yes, one Access table.
Yes, Access can handle conditional calculations.
Yes, Access can be designed to accomplish.

The more "user friendly" the more code.

What exactly is purpose of database?
 

cyliyu

New member
Local time
Tomorrow, 07:12
Joined
Jul 27, 2019
Messages
20
The database basically to track the product life span, failure rate, supplier A and B repair rate, the number of serviceable days and more. Once hit certain criteria, the product needs to scarp etc.
A pivot chart will need to tabulate base on different required too.

I will base on One Table concept to expand it. Thanks for your inputs.
 

Micron

AWF VIP
Local time
Today, 19:12
Joined
Oct 20, 2018
Messages
3,476
concern I need to take note of?
Something called normalization. I would bet June7's money that you will fall into that trap when coming from Excel.
And 1 button per product? Absolutely no. Do yourself a favour and research building an Access database. It's relatively easy to make one - a poorly designed one that is.
 

cyliyu

New member
Local time
Tomorrow, 07:12
Joined
Jul 27, 2019
Messages
20
Given a Car as an example, it has a Display Unit, Air-Con system and more.
The DB will have the Main Menu to display all the system, and the individual system end-user will make a selection to input the defect info etc.
If 1 button per system is not recommended, what should be a correct way of organising it?
 

June7

AWF VIP
Local time
Today, 15:12
Joined
Mar 9, 2014
Messages
5,423
I meant one table for products based on description that 13 sheets have same fields. What other tables are needed is not clear.

Instead of buttons for items (like a MacDonalds cash register screen), conventional approach in database would be to select system from a combobox or listbox.

If you want to provide workbook for review, follow instructions at bottom of my post.
 

cyliyu

New member
Local time
Tomorrow, 07:12
Joined
Jul 27, 2019
Messages
20
Yes, all the 13 sheets will have the same fields. I am adding a product field into the Access table to have all the 13 products into one Table.
A Listbox and Combobox was the initial plan but were worry end-user may select the wrong product to input the data.

The DB consists of In-house Defect records, Supplier Repair history, Car Number Tables.

I attached the sample file of my Excel Spreadsheet. Basically, 2 products in the file, one of the product consist of 3 sheets, in-house defect, supplier repair and product installed to. the other product also having the same number of sheet except there is an additional column need to calculate.
 

Attachments

  • Sample.zip
    742 KB · Views: 89
Last edited:

Users who are viewing this thread

Top Bottom