Transfer Data from one database to another...

[supa_sumo]

I have two databases for the running of a ATV shop, the stock lists and the sold items. Each Database contains the following fields.

Stock info:

Ledger refrence
Stock No
Location
Make
Model
2wd/4wd
Frame no
Gears
Year
Hours
Kilometres
Cost Price
Repairs
Hire income
Actual cost (=[Cost Price] + [Repairs]-[Hire Income])
Estimated Sale Price
Balance (=[Estimated Sale Price]-[Repairs]-[Cost Price]+[Hire Income])
Picture

Sold Items:
Ledger refrence
Stock No
Make
Model
Frame no
Cost Price
Repairs
Balance (=[Sold Price]-[Repairs]-[Cost Price])
Sold Price
Month

I need a button that Is the bikes sold button that would transfer all data from one database and paste into another some fields do not exist in the Sold items database therefors should not be added. There is also some extra fields with data that needs to be added(Month Sold Price). How can I do this?

Thanks for your help

 

[Mile-O]

Why are you storing calculated values?

Also, some of the field names you have a reserved words and are not recommended for use in Access (Month, Year, etc.) Infact, if you store the date these things occur then you don't need these fields at all as you can easily get the year or month from the date.

Make & Model sound as if they are better off cascaded which would mean you only need to store the bottommost of this hierarchy in your table.

Does the cost come from kilometres multiplied by hours? If so, you don't store the cost.

 

[Rich]

You don't need two databases either

 

[supa_sumo]

This database is for the second hand ATVs

The calculated values are wanted by the user to work out the value that they can sell the bike for they are not being stored they are worked out in the form and in a query for a datasheet print(sorry if i misswrote the question)

Month is the month that the bike was sold in for the taxman
year is the year that the bike was created in
Cost is the money that the bussiness paid for the bike
Kilometers are the number of kilometers that the bike has done
hours is the number of hours work the bike has done

(Year, Hours and Kilometers come from the bike when bought, data that will be placed on the website.)

Sorry I should have made it clearer

 

[Mile-O]

You get four wheel drive bikes these days?

 

[supa_sumo]

ATVs like quads or farm bikes

 

[Mile-O]

Sorry, I thought ATV was something I watch the football on.

 

[Mile-O]

Also, reading your original post, do you mean that you have two databases or two tables in the one database?

 

[supa_sumo]

Yeah sorry bout that too I did mean 2 tables, I had a really heavy night and I had to get up at 7 today to go to school, not good

 

[Mile-O]

Can you explain to me the different fields in the tables and I'll make you a proper structure as, as it stands, the tables are not normalised.

You shouldn't store the same details in more than one table. What you have done is created a flat structure which won't work.

 

[Rich]

and it would be more fun if your quad only had two wheels

 

[supa_sumo]

For the Bikes in stock database

Ledger refrence: Location of bike information in a Leger book (paper based backup)
Stock No: Self explanatory,each bike is given a stock number as it is enterd into the database
Location: Location of the bike i.e. workshop, warehouse, On Hire, Showroom
Make: Make of Bike i.e. Honda Suzuki Polaris Yamaha.
Model: Model of bike
2wd/4wd: Whether they are 2 or 4 wheel drive
Frame no: The Number printed on the frame of the Bike
Gears: Electric, Manual or Auto
Year: Year Bike was made
Hours: hours of work bike has done
Kilometres: Kilometers bike has traveled
Cost Price: The amount the bike cost the bussiness to buy in.
Repairs: Cost of reapirs
Hire income: Amount of money made from the bike being on hire
Estimated Sale Price: Amount that the bike should be sold for
Picture: Not sure here wether to put a OLE object as the picture or the location that the picture is stored, It will later be used on a website but I have never done that before.

Added in a query for printing and in the a form for user too see:
Balance: [Estimated sale price]-[Actual Cost]
Actual cost: [Cost Price]+[Repairs]-[Hire Income]


For the sold bikes database:

Ledger refrence: Moved from Other Database
Stock No: Moved from other database
Make: Moved from other database
Model: Moved from other database
Frame no: Moved from other database
Cost Price:Moved from other database
Repairs:Moved from other database
Sold Price: Amount bike was sold for (Entered When the bike sold button is pressed)
Month: The month the bike was sold (Entered When the bike sold button is pressed)


Added in a query for printing and a form for viewing
Balance: [Sold Price]-[Repairs]-[Cost Price]


The same data will not be stored in the same database, when the bike is sold the data that the bussiness wants to keep is stored in a different table

 

[Mile-O]

Excluding the picture (which I wouldn't store in a database), here's my suggested structure: database and picture attached.

Everything else can be done in a query.

 

[supa_sumo]

Ive just seen it, couldnt download zip files in school, (damn filter) seems to make sence tho the form seem a little mor complicated to do but ill have a pop. TY for that

 

[Mile-O]

The reason I made repairs a seperate table is because one bike can, over time, have more than one repair. I made a table tblRepairTypes in which you can list the different types of repairs/services that it has had and the cost to do so. Using a query you can get all the repairs that have happened to a single bike and sum the cost field to get the total repairs. It's the same principle with the hires in that the bike can be hired many times so you store the details about that, the money received from hiring it, and the kilometres done whilst on hire. This way, again, you can sum the cost and kilometres. You can also, query the dates to determine how many times it has been hired, for example, over a period of time.

 

[supa_sumo]

How bout the sold bikes being moved to another table or something, just run a query looking for all the bikes with a sell price the easiest thing to do u rekon? Shouldnt the bikeID be linked to the BikeID in the tblReapairs not to the repairID?

 

[Mile-O]

How bout the sold bikes being moved to another table or something, just run a query looking for all the bikes with a sell price the easiest thing to do u rekon?

You could have another table if you want to write APPEND and DELETE queries to manage the transition ans ensure that the relationships are set up with that table too although it may become a little complicated.

If the default value of the Sold field is 0 then you can use a query to eliminate all those not equal to 0 to find out the sold ones. Inversely for those unsold; in my opinion its less problematic.

Shouldnt the bikeID be linked to the BikeID in the tblReapairs not to the repairID?

Well spotted. Same goes for the RepairTypeID between tblRepairTypes and tblRepairs