transfer data to other tables ? (2 Viewers)

[Cyberslam]

I am creating a Transport databse and got stuck into something.

I have created a master table " Vehicle Details " which inlcudes all the details of cars. Then i created two sub-tables " Vehicles in Garage" and " Vehicles assigned to Drivers". I will be using the forms ( ofcourse ) to enter and edit data. What now i want to do is from a master table i want to tranfer all details of a particular car either to " Vehicles in Garage" or " Vehicles assigned to drivers" tables/forms.

Is this possible ?

Thanks

P.S. I hope this is the right section for this .

 

[KenHigg]

Hum...

Having duplicate places to store data seems a bit 'Un-normalized'.

I would tend to think more along the lines of having a fld in the master table that would store the status or location of the car. Maybe you store "Garage" or "Driver" or maybe "Unasigned". Then instead of moving all that data around when a car goes from the garage to a driver, you simply change this fld...

???

 

[ScottGem]

I agree with Ken, but I can see situations where you might want to store more info about the assignments. For example if you want to maintain a history of the vehicle. In that case I would use a table like this:

tblAssignments
AssignmentID (PK Autonumber)
VehicleID (FK to master vehicle table)
AssignedToID (Fl to a table listing all the garages and drivers a vehicle might be assigned to)
AssignmentDate

I would then have a mainform/subform with the mainform bound to the master Vehicle table and the subform bound to tblAssignments linked on VehicleID. When a vehicle is assigned you add a new record indicating where its been assigned to and the date.

 

[Cyberslam]

Thanks KenHigg and ScottGem for replying .

This is what i want to do .

I want to add the petrol usage details( will be a different table ) of cars which are assigned only not in garage. In the petrol usage details i only need to use 3-4 fields from master table ( Vehicle Details )

Is there anyway of doing this ? I tried doing it by using query but it doesn't work or maybe i am doing something wrong.

 

[ScottGem]

You are missing the point. You do NOT need to use ANY fields from the master table in any other table except for the VehicleID as a foreign key. Any other fields can be pulled using a Join. Without seeing the SQL of your query its hard to tell what you may have done wrong.

 

[Cyberslam]

My DB fields and relationships :

Can you tell me what i have done wrong here ?

 

[Cyberslam]

Here is the link to DL my DB.

http://s32.yousendit.com/d.aspx?id=0TG8QRERJNJUI1SMQKLU9WTD1L ( username Home , password : home2000 , if it asks for it )


Now, as you can see i have 3 main tables ( vehicle details, employee details, petrol usage details ). The only thing i want now is that in petrol usage details table/ form only that vehicles should appear which are assigned( they will appear in Combo Box ). I don't want to sum anything in petrol usage details table. Its just for updating purpose. Can you please guide me in this ?

If there is any other way of doing this then please let me know.


Thanks

 

[KenHigg]

Hum...

You should be able to remove veh type, brand, model and emp name from the usage table.

This is the essence of a relational database!

 

[ScottGem]

A few things. You usage table should look like this:

PetrolUsageID (PK autonumber)
Vehiclecode (FK)
EmployeeCode (FK)
PetrolCardAmount
PetrolFilledDate
PetrolAllowance

