Interesting datasheet view challenge

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
SteveL13Asked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
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
 
regmigrantCommented:
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
 
SteveL13Author Commented:
I need both rows to show.
0
 
SteveL13Author Commented:
I take that back.  I do not want both rows to show.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.