Solved

Interesting datasheet view challenge

Posted on 2014-02-03
4
475 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
ID: 39829417
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
ID: 39829446
I need both rows to show.
0
 

Author Comment

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

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39829469
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

929 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

12 Experts available now in Live!

Get 1:1 Help Now