Tricky query

[pdbowling]

Hi all. I have a table like this

Truck...Repair.....Date......Cost
F001....Brakes.....3/1/03.....0
F001....Axle.......3/1/03.....0
F001....Bearings...3/1/03.....0
F002....Tire.......3/1/03.....0
F002....Stear......3/1/03....0

I have data that needs to go in like this
Truck....Month....Cost
F001.....3/1/03.....$100 'all dates on the first
F002.....3/1/03.....$200

I need to put the costs into the table but only in one of the rows for a given truck. It doesn't matter which row of a particular truck gets it, just that it only goes into one row so that they won't see multiples of the costs.

Example
Truck....Repair.....Date......Cost
F001.....Brakes.....3/1/03....$100
F001.....Axle.......3/1/03....$0
F001.....Bearings...3/1/03....$0

Any suggestions out there?
PB

 

[dcx693]

If you must store your data like this (I assume you have a good reason for this) why not just create a new line in your data so it's clear what it's for? Something like:
Truck...Repair.....Date......Cost
F001....Brakes.....3/1/03.....0
F001....Axle.......3/1/03.....0
F001....Bearings...3/1/03.....0
F001....Total...3/1/03.....$100

You can do that easily with an append query.

 

[pdbowling]

I'm not in control of the data I have to work with.

I've got several sources that are independent of myself and each other that would like to report off of both sets of data. 3 companies, 2 sets of data.

Thanks for the append query idea, I'll give it a go.
PB

 

[namliam]

How about this ?
Truck....Repair.....Date......Cost
F001.....TOTAL....3/1/03....$100
F001.....Brakes.....3/1/03...$0
F001.....Axle........3/1/03...$0
F001.....Bearings...3/1/03...$0

That can be accomplished using a Union query.....

Regards

 

[dcx693]

How about this ?
Truck....Repair.....Date......Cost
F001.....TOTAL....3/1/03....$100
F001.....Brakes.....3/1/03...$0
F001.....Axle........3/1/03...$0
F001.....Bearings...3/1/03...$0

That can be accomplished using a Union query.....

Regards

Isn't that like what I suggested?

 

[namliam]

Isn't that like what I suggested?

Offcourse your right, ..... except you're suggesting Append query, where you really dont need one... Union will do.... Thats my only excuse... Sorry....

 

[dcx693]

Offcourse your right, ..... except you're suggesting Append query, where you really dont need one... Union will do.... Thats my only excuse... Sorry....

Hey, no prob. Whatever works.