Solved

Interesting datasheet view challenge

Posted on 2014-02-03
4
469 Views
Last Modified: 2014-02-06
I have a form displaying in datasheet view.  The form uses a query as it's record source.  But I have one issue... there are fields displaying numbers that repeat and I don't want them to repeat because I have totals turned on and those totals are being effected by the number of times the number appears for that particular item.

Please see that attachment to understand my issue.

Using this example you can see that record M1640 has a scheduled quantity of 1,600 but that number need to either appear only once OR the "Total" number needs to include it only once.

So the "Total" I'm looking for should be 2,000... not 3,600.

Can this be done?

--Steve
sample.jpg
0
Comment
Question by:SteveL13
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
In general you would add  'group by  M#' to the end of your query
eg: select salary from a_table where name = 'Fred' group by name

However you may have a badly formed where clause which is returning two rows instead of one - if you post the query it can be looked at further
0
 

Author Comment

by:SteveL13
Comment Utility
I need both rows to show.
0
 

Author Comment

by:SteveL13
Comment Utility
I take that back.  I do not want both rows to show.
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
Comment Utility
group by works by taking the field its given and grouping the other data relevant to that field - so it will sum  the 'scheduled quantity' for M# for all records returned. In the case you quoted that would mean one record for M1640 with a total of 3200.

It only works correctly if the data set is correctly returned in the first place. So if your query is returning a duplicate row Group By will add both quantities together and 'hide' the duplicate M# whilst doubling the 'scheduled qty'. if the Dataset DOES have two M1640 with a quantity of 1600 each then it will show a single M1640 for 3200 and that will be correct.

I suspect from the question phrasing that you do not expect 2 identical rows and that may indicate the query is wrong, usually its because a join to another table is returning two records so the output is showing one record twice but without knowing the query or the tables its impossible to say
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now