Re: Dont know where to start

On Jun 15, 7:46 am, stephenruss...@xxxxxxxxx wrote:
On Jun 14, 11:27 pm, "Neil" <nos...@xxxxxxxxxx> wrote:





OK, putting his data, below, in a table, and calling the fields Field1,
Field2, etc., and running your SQL:

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

produces the following results:

Range Field2 Field3
1 - 6 1 Cooper Street
2 - 7 2 Cooper Street
10 - 3 3 Cooper Street
10A - 4 4 Cooper Street
5 - 5 5 Cooper Street

The results he was looking for were:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Even considering that you said he could add additional fields (for columns 4
and 5) the two results sets are clearly not the same. At the very least, one
has five records, the other has two. But the data is also not correct. You
still say your query produces correct results?

Neil

"DavidB" <j...@xxxxxxxxx> wrote in message

news:1181844318.835750.219380@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jun 14, 1:24 pm, "Neil" <nos...@xxxxxxxxxx> wrote:
Your query doesn't produce the results he was looking for.

"DavidB" <j...@xxxxxxxxx> wrote in message

news:1181841214.506410.179090@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jun 14, 10:06 am, stephenruss...@xxxxxxxxx wrote:
Ok i what i am trying to do is:

I have a query that pumps out information like this:

(1) (2) (3) (4) (5)
1 1 Cooper Street NS Johnson & Dale
2 2 Cooper Street NS Johnson & Dale
3 3 Cooper Street NS Johnson & Dale
4 4 Cooper Street NS Johnson & Dale
5 5 Cooper Street NS Johnson & Dale
6 1 Cooper Street SS Bail & Bob
7 2 Cooper Street SS Bail & Bob
10 3 Cooper Street SS Bail & Bob
10A 4 Cooper Street SS Bail & Bob

Ok so i have information that looks something like this. What i am
trying to do is have excel open the database and show the information
like this:

1 - 5 Cooper Street NS Johnson & Dale
6 - 10A Cooper Street SS Bail & Bob

Is it possible to have excel do this?

SELECT Min([Field1]) & " - " & Max([Filed1]) AS Range, Table1.Field2,
Table1.Field3
FROM Table1
GROUP BY Table1.Field2, Table1.Field3;

Add fields as necessary- Hide quoted text -

- Show quoted text -

Sure it does- Hide quoted text -

- Show quoted text -

Neil can you explain your SQL to me. Like explain what your code
does. I am having troubles putting it to use and i wanted to make
sure i was not missing anything.

Thanks- Hide quoted text -

- Show quoted text -

ok so i used this data as a sample

Field1 Field2 Field3 Field4 Field5
1 1 Cooper NS Jell & Bell
2 2 Cooper NS Jell & Bell
3A 3 Cooper NS Jell & Bell
4 4 Cooper NS Jell & Bell
5 1 Cooper SS Bob & Free
6 2 Cooper SS Bob & Free
7 3 Cooper SS Bob & Free
10 4 Cooper SS Bob & Free
12 5 Cooper SS Bob & Free
13A 1 Frank SS Bob & Free
13B 2 Frank SS Bob & Free
14C 3 Frank SS Bob & Free


And this is what i got from query 2

Expr1 Field3 Field4 Field5
1 - 4 Cooper NS Jell & Bell
13A - 12 Frank SS Bob & Free
13A - 12 Cooper SS Bob & Free
5 - 12 Frank SS Bob & Free
5 - 12 Cooper SS Bob & Free

Help?

.

Relevant Pages

  • Re: Combo-Box list unsorting
    ... RecordSource property and select it. ... it'll take you to the query. ... drag-dropping fields from Table1 to the Form. ... want one of the fields (say Field2) in Table1 and the Form. ...
    (microsoft.public.access.gettingstarted)
  • Re: How do I show all in a count query?
    ... you will have to do this in the SQL view of the ... of a new query. ... FROM Table1 ... "Joseph C." wrote: ...
    (microsoft.public.access.queries)
  • Re: Calculated Mean
    ... FROM Table1 AS T ... "Charles" wrote in message ... > I copied your statement to SQL screen again but got the same error ... On the Query screen, I clicked New, ...
    (microsoft.public.access.queries)
  • Re: how to create a function w/in a query?
    ... Table1, with fields a,b,c ... Table2, with fields h,i,,j ... I want a query that does something like this: ... SQL queries... ...
    (microsoft.public.access.queries)
  • Re: Dont know where to start
    ... FROM Table1 ... Range Field2 Field3 ... field2 field3 in the query nit just carte blance cut and paste! ... SQL, but should use other fields instead. ...
    (comp.databases.ms-access)