Duane -
Thanks much for all of your advice thus far. I was
able to get part of the results I wanted. It was the dates
that was throwing it off. I just made 2 tables, one with
only 2002 and one with only 2003, and that worked as far
as eliminating duplicates.
Now what I need to do is get the 2002 count of parts
and sum of IV$$ for the parts onto the report with the
2003 data. I have 3 tables. One with everything in it, and
the other two as mentioned in paragraph one. I have tried
creating relationships, but it seems to be doing some sort
of function on them. All I really need is the actual figs.
to pull over. I got all this to work fine as long as it's
formatted grouped one year above the other, but my boss
wants to see it in a columnar layout like (all one row):
Customer 2002 Part Count 2003 Part Count Part Desc.
2003 IV$$ 2002 IV$$
Any more advice would be much appreciated.
-----Original Message-----
Thanks much - will give that a try.
-----Original Message-----
There is no GROUP BY in your SQL... What happens if you try:
SELECT CMT.RECD_FROM, CMT.PART_DECP, Sum(CMT.[IV$$]) as TotalIV,
CMT.STATUS, CMT.RECD_DATE, Count(STATUS) As NumOf
FROM CMT
WHERE (((CMT.RECD_FROM) Not Like "hon*") AND ((CMT. [IV$$])
1) AND ((CMT.STATUS) Not Like "sh*") AND ((CMT.RECD_DATE)
Between #12/1/2002# And #12/1/2003#))
GROUP BY CMT.RECD_FROM, CMT.PART_DECP, CMT.STATUS, CMT.RECD_DATE;
--
Duane Hookom
MS Access MVP
Here's the SQL from the query. Maybe if I switch the query
to just do the dates by year, instead of month (he wants
to compare current year vs. prior). That way, maybe if I
hide duplicates I won't get a bunch of blank rows. It's
just a sticky wicket since no matter what, as part
description doesn't have a numeric value. It's difficult
to tie the part descriptions to their corresponding
invoiced amounts to sum up the total billed for each part
type.
Thanks again! : - )
SELECT CMT.RECD_FROM, CMT.PART_DECP, CMT.[IV$$],
CMT.STATUS, CMT.RECD_DATE
FROM CMT
WHERE (((CMT.RECD_FROM) Not Like "hon*") AND ((CMT. [IV$$])
1) AND ((CMT.STATUS) Not Like "sh*") AND ((CMT.RECD_DATE)
Between #12/1/2002# And #12/1/2003#));
-----Original Message-----
What field is actually causing the issue? If it is the
date field, can you
try DateValue([Recd Date])? It shouldn't be too difficult
to determine the
field with unique values.
Maybe you need to paste your SQL into a response.
--
Duane Hookom
MS Access MVP
message
Duane -
Thank you! I already tried the grouping by Recvd From
and Counting the part description, thinking that might
take care of it, but there must be some kind of extra
space/font issue with the data (I'm importing, not hand-
keying), because even for the same date, it's not
counting
3 instances of Widgets as Widget in the description
with 3
in the count column, but still listing Widget 3 times
with
a 1 in each of the count columns.
i.e.
Count of Part Decp Part Decp Recd Date
1 Widget 12/22/2003
1 Widget 12/22/2003
1 Widget 12/22/2003
Rather than
Count of Part Decp Part Decp Recd Date
3 Widget 12/22/2003
By grouping by date, I was hoping that it would count
the
part descp. instances for each date separately, although
there may be the same part on different days. I had some
success by just reporting it without IV$$ in the detail,
just Recd From, Recd Date, and Description, hiding the
duplicates, then adding a calculated control to sum the
IV$$, but my boss wants to see it the way I described
having the issue with. I could send it over to
Excel
and
get it into the format he wants, but this is a person
who
wants their data in a few simple clicks. Any further
advice
will be much appreciated.
Beth
-----Original Message-----
What do you expect if two similar records are on
different dates? You can
create a totals query and set the RecvdFrom to Group By
PartDesc to Group By
and IVDollars to Sum. You can also create a new column
that Counts the
PartDesc.
--
Duane Hookom
MS Access MVP
message
Duane -
Thanks for the response. An example of the info.
I've
got is:
Rec'd Date Rec'd From Part Desc. IV$$
12/22/2003 ABC Company Wrench $5.75
12/22/2003 ABC Company Wrench $5.75
12/22/2003 ABC Company Widget $6.25
12/22/2003 Smith & Jones Widget $6.25
I'd like for it to take the 2 Wrenches recieved from
ABC
Company and sum the total of the IV$$, and only show
the
company name ABC Company 1ce for each part type, no
matter
how many of the same part there may be (due to
summing
the
IV$$). i.e. end result being
Rec'd Date Rec'd From Part Desc. IV$$
12/22/2003 ABC Company Wrench (2) $11.50
12/22/2003 ABC Company Widget $6.25
12/22/2003 Smith & Jones Widget $6.25
If I need to post any further info, please
advise
& I
will
be happy to. Thanks again!
Beth
-----Original Message-----
Beth,
You talk about duplicates and hiding them etc but
only
show us one
record/row. Could you please enter a few
records
and
then
describe how this
should be presented/calculated in the report?
--
Duane Hookom
MS Access MVP
in
message
All -
The issue I am having is this - I have
report to
create, that I need to eliminate duplicate text
records
in, but sum all invoiced amounts. If I hide
duplicates
on
the part names, I get empty rows, which I do not
want.
Is
there a way to set up a query to list the part
name
just
once, as well as do a count; and further, to sum
all
the
invoice $$ for the same name parts? It needs to
bring
back
all invoiced monies for parts for 2003, without
repeating
all the duplicate part names, or giving a bunch of
blank
rows. i.e:
Received From Received Date Part Desc.
Invoice $$
Me 1/1/2003 Wrench (3)
$5.75
Please help!
Appreciatively,
Beth
.
.
.
.
.
