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 .

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...

???

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.

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.

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.

My DB fields and relationships :

http://img.photobucket.com/albums/v333/hello143/relationships_motor.gif


Can you tell me what i have done wrong here ?

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

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!

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?

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 .

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??

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.

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 .

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

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.

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.

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

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.

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 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.

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.

I can't really understand this .. Can you please explain this ? When we enter a new record every month for a particular car and employee of thier petrol usage we will have to enter all the data in petrol usage table everymonth which i don't want.

Any help ? I am new to Access so please guide me ..

You said; "In short, i want to have records of everymonth when the petrol is filled in a particular car of a particular employee."

To have a record for each month, you have to create a NEW record for each month. Otherwise you are just storing the last months usage for a vehicle without any history. I don't see much value in that.

Since you have to enter a NEW record you need at least three pieces of info as I indicated. You need to enter the date so you know what period the record is for. You need to enter an amount so you know how much was used. Whether you only store the last period's usage or a record for each period you still have to enter those TWO pieces of info. The third piece, the Vehicle Code NEEDS to be in the record. If you are only storing the last period, then you are changing value on an existing record so the Code is already there. If you are using a subform to enter usage and the main form is bound to the Vehicle table linked on Vehicle code, then Access will automatically fill it in. But if you are just entering the data directly into a form bound to the usage table, then you need to enter the Vehicle Code.

K.. Can you please check this databse and let me know if its OK to do like this. Open the form" tbl_petrol_usage_details" and check if its the right method to record petrol filled date.

LINK : http://s5.yousendit.com/d.aspx?id=18NLE0THXPM431K3P59M89JH7P

Thanks once again.

Please don't link databases externally. If you want us to check your work, ZIP it and attach to a note here.

^ here it is again.

Username = Admin
PAss= home2000

( IF It asks for it )

I'm going to try this one more time. This makes NO sense. The Petrol Usage date table makes no sense. I don't know how else I can word it.

Is the Petrol Card amount the same for every month? Again, you NEED to have the three pieces of data I listed in each record.

lets make it simple...i would simply like to enter the total petrol used every month and not record the details of when it was filled - just the total petrol usage every month... can you provide your insights on this?

I don't know what it takes to get through to you on this. You just aren't making sense. Why can't you see you need THREE pieces of info? There is no way to do this with anything less than THREE pieces. You need the petrol used, the period during which it was used and the vehicle that used it. This is the absolute minimum of info. Doing anything else is meaningless.

As I've said several times you can automate entering the VehicleCode by entering the data as a subform of a main form bound to the Vehicle table. You can also semi automate the period by using an unbound control to enter the period once and then setting the default value of the period control to that unbound control. So all you need to enter for each record is the usage amount.

ScottGem, i know i gave you a hard time during all this discussion and i am really thankful to you for bearing this with me :)

Now i have this last request for you. As i have attached the database, can you please make the changes that you think should be made to this DB. It will be easier for you and me to understand. I am sure this will be a learning thing for me. Can you please do it ?

Thanks alot ..

First there is no attachment, second, no I won't do the work for you. I've given you more than enough information to make these changes yourself. If you want to make the changes and post your results for us to review, that's fine. But I'm not going to do the work for you, unless you are willing to contract for my services.

^ There is a attachment but i understand. It won't be possible for you. I have to do this myself.

One more thing, is there anyway to do this: ?

For example, i have 2 fields " Vehicle Code" and Employee Code". I have added a new record, entered Petrol usage ID ( Auto no. ) selected vehicle and employee code from the combo box. What now i would like to do is when i enter a new record for the same vehicle and employee code, i would like employee code to be automatically selected when i select vehicle code from combo box.

I saw this in some example DBs but couldn't figure out. Is there anyway of this ?

Thanks

How are you going to enter the data? By employee or by vehicle? You should be able to minimize your data entry if you use the correct organization of forms/subforms as has already been suggested.

Employee and Vehicle will be entered in thier respective tables.

Can you please tell me how to do what i said in my last post ?

Review the info I gave you about Subforms. You do this by entering the usage in a subform where the main form is your Vehicle table. However, you can't automatically select Employee unless the employee is tied to the vehicle. In that case you can lookup the employee on the Vehicle record.

Employee and Vehicle will be entered in thier respective tables. I hope you mean forms. But that wasn't the data we were talking about. We were talking about petrol usage data. How do you forsee entering that - by employee or by vehicle? As has already been suggested, you would use a subforms to manage the data entry. For example a subform on the employee form to show vehicles assigned to that employee and then a subsubform to enter petrol usage. From the vehicle perspective, you should have only one employee assigned so you only need a subform for petrol usage entry.

Pat Hartman, I do understand you but my requirements is to do what i said in my above post. By using the subform the data would increase too much that it will be difficult to manange it as we have to scroll down every time we enter data in data entry fields.

So any help ?

Pat Hartman, I do understand you but my requirements is to do what i said in my above post. By using the subform the data would increase too much that it will be difficult to manange it as we have to scroll down every time we enter data in data entry fields.

So any help ?

That is not true. First, only the usage for the specific vehicle or Employee (depending on what the main form is) will show. Second, all you have to do is press the new record button. to get to the new record.

^ I think you didn't get me there.

Anywayz, i am attaching a sample of what i am trying to do here. Its examples of comoboxes. Goto the example of "Lookup" and when you select a "employee number", the data below it automatically comes in the fields. Thats what i am trying to do here..

Does anyone knows how to do this ?

(Password= Admin, Pass= home2000, if it asks for it)

First, all you have to do is look at the form in Design mode and you can see how it was done.

Second, That is not, however, what you want or need to do. The Lookup example (and there are several ways to do that) is useful when you want to select a value and see the rest of the info pertaining to that value. That's NOT what you need to do.

You have been asking about inputting a monthly record of petrol usage. We have told you several times in detail how to do that.