Thank you for replying Lionstone.
I'm not trying to find out how many addresses there are, I'm
simply trying
to display a dynamic menu of addresses that doesn't repeat an
address, based
on whether there are two, or more, identical postcodes in the
recordset that
feeds the menu. In literal terms, saying "this postcode
appears twice, or
more, in this recordset, so I'll only show one of them".
SELECT * FROM orderstable
GROUP BY postcode
This produces an error so I've tried including all of the
fields, as you
suggest, in the GROUP BY clause:
SELECT orderID, customerID, address, city, postcode
FROM orderstable
GROUP BY postcode, orderID, customerID, address, city
However, this is still showing a duplicate? It isn't
producing an error
though, so I guess that's progress - of sorts! :o)
Is GROUP BY even what I need then? Your expert advice would,
again, be
greatly appreciated - oh wise one! :o)
Regards
Nath.
"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in
message
news:e8bffm$me4$1@forums.macromedia.com...
> GROUP BY doesn't work the way you're thinking. It does
not create a group
> such as you'd get with Crystal Reports, with a header
and detail rows. It
> doesn't give you groups of results, it turns your
results into groups. In
> your case, grouping by postal code, you will end up with
a list of postal
> codes. You could then count, sum, average, etc, to your
heart's desire to
> find out how many addresses are in the post code, etc,
but you cannot
> include ANY information from those columns unless you
also group by those
> columns.
>
> In a nutshell, when you have a GROUP BY clause in your
query, all columns
> in the SELECT clause must also be in the GROUP BY clause
or a part of an
> aggregate function (SUM, MIN, MAX, AVG, COUNT, etc).
>
>
> "Nathon Jones" <sales@NOSHPAMtradmusic.com> wrote
in message
> news:e8bbif$har$1@forums.macromedia.com...
>> Hi. Hope someone can help with my SELECT statement.
>>
>> I have a customers table:
>>
>> customerID
>> customername
>> address
>> ...etc
>>
>> An orders table:
>>
>> orderID
>> customerID
>> deliveryaddress
>> deliverypostcode
>> ...etc
>>
>> A customer can obviously submit several orders and
each of these orders
>> may have different addresses. I want to be able to
provide the customer
>> with the option to auto-complete the order form by
choosing a previously
>> used delivery address from a drop down menu on the
order form page.
>> However, I would like to GROUP the delivery
addresses by postcode so that
>> the same postcode (address) doesn't appear twice in
the menu.
>>
>> The problem I'm having is that I can only use GROUP
BY on a recordset
>> that contains only one field - the field that I
group by (the postcode!).
>> Yet I need ALL of the delivery address information
in order to fill in
>> the form.
>>
>> I am very new to GROUP BY and someone also mentioned
using DISTINCT but
>> I'm not sure how to do this.
>> I understand that I can create a query in my
database that uses an INNER
>> JOIN to take related data from two separate
tables/queries, but I'm not
>> entirely sure if that will work either on a GROUP BY
or DISTINCT.
>>
>> Hope someone can point me in the right direction!
Thanks.
>> Regards
>> Nath.
>>
>
>
6
Replies
AdChoices