[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Interesting datasheet view challenge

Posted on 2014-02-03
4
Medium Priority
?
488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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