All the other fields do NOT belong in that table (and I'm not sure about Allowance). The FK fields are used to get the other data from the table they are an FK to.

I'm not sure I understand exactly what the usage is recording, can you explain further?

 

[Cyberslam]

ScottGem, do i have to create a relationship between usage table and employee and vehicle table ? What does FK stands for ? Are you saying that in usage table data will be taken from employee and vehicle table ( vehicle and emp code ) in the form of combo box ?

usage will be used to record the petrol usage of a car ( which is assigned only ) /month .

 

[Cyberslam]

also, how can I make a provision for entering the petrol amount for every month? say I have entered petrol detail for this month for all assigned vehicles, how do I do the same for next month without losing the record for this month??

 

[ScottGem]

FK stands for Foreign Key. If usage is recorded onyl monthly, then I would have a record for each month per vehicle in the usage table. Yes, you need to create relations between Usage and Vehicle on VehicleID and usage and Employee on EmployeeID.

If you want to display more info about the vehicle and/or the employee on the form where you enter the usage, you can do that in a variety of ways. But I don't think its necessary. I would have 2 combos on that form with RowSources like this:

cboVehicle: SELECT [Vehicle Code], [Brand] & "/" & [Model] & "/" & [Year] AS Vehicle FROM tbl_Vehicle_Details;

cboEmployee: SELECT [Employee Code], [Employee Name] FROM tbl_Employee_Details;

Some other points. you should not use spaces in object names, thei will come back to haunt you in the future. Year is a reserved word so you shouldn't use that as an object name, use ModelYr instead. You shouldn't use a single field for a person's name. At the least use a last and first name.

 

[Cyberslam]

ScottGem, how would i have a record for each month per vehicle in the usage table ? I mean how can i do that ? Sorry i am new to Access so please help me out.

And how will the spaces in object names ( u mean field names, right ? ) will haunt me in the future ? Can't find anything wrong with it yet .

 

[Pat Hartman]

In addition to having duplicated columns as has already been mentioned, you are missing a table. You need a table to define the assignment of a vehicle to an employee. Which you already have. But the table name should be tbl_Employee_Vehicle. Is the Petrol Card assigned to the empoyee or to the employee for a specific vehicle? So if for some reason, the employee had a second vehicle assigned to him, would he have a second Petrol Card? I suspect not. That means that the PetrolCardCode needs to move to the employee table. Fields I would add to the employee vehicle table are AssignedDate, ReturnedDate, StartingMilage, EndingMilage.

The new table will be a transaction table. Each time the employee puts gas in the car, he would log the amount, cost, and milage reading.
tbl_Petrol_Usage
PetrolUsageID (PK autonumber)
Vehiclecode (FK)
EmployeeCode (FK)
PetrolAmount
PetrolCost
PetrolFilledDate

 

[Cyberslam]

Pat Hartman, thanks for replying.

But i am still confused about that PetrolEntry Date. As the petrol usage has to be recoreded every month then in the "PetrolEntryDate" record of the previous month has to be recored when the record of the new month is added. I want to add new record in PetrolFilledDate without loosing the old records. In short, i want to have records of everymonth when the petrol is filled in a particular car of a particular employee.

 

[ScottGem]

Pat Hartman, thanks for replying.

But i am still confused about that PetrolEntry Date. As the petrol usage has to be recoreded every month then in the "PetrolEntryDate" record of the previous month has to be recored when the record of the new month is added. I want to add new record in PetrolFilledDate without loosing the old records. In short, i want to have records of everymonth when the petrol is filled in a particular car of a particular employee.

I'm not sure I understand what your confusion is here. When you enter information for each month, just select a new record. If you follow our suggestions for structure This should not be a problem Each new Usage record has its own (autonumber) Primary key. Each record should be defined by a date (either month beginning or month end, whichever makes more sense to you). Each record is also defined by selecting the Vehicle Code from a combobox.

As for defining the record for a particular car of a particular employee, does only one employee use a car during the month? If not, then you have some problems with your work flow that need to be dealt with. I would record EACH and every fill up rather than a motnhly total, in that case.

 

[Cyberslam]

I'm not sure I understand what your confusion is here. When you enter information for each month, just select a new record. If you follow our suggestions for structure This should not be a problem Each new Usage record has its own (autonumber) Primary key. Each record should be defined by a date (either month beginning or month end, whichever makes more sense to you). Each record is also defined by selecting the Vehicle Code from a combobox.

I do understand that but by doing that i have to enter all the required info in petrol usage table every month when i update petrol usage. Is there anyway by which i only have to update the petrol filled date ? I tried that by having Petrol filled date as a subform but for some reason its not working .. The form come blank ?

Any help regarding this ?

Thanks

 

[ScottGem]

I do understand that but by doing that i have to enter all the required info in petrol usage table every month when i update petrol usage. Is there anyway by which i only have to update the petrol filled date ? I tried that by having Petrol filled date as a subform but for some reason its not working .. The form come blank ?

Any help regarding this ?

Thanks

You have to enter at least three pieces of data here; the new filled date, the amount that was filled and the vehicleID. If you are using a subform where the main form is bound to the Vehicle table, then the last piece is automatic.

 

[Cyberslam]

I want to bound "Petrol filled date " field to Petrol usage tabel. So i just have to enter the filled date instead of entering all records in petrol usage table when entering new record of a particular car every month.

 

[ScottGem]

I want to bound "Petrol filled date " field to Petrol usage tabel. So i just have to enter the filled date instead of entering all records in petrol usage table when entering new record of a particular car every month.

I don't understand. Please reread my last post.