Transfer data from table or query to second table

[plihu]

Hi,

I have a problem, which can be bery easy to solve, but I have no idea how to do that! Please see example below:
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them
I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tommorow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
Do you have any idea how to solve this problem?

Thanks for help,
Michael

[rpeare]

How detailed is your engine information, for instance, engine A and engine B could be 'diesel engine', 'conventional engine', or it could be '1996 Toyota Corolla', '1998 Toyota Corolla'.
What you're talking about is a basic inventory management application.

I think I would have something like this:

1. A table for employees with a PK (primary key, autonumber works fine)
2. A table for the different types of engines you receive with a PK (again autonumber works fine)
3. A table for engine receipts with a PK (autonumber again) each engine received would receive it's own record, so if you received 15 engines today, each engine would be cataloged separately, this allows you to audit individual engines if it's necessary. This table would have a FK to the engine types table, the date the engine was received, and any other information related directly to the engine that has been received, for instance a basic description of the problem and/or initial assessment of what needs fixing.
4. An engine repair log table with a PK (autonumber again), this table would contain the employeeID, what work they did, for instance if you have a regular list of services like 'engine flush', 'replaced serpentine belt' you could log what the employee did. If it's not that detailed and you are just recording date the employee worked on the engine and an estimated percentage of the work done that's much simpler to deal with that's all this table would contain.

If you are strictly talking about being able to do data entry the way you're talking about it makes me thing you're using some sort of aggregate query and there is no possible way to data entry on an aggregate query, MS access would have no ability to discern which record of many to apply any update to.

[plihu]

Thank's for your tips, they were very usefull!
To be honest, I'd like to avoid situation that every engine has his own record, becouse there are hundreds of engines beeing suliedevery day and I'm affraid that this base would become huge and slow very soon. I'd like rather to treat 50 engines A that has been suplied today as a "pack". Please see attachment with relations.

That's what I've figured out: in tblEmloyees there're all employees info, tbEngines will be to register all engines that workers can repair, tblTypesOfEngines - just additional info which describes tye of engine (electric, internal combution etc.), and tblEnginesRepaired will be for workers to register repaired engines, and that's how it works:
Every employee at the end of a day will be register today's date, repaired engines ID and quantity of repaired engines (so if worker A has repaired today 5 engines A and 5 engines B - there will be 2 records for him in tblEnginesRepaired)
So now I have a problem with registering received engines and I can't even imagine how to solve that
I can't add column "Incoming" to tblEnginesRepaired, because 2 different workers can repair the same engineID the same day.
Besides that different engines have different deadlines to be repaired - Eg. engines A have to be repaired max 5 days after they've been supplied to the factory - so I'll need a report, if all deadlines are achieved.
Besides that I have to assume that not every engineID is supplied every day - so I need information on my report that 0 has arrived.

Any more tips?

[rpeare]

The basic structure I gave you will still work but you'd have to think about it slightly differently.

If you have multiple 'suppliers' (people who send you engines to be repaired) you'd have a supplier table as well then when they sent you a shipment you could record the supplier, the number of engines sent, the date you received the shipment.

Then when recording the 'fixes' you would have a separate table, one with the employee ID, the FK to the shipment table, the number of engines fixed and the date of the fix.

so let's say you received 50 engines from supplier X on 1/1/2014 (you may want to record a receipt time as well if you need to measure 5 days exactly from receipt to completion)

then at the end of the day person A reports repairing 4 engines, Person B reports repairing 5 engines. you can enter two different lines in your database, one for employee A, one for employee B. The only thing you have to consider is if both employees are claiming to repair the same engine and whether or not you're going to give partial credit to both. Your current structure is flawed in that you are likely using a description (text value alone) in your tblTypesofEngines which is a bad practice. A table should always have a PK that is not subject to the data within it, something that will not change over time. And unless you receive nothing but shipments of identical engines your design of tblEnginesRepaired is likewise flawed in that you are linking to tblEngines. this structure makes me think you are attempting to log individual engines in tblengines but make them part of a group in tblenginesrepaired which your structure does not support.