Solved

Interesting datasheet view challenge

Posted on 2014-02-03
4
485 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
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.

696 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