help with a query

[Guest]

My database is filled by a form that enters salesmen names, date of sales,
and state of sales. I have a two fold problem. Is it possible to write a
query that gives me the date of first sale and last sale for each salesman?
Then is it possible to have all states of sale put in the same column?

Form enters:
Salesman SalesDate State
1 3/ 3/2001 Texas
1 3/4/2001 Maine
1 3/5/2002 Texas
2 3/7/2002 Missouri
2 3/9/2002 Missouri
2 3/10/2002 Texas
3 4/1/2002 Illinois
3 4/2/2002 Texas
3 5/5/2002 Texas
3 5/6/2002 Missouri

I would like the resulting query to look like:

Salesman FirstSale LastSale SaleStates
1 3/3/2001 3/5/2001 Texas, Maine
2 3/7/2002 3/10/2002 Missouri, Texas
3 4/2/2002 5/6/2002 Texas, Missouri

Thanks for any help. Or is it even possible?

Rebecca

 

[Guest]

Me again, I do not understand that since Microsoft treats dates as numbers,
why cannot I query for MIN (salesdate) and MAX (salesdate)?

Also to simplify my table I may have been misleading in that I used (state).
there are an unlimited number of states in my database (not just 50). So I
cannot set up a table

sales
states

Thanks again for any help.

 

[Marshall Barton]

Me again, I do not understand that since Microsoft treats dates as numbers,
why cannot I query for MIN (salesdate) and MAX (salesdate)?

Also to simplify my table I may have been misleading in that I used (state).
there are an unlimited number of states in my database (not just 50). So I
cannot set up a table

Min and Max are the right way to get the earliest and latest
dates. What are you getting that makes you think it dosen't
work? Note that Access stores Date/Time values as a Double,
but that does not mean that you can't put something that
looks like a date in an text field.

You can use the the function at:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
to combine the states into a single field.

If you need further assistance, please include a Copy/Paste
of your query's SQL statement.

 

[Guest]

When I try to open the url in your post, I get:
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32


When I saved my query and try to run it, I get:
Cannot have aggreate function in WHERE clause (testing2.[First
Date]=Min("seen"))

testing2 is my database. Or was, after 12 hours, I deleted everything a
will try and tackle the problems tomorrow. I will have to reproduce the sql
tomorrow.