Setting up tables/queries etc... the RIGHT way

[digital]

Greetings from Bosnia!

I started working on a "make my job easier" project.
I have 30 machines, running 24/7, producing various products, and I
have to have some stats about these machines, for example:

INPUT DATA FOR EACH MACHINE:

- product name that machine is currently producing
- order #
- date of production start
- time of production start
- date of production stop
- time of production stop
- number of produced pieces
- buyer
- remarks
- machine currently running (yes/no)
- ammount of product ordered
- delivery term

REQUESTED OUTPUT

- how long was each machine running (in hours/minutes) for a specific product that
it was producing (query by name/product code)
- total working hours of a specific machine
- listing of busy/free machines
- most used machine
- most produced product
- busyest hours/days/months

Question:
How should I make the DB for this project:
1. a table for each machine
2. one table for all machines (~400 fields!!!)
3. any suggestions?

Right now I have:
1 table for every machine (30 total),
1 form for every machine (30 total),
1 main form with buttons representing each machine (30 buttons)
1 table for this main form with machine/button state (green/red - working/available)

This whole system works fine now, but, I'm having the following problems:
When oppened, how to have the form always display the last viewed record?
how to combine the data from 30 tables (if my solution nr 1 - see above - is right),
without bogging down the whole system - access freezes?

Thank you for your tips in advance,
Best regards.

 

[M8KWR]

what i would do is to have 2 tables.

The first table holding the machine info (so you would have 400 lines)

the second table holding the job descriptions for each machines.

You would have a link from these two tables so on the ONE form you should have (instead of 30), you select the machine you want to view, then it would list the job that, that machine has done, currently running.

What you could also do is hide the old job, have an extra field, and just filter the subform to not display these records.

There are many ways you could do this.

If you want any more help. or are unsure about what i have suggested please say.

I could produce you a template for you to start with, and how I feel it should work and the advantages for doing it this way.

You seem at the moment to have a lot of form / tables, which you do not need, and combining them is the best way to go, but you would still be able to query each machine, or a particular make of machine more easily then having to do a union select query for example.

Hope this helps.

 

[Pat Hartman]

You need some lookup tables. For example Product and Buyer tables can be used to populate combos and prevent data entry errors.

The three main tables should be:

tblMachine:
MachineID (autonumber primary key)
etc.

tblProduction:
ProductionID (autonumber primary key)
MachineID (foreign key to tblMachine)
OrderID (foreign key to tblOrder)
ProductionStart (both date and time)
ProductionEnd (both date and time)
Quantity

tblOrder:
OrderID (autonumber primary key)
BuyerID (foreign key to tblBuyer)
QuantityOrdered
DeliveryTerm

Having a flag to determine if a machine is running is unnecessary. If you add the production record when the run starts, the end date will be null. That would give you the same information. This structure allows you to produce product for the same order on multiple machines.

 

[digital]

Thanks everyone!

Thanks for your tips!

Here are 2 GIF's showing the main form which shows all
of the machines, and another one, which exists like 30 times,
with 30 tables, containing machine/production data I described
in my previous post.

Currently, this system works fine, but modifying
it is a major pain The green/red lights on the buttons,
with button states ON/OFF are controlled via VBA,
and stored in 1 main table that's linked with this main form.
It's also controlled via VBA from within all
of the 30 machine specific forms.

So, basically, I like this interface,
but getting data/statistics is a pain,
I'v tried it and trust me, it hangs access completelly :8

Anyways,
I'd really appreciate it if someone would make a model with example
tables/queries (I never used subforms, sub*things, unionqueries etc, just
simple queries with simple criteria - so all your help would really mean a lot)
that I could modify/expand to fit my purpose.

When the program's done, I'd use it on a laptop to input data from machines
on a daily basis, 3 times per day or so.

At the end of the month, I'd print a summary of production by machine make/model/number,
my product name, time spent etc...

