Total query question

[Markvand]

Hi all,

I want to list only the latest records, thus I have total query, I'm playing around with this but somehow it doesn't work:

I have 3 fields,

print_id - PM in a table and autonumber
date
time

I have a group by in print_id and max in date and time

Please help

 

[Brianwarnock]

The problem is caused by the fact that your date and time are in separate fields the max time will not necessarily be in the rec with the max date

brian

 

[Markvand]

Brian,

Thnx for reply, I've put date and time in one colummn: date() &" "& time()
and still nothing, I don't know if this is a way.

Any other suggestions are more than welcomed

Mark

 

[Jon K]

[Date]+[Time]


It's better not to use Date and Time as field names. They are built-in function names.
.

 

[Markvand]

G'day Jon,

I've put date() + time(), and still can't limit the results only to latest date.
I'm getting all the records sorted from the latest to the oldest, and what I want to have is only the lastest.

The thing is I am using an append query where I'm assigning autonumber to each of item_id and date when the item was added to table. The problem is with sets though, a set includes a number of item_id. When I run an append query and I choose a set, it adds me few item_id's - exactly how I wanted, but when I run a query to list it by the latest date it just doesn't work.

Hope I'm clear enough here

 

[Brianwarnock]

Nope , I'm sorry but it's as clear as mud to me.
Jon expected you to enter into the qbe grid exp1:[date]+[time] where date and time are your field names hence his comment regarding the unsuitability of such names, if you are coding date() and time() it looks like you are using the functions, is during the append query? NOW() would put date and time into 1 field.

If your table has 3 fields item_id date time then your query will groupby item_id and select max of [date]+[time].

If you don't want date and time in the one field then you can run 2 queries
query1 group on item_id [time] and max [date]
query2 takes query1 and group on item_id [date] and max [time]

Brian

 

[Markvand]

Finally got it work,
Thanks a lot guys, much apreciated

Cheers