Help with converting Excel to Access

[InfernoJaffa]

Hi Guys,

I have created a shared workbook in Excel and it isn't working very well with constant lock outs and losing data.

I would like to create an access database to replicate what the workbook does but I am having trouble on how to start.

Basically, the workbook tracks and records cycle counts for equipment under test. I have 50 rigs which have 8 bays each, each bay can test 1 unit. The excel sheet displays 1 sheet with 8 rows per "rig" (so 50 rig sheets in total), I can change the values in these rows to suit.
When the equipment under test has completed I can mark a column "yes" click archive and the row will be copied to another archive sheet and then everything apart from the first column is deleted leaving an empty bay for me to use in the future.

I am having trouble replicating this as I am unsure on how to tackle it.
I have set up a datasheet form which displays the 8 units on test.
The problem I am facing is that when I "archive" (I am using check-box on record and re-query to remove the record) it removes the whole row so I cannot use it for another test in the future.

I have attached an example excel sheet to show how the current shared workbook functions.

Any help would be greatly appreciated.

Thanks

 

[plog]

The process of setting up your tables is called normalization (https://en.wikipedia.org/wiki/Database_normalization). Give that link a read, google others, do some tutorials and then tackle your data and post back your attempt.

 

[Minty]

Okay - I think you have fallen onto the classic trap of trying to make a database look like a spreadsheet. The fact you have created a datasheet copy of your data leads me think this.

You need to normalise your data.
One simple step is to look at your archive function. You wouldn't "move" your results to an separate archive table (Sheet) you would simply have a completed date field and filter out the uncompleted data. This gives you easy look up of completed jobs and the ability to report on numbers completed per day / week month etc.

Search on here for normalisation - and start with your data, not with a form that looks like your spreadsheet. There are numerous interesting threads about starting to learn about this. (Some of which will show you how NOT to proceed)

Start by removing things like test1, test2 etc. Have a table of units and a table of tests per unit.

 

[InfernoJaffa]

The process of setting up your tables is called normalization (link removed). Give that link a read, google others, do some tutorials and then tackle your data and post back your attempt.

Hi plog,

Thanks for your reply.

The part I am stuck on is trying to replicate the sheet attached in my original post. I am unable to create some kind of form that will display the 8 rows of units under test (a blank row if that bay is free which i can fill in) with a means of "archive" where it will blank out a row so I can use it again (but still keeping the data).

I have looked into normalization but this is more for tidying up repetitive strings? Forgive me if I am wrong.

 

[InfernoJaffa]

Okay - I think you have fallen onto the classic trap of trying to make a database look like a spreadsheet. The fact you have created a datasheet copy of your data leads me think this.

You need to normalise your data.
One simple step is to look at your archive function. You wouldn't "move" your results to an separate archive table (Sheet) you would simply have a completed date field and filter out the uncompleted data. This gives you easy look up of completed jobs and the ability to report on numbers completed per day / week month etc.

Search on here for normalisation - and start with your data, not with a form that looks like your spreadsheet. There are numerous interesting threads about starting to learn about this. (Some of which will show you how NOT to proceed)

Start by removing things like test1, test2 etc. Have a table of units and a table of tests per unit.

Hi Minty,

Thanks for your reply.

As my above reply I believe I've caused some confusion.

I have started the database and included what has been suggested but thought it would be more beneficial to attach the working Excel sheet to show what I am struggling with.

I have attached the database.

 

[plog]

In the house building process, forms are equivalent to picking out the wall paper and fixtures for the upstairs bathroom. Its the part everyone sees, its the part everyone wants a say in, but it is not the part that ensures your house doesn't fall down.

The guys with the cement truck are the most important people in building a house. If they screw up, no shade of perriwinkle in the crapper is going to salvage the place. Tables are the foundation of your database, get that right before moving on.

Normalization is more than strings. Its picking the right field types, its putting fields in the right table, its making the correct tables and its properly establishing the relationships among your tables.

Post a database with your table structure and let's start there.

 

[InfernoJaffa]

In the house building process, forms are equivalent to picking out the wall paper and fixtures for the upstairs bathroom. Its the part everyone sees, its the part everyone wants a say in, but it is not the part that ensures your house doesn't fall down.

The guys with the cement truck are the most important people in building a house. They screw up, no shade of perriwinkle in the crapper is going to salvage the place. Tables are the foundation of your database, get that right before moving on.

Normalization is more than strings. Its picking the right field types, its putting fields in the right table, its making the correct tables and its properly establishing the relationships among your tables.

Post a database with your table structure and let's start there.

That's a good explanation of things!
I've attached whats I have managed so far above.

 

[plog]

Basically, the workbook tracks and records cycle counts for equipment under test. I have 50 rigs which have 8 bays each, each bay can test 1 unit

You really need to be precise in the terms you use. We have no frame of reference for what you are talking about. In your initial description you used the terms 'cycle counts', 'equipment', 'rigs', 'bays' and 'units'. Of those, the only terms you use in your database names are Cycles (field name, but you also have an HCycle, which confuses us) and Units (table name, but that's a generic term which doesn't really help--every entity is a unit).

In your next post, pretend its career day and you have to explain to 12 year olds what it is you do. No database jargon, no spreadsheet references, just talk to us in simple (yet specific) terms to let us know what your organization does.

So far the database looks fine, but it seems its missing a lot of tables (bays, rigs, equipment, etc.).

 

[Minty]

Plog has it nailed up - You are still thinking in spreadsheet 101 terms.

Break it down into small fixed things. Your unit table should have top level information only in it Serial number, Model number, maybe a Customer Acct Number, date received, date shipped etc.
Things that are fixed and won't change and that there are only one version of.

 

[InfernoJaffa]

You really need to be precise in the terms you use. We have no frame of reference for what you are talking about. In your initial description you used the terms 'cycle counts', 'equipment', 'rigs', 'bays' and 'units'. Of those, the only terms you use in your database names are Cycles (field name, but you also have an HCycle, which confuses us) and Units (table name, but that's a generic term which doesn't really help--every entity is a unit).

In your next post, pretend its career day and you have to explain to 12 year olds what it is you do. No database jargon, no spreadsheet references, just talk to us in simple (yet specific) terms to let us know what your organization does.

So far the database looks fine, but it seems its missing a lot of tables (bays, rigs, equipment, etc.).

Apologies for the vagueness.

We have a test lab which test consumer electronics, which I'll call 'equipment under test' (EUT).

The lab has 50 test rigs which each have 8 test bays, each bay can test one EUT at a time (total of 400).

Location:
This string records where the EUT is on test with the format "TEST_RIG_NUMBER.BAY_NUMBER" (example: 20.6 = test rig 20, bay 6).

Serial Number:
This is a unique number for each EUT.

Build:
This is a description of what the EUT is.

Test Voltage/Frequency/Type:
These are test parameters decided before testing.

Cycles/Hcycles:
This is for test results.

On Test Checkbox:
Default = yes. This records whether the EUT is still being tested.

How the DB should function
An index form will display links to all 50 rig forms.
Each rig form (50 in total) will display 8 bays and whats on test in each test bay, (example: clicking on rig5 will show locations 5.1, 5.2, 5.3, 5.4, 5.5, 5.6, 5.7 & 5.8) I should be able to edit this data.
Setting the on test checkbox to "no" on one of these records will remove that row from the rig form leaving it blank for me to be able to use for new EUT.

 

[plog]

Thanks for the good description, that helps a lot.

First, discrete pieces of data gets stored discretely. You don't compress data into codes: 20.6 isn't how you would store rig 20 bay 6. You would store it in 2 fields, both numeric, one for the rig and one for the bay. That's part of normalization, again, read up on it.

From what you have explained I see these objects:

Locations
this will sort out all the bays/rigs you have
fields include Bay and Rig

EUTs
this will hold the information related to equipment
fields include Build, Serial

Tests
this will hold information about tests you do (this will not contain equipment nor result data, just a list of tests)
fields include Name, Frequency, possibly parameters

TestResults
this will hold information about tests performed on equipment
fields include EUT foriegn key, Test foreign key, date, possibly parameters

Again, those are the objects I see you have mentioned. Most likely you will need a few other tables to help normalize your data to do things like place equipment into bays/rigs, determine who did what test and when, etc.

For now, build those tables and post your database back. Here's a few guidelines:

1. Use the Description section of the table. Tell us what each field is for.
2. Only use alphanumeric characters and underscores in names. Don't put spaces in table nor field names (e.g. [Test Voltage] = [TestVoltage])
3. Account for all your data. I gave broad strokes and omitted some fields, you should fit all your data into your database.
4. Use sample data. Just a few records so we can see what data is going in.
5. Stop talking about forms.

 

[Minty]

Okay - So Now you can simplify your data structure.
Firstly forget your fixed ideas / current restrictions. 50 Rigs - you might add some more. So don't restrict your data storage to your current limits. So lets say we populate a table called TestRigs with three Fields
tblTestRigs
LocationID = Autonumber
RigID - Number Field \¬
BayID - Number Field / These 2 become the Primary Key
InService - Yes/No

This will allow you to set up an infinite number of Rigs with a infinite number of bays, and also remove a bay from service if it becomes defective.

Your Main EUT table;
tblEquipmentUT
EquipID - An Autonumber field set as PK
SerialNo - Text Field - Indexed - am I correct in assuming you could test the same piece of equipment twice
RecvdDate - DateTime

Another table - for storing your tests in;
tblTests
TestID - Autonumber - PK
EquipID - The number of the equipment tested from tblEquipmentUT
TestType - Possibly an ID from a TestType Table?
LocationId - The Test Rig ID from the tblTestRigs
TestCompleted - Yes/No

I won't do anymore - but do you see where this is heading ?

 

[InfernoJaffa]

Hi Both,

I have updated and populated the tables to what you have suggested.

I will delete the previous table/query/forms once I know that I no longer need them (I may need them to see how i done things previously).

Please can you have a look at the 4 tables and suggest if they are OK and where to go next?

 

[Minty]

At a quick glance they look about right.
Obviously there may be things we haven't allowed for - or that you haven't thought of yet. But if you see how the data ties together at this point, you should be able to spot any holes in the design quite easily.

You don't have to, but you could add the relationships into the mix, to help clarify how it goes together;

 

[InfernoJaffa]

Thanks, just copied over the relationships as pictured.

What would be the next steps to take to be able to display and edit the 8 currently on test records for each rig?

 

[Minty]

My initial thought would be to create a continuous form with the essential information displayed based on the test completed being null or no. This would always give you a current "on test" list.

Once you have this basic design correct with combo boxes set up for the various data fields that you can edit.
Once the basic layout and data are in place you can then look at adding a filter method to restrict the view to a specific Rig - again probably using an combo box, and or maybe filters to show Occupied , Unoccupied or All bays.

 

[Minty]

You might want to consider adding another table tblEmployees to record who is doing what tests , and also later to record who is updating the data.

 

[InfernoJaffa]

My initial thought would be to create a continuous form with the essential information displayed based on the test completed being null or no. This would always give you a current "on test" list.

Once you have this basic design correct with combo boxes set up for the various data fields that you can edit.
Once the basic layout and data are in place you can then look at adding a filter method to restrict the view to a specific Rig - again probably using an combo box, and or maybe filters to show Occupied , Unoccupied or All bays.

The filter will be able to show whats on test and even filter out individual rigs but I am stuck with being able to show unoccupied bays. The list should be able to show all 400 bays even if they are unoccupied.

 

[Minty]

You have a list of all possible bays - so create a query that lists them all with any matching test records that are incomplete. You'll need to use a left or outer join to do it.

 

[InfernoJaffa]

I think I'm half way there but my query shows no data. I have 400 bays in the table and have joined it (i think?) with the test table.

Can you spot what I have done wrong?