Re: Dont know where to start


"DavidB" <jebva@xxxxxxxxx> wrote in message
news:1181929543.391051.266830@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 -

Well dumb ass you are suppoed to use the fields you want for field1
field2 field3 in the query nit just carte blance cut and paste! If
you cant figure that much out you have no business posting help in here

So why don't you give us an example of the query you'd use to obtain the
results he requested (1-5 Cooper Street, 6-10A Cooper Street). You post a
query that doesn't work, but you insist it does work. But you refuse to show
the query that you claim does work. That's because it doesn't. Period.

The data's right here:

(Field1) (Field2) (Field3)
(Field4) (Field5)
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

Your original post to Stephen was:

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

Add fields as necessary"

You are now saying that he shouldn't use Field1, Field2, and Field3 in the
SQL, but should use other fields instead. So why don't you just post the SQL
you'd use to get these results:

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

instead of just continually saying, "It works, it works," while it doesn't
work. Just post the SQL that actually works and you'll prove me wrong. But
since you can't do that (since it doesn't work, even if you change the
fields around), calling me names and just saying "It works" means absolutely
nothing. Desperate men resort to name-calling and abuse when they have no
rational argument.

Neil


.

Relevant Pages

  • 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)
  • Statistics IO logical reads sometimes 15 million and other times down to 90 thousand?
    ... I am running a query in SQL 2000 SP4, Windows 2000 Server ... This 'TABLE1' has about 400,000 records ...
    (comp.databases.ms-sqlserver)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
Loading