I know how this must sound but....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dazzed - 15 Sep 2006 02:03 GMT

I have a rather small table( 2204 records ) at the moment. We need to
generate a report that will spit out some prices that are totals based
on these records. So far so good and this query works too. Most of the
time....

SELECT tbl_a.addr, tbl_a.[Cust Name], tbl_a.PN, tbl_a.[Unit
Description], SUM(tbl_a.[Number of Units]) AS TotalUnits,
SUM(tbl_a.TotalPrice) AS TotalPriceOfUnits
FROM tbl_a
GROUP BY tbl_a.addr, tbl_a.[Cust Name], tbl_a.PN, tbl_a.[Unit
Description];

As the data has been entered we noticed that only in a few instances
have records not been grouped and as such summed.
Ultimatly what I need to have happen is the report spit out a list of
each towns customers listed with any records for them grouped and
summed up in a nice neat package what we are getting though is this and
then a few customers later another listing for the same customer again.
I have tried to recreate this behavior in another db but for the life
of me I dont know how its happening or why!

Would appreciate any help you folks out there could offer.

Thanks

Reply to this Message

Smartin - 15 Sep 2006 02:34 GMT

> I have a rather small table( 2204 records ) at the moment. We need to
> generate a report that will spit out some prices that are totals based
[quoted text clipped - 20 lines]
>
> Thanks

Are there differences (albeit slight ones, possibly) in
tbl_a.addr, tbl_a.[Cust Name], tbl_a.PN,
tbl_a.[Unit Description]
that cause undesirable breaks? You know, an extra space here or there, a
different abbreviation of "street", "John Smith" Vs. "Jon Smith", or
some such?

If you want to summarize by town as well, you will need to split up
"addr" into atomic address units and group accordingly.

Signature

Smartin

Reply to this Message

Dazzed - 15 Sep 2006 21:58 GMT

> > I have a rather small table( 2204 records ) at the moment. We need to
> > generate a report that will spit out some prices that are totals based
[quoted text clipped - 33 lines]
> --
> Smartin

That was one of my first thaughts as well. Unfortunatly there are no
chances at this point for variation in those strings. They are used so
often in made more sense to give them a table unto themselves so they
are indexed by value on a form and then pulled into the record by
query. The other thing I was thinking was that we may have ended up
with another customer with the same name some how mimicing closely but
jsut slightly off that we thaught was the same but was not this too
turned out not to be the case. In the end these records really should
be grouping and just wont. Have to admit at this point I'm getting a
bit frantic.

Reply to this Message

David Cox - 15 Sep 2006 22:08 GMT

Just a thought, but sometimes a leading blank can be very hard to spot.

You could try a Trim() and see if it makes a difference.

>> > I have a rather small table( 2204 records ) at the moment. We need to
>> > generate a report that will spit out some prices that are totals based
[quoted text clipped - 44 lines]
> be grouping and just wont. Have to admit at this point I'm getting a
> bit frantic.

Reply to this Message

Dazzed - 15 Sep 2006 23:45 GMT

> Just a thought, but sometimes a leading blank can be very hard to spot.
>
[quoted text clipped - 48 lines]
> > be grouping and just wont. Have to admit at this point I'm getting a
> > bit frantic.

Well all here I sit with egg on my face. Let my embarisment be the
lesson for us all. In the end no matter how hard you try a crafty user
will find a way to foil your idiot proof forms. In my case someone must
have skiped the form and attempted to enter data directly on the tables
as I did end up finding additional white space and in another spot an .
that was not in the table. Thank you both for your prompt response I
hope to return the favor one day.

Reply to this Message

Smartin - 16 Sep 2006 00:28 GMT

>> Just a thought, but sometimes a leading blank can be very hard to spot.
>>
[quoted text clipped - 55 lines]
> that was not in the table. Thank you both for your prompt response I
> hope to return the favor one day.

Thanks for the follow-up. As a novice developer I have to keep reminding
myself that no matter how hard I try to make an application fool-proof,
/somebody/ will find a way to fool me...

Signature

Smartin

Reply to this Message

David Cox - 16 Sep 2006 08:25 GMT

>>> Just a thought, but sometimes a leading blank can be very hard to spot.
>>>
[quoted text clipped - 64 lines]
> myself that no matter how hard I try to make an application fool-proof,
> /somebody/ will find a way to fool me...

As a seasoned novice (they keep changing the rules) I have so many scars
that itch ...

David F. Cox

Reply to this Message