Suggestion on loading/ unloading transaction (1 Viewer)

[mahenkj2]

I am making a small database on a process which involves loading a product on a machine, unloading after a minimum time. Reloading is also possible of once loaded item.

When a product is loaded on a machine, user name, machine ID, date is inserted. This seems doable but what if this product is unloaded, how that transaction should be done.

How many tables for the above should be made? Should I make loading table, unloading table etc or only a single table in which status changes for a particular product ID. But I also need to track when and for how much time the product was loaded.

In above question, I already have supporting tables, I need to understand about loading/unloading/reloading transaction.
I need help basically on structure and table design.

All help well appreciated.

With regards.

 

[jdraw]

Do you have a separate transaction type for reload?

In very simple terms, load product on machine, record the time -->OnMachineTime
unload product from the machine , record the time ----->OffMachineTime

Time on the machine is OffMachineTime - OnMachineTime

If you put that product back on the machine (a load event) record the OnMachineTime
and when you take it off the machine, record the time as OffMachineTime

So if you are working with productNumbers Product XYZ

you would find 2 {load and unload} transactions, and total time on machine would seem to be

trans1 Product XYZ OffMachineTime - OnMachineTime ---> first activity time
trans2 Product XYZ OffMachineTime - OnMachineTime ----> second activity time
first activity time + second activity time = total activity time for Product XYZ

Perhaps I'm missing your point, but it seems like 2 load/unload transaction for the same Product. So sum the individual time on the machine.

 

[mahenkj2]

Do you have a separate transaction type for reload?

At the moment, I have not thought of.

I am right now bit busy in completing another form so I will give a try to your thoughts and shall come with observations.

Many thanks.

 

[jdraw]

I wasn't suggesting a reload transaction- I was just curious if you had it set up that way.

My view (untested) would be multiple load and unload transactions based on the product number, then just sum the times on the machine.

 

[mahenkj2]

In very simple terms, load product on machine, record the time -->OnMachineTime
unload product from the machine , record the time ----->OffMachineTime

I have thought of a loading form and unloading form, so basically two forms. These forms based on a table with fields such as product ID, loading time, unloading time etc. When a product is loaded, loading time shall be inserted. Now, I want to know untill this product is unloaded (means unloading time is entered), it must not be loaded again. How to do that? Please suggest.

 

[MarkK]

I think your "Loading" event is a single thing that has duration as a dimension, or maybe you prefer to record the time dimension as start time and end time. But it's a single thing and I believe it should be described using a single table and therefore a single form.

 

[mahenkj2]

Thanks for your advise.

I have to make this form to understand clearly, so I will give a try. But meanwhile can you suggest how should I make provision that once a product is loaded, it must not be reloaded untill unload.

I am going to have a drop down which shall contain product IDs, so what I think is, once I load a product from that drop down, if somebody else makes a loading that ID should not be available in drop down.

I know without knowing the whole structure you might not be able to pin point but just your thoughts.

regards.

 

[jdraw]

What are the actual steps involved when loading a product? Who does what and how do the database application fit in? Is there anything in that process that "causes/forces/requires" someone to do something in the database?

There is a great video tutorial on how to add/hide items from listboxes.

http://www.datapigtechnologies.com/flashfiles/listboxtrick.html

 

[mahenkj2]

I try to elaborate whole thing in brief:

I have following tables:
tblMake
tblGrade
tblColors
tblInkCode
tblRollingMachines

I have a unbound form to generate ink code and sends data to tblInkCode and then I make a query based on this tblInkCode. This query has calculated field for InkCode.

So, one process is finished. I use the code generated as bar code and put on products. Each product has unique code.
=====================================
Now, we have some kind of rolling machines (1,2..) on which we load the bar coded product. We need to track number of hours the product is rolled.

I make another table
tblTrasaction

fields are
TxID (AutoNumber)
BottleCode
LoadingTime [Shall be now()]
UnLoadingTime [Shall be now()]
LoadedBy
UnLoadedBy

I have no preference whether I keep a single form for both loading or unloading transaction or two separate forms. I will go for what seems to be optimum based on advises.

What I was doing

Made a form frmTransaction with a drop down from a query qryInkCode. This form bound to tblTransaction. I select the bar code from drop down and enter other fields. So, a loading is done. Now I should not be allowed to select that loaded product again. Next time, I should unload that product. Then, it should become available again for reloading if required.

I hope, this is not so much but I need to make it clear for JDRAW's questions.

regards.

 

[jdraw]

Thanks for the description.

Since

Each product has unique code,

it seems any product is in 1 of 2 states -- either Loaded or UnLoaded. And from that a Boolean Flag would identify the status of any product at any time.

If a given Product is Loaded on any machine, it should NOT be available for Loading until an Unload for that Product is received.

I think your drop down concept is a good one, but I think an Available for loading Listbox
(the concept shown in the free video I mentioned) is how I would approach it.

The key to your success, in my view, is keeping your Production rolling machines and their products in sync with your database application.

Good luck with your project.

 

[mahenkj2]

OK. I applied the list box thing and it worked perfectly. So nicely illustrated. (I targetted ~9 minutes of video and just took 20 minutes!!!!)

So, now product is available in one side only and yes it is something like I was looking at but can you advise how to go further i.e. to track multiple times tracking of loading and unloading of one product (i.e. time tracking). Your few lines of direction shall help me to move further.

Many thanks.

 

[jdraw]

From what you have said, you record the time at which a Product is loaded onto a machine - behind the scene that triggers the IsLoaded Flag to be True.
Also behind the scene, that Product is removed for the Products available to be loaded list.

Your Lists- ProductsAvailableToBeLoaded and ProductsOnMachines -

When a Product is unloaded what is the sequence of events? How does unloading the product meet up with the database?

You could have a List of ProductsOnMachines and the product would be in that List. So for unloading, you look at the ProductsOnMachines List and move it the ProductsAvailableToBeLoaded for Loading. AND RECORD THE TIME OF UNLOADING THAT PRODUCT FROM THAT MACHINE.

You review your Tables and fine the Load/Unload times for each Product. For those Products that have been Loaded and Unloaded multiple times, you must calculate the sum of the differences between each Load and Unload (Time on the machine).

You can have some internal validation routines to ensure that a Product to be Unloaded must have its IsLoaded Flag = T, it must not be in the List ProductsAvailableToBeLoaded and it must be in the
ProductsOnMachines List...

Good luck

 

[mahenkj2]

Thanks. I am giving a try.

 

[mahenkj2]

Thanks for all the support provided.

At last, I made this form and now in the process of fine tuning.

The two list box method helped me a lot, I made that a base and then in the command buttons used for updating the list box, I used INSERT INTO statement to update a table.

At last, I made it with a single form and single table.

Thanks all.

 

[jdraw]

Great. An INSERT INTO is what Access calls Append.