Append/Update querie

[Guest]

I have a table with the values as follows. Note that thereare multiple
entries for each client ID.
ClientID DropOff Farmer PreSchedule Duration
226 0
226 1
226 0
227 0
227 0
227 0
227 1.00
228 3.00
228 2
228 0
228 1
229 0
229 1
229 0
229 0.00
230 2.00
230 2
230 0
230 0
231 0
231 1
231 0
231 0.00
232 3.00
232 2
232 0
232 1
238 0
238 0
238 2
238 1.50

I am trying to do an append query that will take the information above and
put it in another table as follows:
ClientID DropOff Farmer PreSchedule Duration
226 1 0 0
227 0 0 1.0
228 0 1 2
ETC

Any suggiestions on how I can make this happen??

 

[OfficeDev18 via AccessMonster.com]

Use an append query grouping by ClientID and using the Max() and Nz()
functions on the rest, because you have a lot of null data in the table. It
would look like this:

INSERT INTO NewTable (ClientID, DropOff, Farmer, PreSchedule, Duration)
SELECT ClientID, Max(Nz(DropOff,0)) As MaxDropOff, Max(Nz(Farmer,0)) As
MaxFarmer,
Max(Nz(PreSchedule,0)) As MaxPS, Max(Nz(Duration,0)) As MaxDuration
FROM OldTable
GROUP BY ClientID;

Copy and paste the above SQL statement into the Access Query SQL design
window, and change the names of OldTable and NewTable with the names of your
real tables. Try running the query and see what happens. Before you actually
run it for real, of course, display the results in datasheet display.

HTH

 

[Guest]

Thanks! That did exactly what I wanted it to do!!