Btw, how do I calculate the time spent from timeroductionstart until timeroductionend?
One MINUS the other, I guess? Maybe not?

Thanks again for your replies...

Best regards.

 

[M8KWR]

you should still be able to use the code for the green/red buttons, but instead of looking in 30 tables it would just look in one.

To calculate the time between two fields, you have to use something like

Production_time = between start_time and end_time

Do you have a table that you could print screen?
Are all the tables the same format (i.e. fields, field lengths etc)
Is there any additional fields you require, or do not want anymore?

If you could supply this it would help. One quick point obviously it is not in english so i would not be able to translate it, so if you could that would help things.

 

[digital]

you should still be able to use the code for the green/red buttons, but instead of looking in 30 tables it would just look in one.

To calculate the time between two fields, you have to use something like

Production_time = between start_time and end_time

Do you have a table that you could print screen?
Are all the tables the same format (i.e. fields, field lengths etc)
Is there any additional fields you require, or do not want anymore?

If you could supply this it would help. One quick point obviously it is not in english so i would not be able to translate it, so if you could that would help things.

Thanks for your support so far. I really appreciate it.

Attached are requested screenshots.

A few notes:
- Each machine has all the same fields (same table).
- No additional fields for now, but I may add more machines later?

 

[Pat Hartman]

Currently, this system works fine, but modifying

- Modifying it is a pain because it is not normalized. The sooner you get started on the new structure, the sooner it will be done.

I understand that you like the form with all the buttons. It looks like it might even represent the shop floor layout. To recreate this form with a normalized structure, you can create a crosstab query and base the form on the crosstab. If you add or remove machines, you would need to modify this form but not the rest of the application.

 

[digital]

Yes, it's the shop floor layout.

Okay, I have a basic idea of how this should be done, but my access2k3 hangs when I try to run my crosstab query.

The sittuation:

- I have 30 tables containing same field count/names.
- I have to find a way of showing the values from these fields in a report,
sorted by various fields, totals etc...

Is there a way of getting this done? 30 tables... ~400 fields total.
Every table is the same, the difference in data is that it's just containing machine specific info.

Any ideas?

 

[Rich]

Unless you follow the advice Pat has given you you're going to have a very difficult time trying to Report on anything.
You need to normalise your data

 

[digital]

MMkay

Okay guys, I'v changed a few things, now I have 2 tables,
1 table containing job descriptions etc, and the other - machine status info - for my "nice lookin' machine shop floor layout" with all the green/red lights etc

Now, I have also only 2 forms. I may add 3rd for stats buttons which would call various reports/queries etc...

My current problem is:

When I click on a button representing machine #6, it will display my "universal form", but I want it to display that form AND FIND the last job for this specific machine.

Please suggest a way to get this done.

Btw, this 1 table containing job descriptions WILL GET VERY HEAVY with time, now it has arround 20 fields, but will have hundreds of thousands of records with time. Will this be a problem?

Regards,

AC

 

[digital]

you should still be able to use the code for the green/red buttons, but instead of looking in 30 tables it would just look in one.

To calculate the time between two fields, you have to use something like

Production_time = between start_time and end_time

Do you have a table that you could print screen?
Are all the tables the same format (i.e. fields, field lengths etc)
Is there any additional fields you require, or do not want anymore?

If you could supply this it would help. One quick point obviously it is not in english so i would not be able to translate it, so if you could that would help things.

I'v tried using your tip to get the time between two fields,
but it doesnt work - "between" is not recognized etc..

Can someone please give me an example of doing this?

Thanks..

 

[Mile-O]

this 1 table containing job descriptions WILL GET VERY HEAVY with time, now it has arround 20 fields, but will have hundreds of thousands of records with time. Will this be a problem?

My job description fields - and I'm sure those of most people - have approximately three fields.

JobDescriptionID
JobTitle
JobDescription

How can you have 20? Or have you still not thought about normalising your table structure?