Eliminate repeat values in one field

  • Thread starter Thread starter jimbosdad
  • Start date Start date
J

jimbosdad

I am trying to create a query whereby values in a "Stock On Hand" does not
repeat in every record each time stock is sold on different and multiple
dates. Please see examples below. This also impacts the summary totals for
each numeric column. Thanks so much for any guidance you can provide.

DESIRED:
Stock Stock On Transaction
Date Type Hand Sold Date Sale Number

6/30/07 CO1 100 10 4/3/07 01
15 6/2/07 02
20 6/4/07 03
6/30/07 CO2 100 10 4/3/07 01
15 6/2/07 02
05 6/4/07 03

6/30/08 CO1 150 35 11/7/07 01
20 12/30/07 02
45 10/14/07 03

NOT DESIRED:
Stock Stock On Transaction
Date Type Hand Sold Date Sale Number

6/30/07 CO1 100 10 4/3/07 01
6/30/07 CO1 100 15 6/2/07 02
6/30/07 CO1 100 20 6/4/07 03

6/30/07 CO2 100 10 4/3/07 01
6/30/07 CO2 100 15 6/2/07 02
6/30/07 CO2 100 05 6/4/07 03

6/30/08 CO1 150 35 11/7/07 01
6/30/08 CO1 150 20 12/30/07 02
6/30/08 CO1 150 45 10/14/07 03
 
Access queries return ALL of the data.

If you don't want to see that "repeating" value, use the query to feed a
report and have the report hide the duplicate values.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I am trying to create a query whereby values in a "Stock On Hand" does not
repeat in every record each time stock is sold on different and multiple
dates. Please see examples below. This also impacts the summary totals for
each numeric column. Thanks so much for any guidance you can provide.
Click to expand...

This is easy to do on a Report - just set the "Hide Duplicates" property to
Yes. I know of no way to do it in a Query, but you should not be using a query
datasheet for viewing or printing data in any case.
 
Thank you for the quick response and suggestions. Unfortunately, I need to
ask for a different solution. My problem is that I have multiple "Sold"
records for a given Stock Type (CO1, CO2) and initial date (in this case,
6/30/07). I need to show the "Stock On Hand" (100) for "Stock Type" (CO1)
from 6/30/07 and I also need to show the "Stock On Hand" (100) for "Stock
Type" (CO2) from 6/30/07 as shown below, but for each subsequent "Sold"
record of each "Stock Type", I do not need to have the "Stock On Hand" number
and respective "Stock Type" Code repeat for each distinct "Sold" record.
Said another way, the "Stock On Hand" numeric value does not need to be
repeated for each subsequent "Transaction Sale Number" for a given "Date" and
"Stock Type" entry. The Hide Duplicates removed all "Stock On Hand" entries
of (100) after the first one even though the 6/30/07 and CO2 stock type group
is a distinct group separate from the 6/30/07 and CO1 stock type group. I
hope I haven't muddied the waters even further..... Again, thanks for any
guidance in this matter.

Jeff Boyce said:
Access queries return ALL of the data.

If you don't want to see that "repeating" value, use the query to feed a
report and have the report hide the duplicate values.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
___________________________________
Total 350 175
______________________________
Total 1,050 175
 
I believe I've understood that you don't want to see the (100) repeat after
the first time it's display for CO1, and so on.

But only for the combination of CO1 AND a given date.

Access queries don't do that.

Access reports do.

Create a new report, add a group header into which you'll combine stock type
and date, then put the details (stock on hand, transactions, etc) in the,
well, Detail section.

Or put stock type, date AND stock on hand in the group band, then put
transactions as details in the Detail section.

Or am I still misunderstanding...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have the same issue. The client wishes to see the data displayed in a
"spreadsheet" type view, but not show repeating values.

When you use the report Sorting and Grouping it puts the data in a seperate
line for each group. The client wants the first row of a new record to be a
complete listing of all fields across the page.

Field1 Field2 Field3 Field4
-----------------------------------
1 A 1 Dog
2 Cat
2 B 1 Goat
3 C 1 Chicken
2 Duck

If you Hide Duplicates, the "1" will be missing off the "3" listing.
 
I have a "solution" that works, but it does have one small issue which I will
explain at the end.

Set the report with the grouping headers for Stock Type and Stock On Hand.
Place the controls for these header groupings within their respective headers
(align as though they are on the same row).

For each of the two headers, add the Event Procedure for the On Format of:
Me.MoveLayout = False

This aligns all the data across the row to the top.

Put the rest fields in the detail section (make sure to move far enough to
the right as not to run into the Stock On Hand column).

As to the issue...
This works great when you have a defined field lengths or have the controls
set to Can Grow = No.

If you have a long value in Stock Type or Stock On Hand, which requires two
or more lines or will vary from record to record (memo field), you will need
to stretch the Detail section to accommodate.

Hope this solves your problem.
 
I'm not sure I'm following...

Are you saying that the Field3=1 will not be shown for Field1=3? If so, I
would think Field3=1 would also not show for Field1=2, and Field3=2 wouldn't
show for Field1=3. What am I missing?

Also, you've provided a layout, but not described how you'd use the sections
in the report design to achieve that. Is there a chance you've pushed all
the fields into the details section? If so, then any field value that
'repeats' would be hidden. I was suggesting using grouping to allow groupby
Field1, Field2, ?Field3.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top