Query Structure

[vicsalt]

Scope - Capture down time by machine
We have production machine producing parts over a shift
Within this time there will be down time.
The operator records the down time and production data within a database.

The structure is basicly

Date
Shift
Machine
No of Good Parts Made
No of Bad
DT1 (dropdown box list of known issues)
DT1 Time (time lost due to above)
DT2
DT2 Time
DT3
DT3 Time
ETC..........

Collecting the data not a problem however we have around 25 codes for 25 differant downtime catagorys. Completing a Query to list Total Down Time per Catagory seems impossible espacially as the operators may but to DT catagory in DT1,DT2,DT3.............. Cell

There must be a easier way. I tried Two tables, one for production & one for down time but still struggled

 

[pr2-eugin]

Your second approach i.e. creating two tables should have given you the result you desire..Could you explain why you abandoned the structure? There should be a One To Many relationship between tblProduction and tblDowntime..

BTW, the current structure will go through a very complicated process and getting what you want would be quiet long winded..

 

[vicsalt]

Yes you are right had another go, my initial issue was the setting of a key field, this is now corrected. However Im getting other issues.

Im using a Subform to enter the downtime data (main form production detail) however the subform shows data from previous entrys, I have Data Entry set to On

 

[vicsalt]

More info I have linked the subform to my main form by the Machine Number.
However when I create a 2nd record for the same machine the subform displays all other downtime related to this machine, is there a way to start with a blank subform every record.

Hope this makes sence

 

[Mihail]

My opinion:

tblMain
ID_Main - AutoNumber (PK)
CurrentDate - Date/Time ("Date" is a reserved word - do not use as name)
Shift - Number
Machine - Number (maybe one more table for machines)
GoodParts - Number
BadParts - Number

tblIssues
ID_Issue - AutoNumber (PK)
IssueDescription - Text

tblDownTimes
ID_DownTime - AutoNumber (PK)
ID_Main - Number (FK)
ID_Issue - Number (FK)
DownTime - Number (instruct the operator to use only 1 unit of measurement

 

[vicsalt]

PK & FK - ? KEY FIELD ? Really appriciate your advice, will have a play again tomorrow

 

[Mihail]

PK - Primary key
FK - Foreign key

If a "parent" table has a PK a "child" table must use this PK as FK
Take a closer look:
tblMain has ID_Main as PK.
On the other hand, tblDownTimes use ID_Main as FK
This way Access "know" how the records are related.

 

[vicsalt]

I am close "I Think" however
Open Mainform +Subform
Add Production data (Date, Machine, Good Parts, Bad Parts..........)
Subform section add (subform linked by Machine) add d/time catagory, mins

EG: 800 good parts & 12 bad parts made on M/C 1, there were 2 down time issues, A-12m & B-10mins

Database Entry
MainForm
Date: 11/11/13 M/C: 1 Good Parts: 800 Bad Parts:12
Subform
M/C: 1 (auto populates due to link in MainForm) DT Cat: A Mins: 12
DT Cat: B Mins: 10

All Works Great !

Enter 2nd Record for same Machine and the subform shows ALL down time related to machine 1.

Question: Every time a record is added can the subform display nothing! So operator only adds (sees) the data hes adding

Obviously I do want to collect all downtime for reporting, however the operator will get confused if he sees a huge list of down time issues within the subform every time he adds a record.

I hope this all makes sense

 

[Mihail]

I am close "I Think" however...

Based on your description... I don't think so, but if you say...

What about to upload your DB in order to take a closer look? Fill it with some data for test.
Convert the DB in Access 2003 version if it is possible.

 

[vicsalt]

reply, will do I just need to extract the relivant detail and remove stuff u dont need

Cheers