Is it possible to hide/prevent duplicates for SOME data in a query?

[HK123]

Hello, I am very new to Access and self taught by YouTube / Google and by no means a "computer" person. My apologies if I am therefore using incorrect terminology.

I have a query that pulls info from a main order form and its sub form with the item details. I have totals on the main order that I would like to show up only once in my query, while still displaying the information of each item. I have gotten this idea to work on a report I created based on the query by highlighting the text boxes for the data I did not want to repeat and clicking yes on the hide duplicates within the property sheet.

I am assuming this can not be done in a query, but as I said I do not actually know that much about Access yet, and have been surprised a few times at what is possible. I am doing most of my work in design view and have just barley started to dabble in macro functions. If it is possible and anyone knows a good reference article or YouTube video on how to accomplish this I would greatly appreciate it! Or just let me know its impossible so I can stop looking (:

Thank you!

[Micron]

Not sure but what you're describing might need a Totals query. Suggest you google 'ms access Totals query' before trying it in query design view. IIRC it is the Sigma button on the ribbon. Σ

[CJ_London]

I am assuming this can not be done in a query

It can probably be done in a query - suggest show some example data and the outcome required to make sure we understand what the requirement actually is.

[HK123]

Thanks all, with more research it appears it was not possible to achieve my results in a query. I was however able to export into excel and then with a bit of formatting get the desired results.

[NoellaG]

Some things that cannot be done in 1 query can be done when combining two queries. You can create a totals query and combine it with a query showing detail records.

[June7]

I am sure it can be done - involves a UNION query. Something like:

SELECT ProdID, Product, Quantity FROM OrderDetails INNER JOIN Products ON OrderDetails.ProdID = Products.ProdID
UNON SELECT "Total", Product, Sum(Quantity) FROM OrderDetails INNER JOIN Products ON OrderDetails.ProdID = Products.ProdID GROUP BY Product
ORDER BY Product, ProdID;

However, if you want to display more fields from OrderDetails, will get more complicated.

SELECT ProdID, Product, OrderNum, Quantity FROM OrderDetails INNER JOIN Products ON OrderDetails.ProdID = Products.ProdID
UNON SELECT ProdID, Product, Total, Null FROM (SELECT "Total" AS ProdID, Product, Sum(Quantity) AS Total FROM OrderDetails INNER JOIN Products ON OrderDetails.ProdID = Products.ProdID GROUP BY Product) AS Q1
ORDER BY Product, ProdID;

[Salty Mariner]

Hello, I am very new to Access and self taught by YouTube / Google and by no means a "computer" person. My apologies if I am therefore using incorrect terminology.

I have a query that pulls info from a main order form and its sub form with the item details. I have totals on the main order that I would like to show up only once in my query, while still displaying the information of each item. I have gotten this idea to work on a report I created based on the query by highlighting the text boxes for the data I did not want to repeat and clicking yes on the hide duplicates within the property sheet.

I am assuming this can not be done in a query, but as I said I do not actually know that much about Access yet, and have been surprised a few times at what is possible. I am doing most of my work in design view and have just barley started to dabble in macro functions. If it is possible and anyone knows a good reference article or YouTube video on how to accomplish this I would greatly appreciate it! Or just let me know its impossible so I can stop looking (:

Thank you!

Have you considered using a subReport on a form? You can use the Grouping and Totals functions of the report If you are not going to edit the data this would accomplish what you want since you already figured that out. and if you do need to modify it. I did that in the picture below. If the user needs to change the record I have a click event attached to the textbox for the document name. I formatted it to "look" like a hyperlink so the user has a visual clue that it actually does something. That in turn opens another form to actually edit the data. It works great. Just shrink the subReport to fit on your form. In my case the detail section of the report behaves just like continuous forms.

Something to think about and perhaps play around with.

[CJ_London]

still guessing as to what your actual output is required but this link might give you some idea's - all done with queries

https://www.access-programmers.co.uk...report.326715/

first example looks like this


colors are a feature of the form rather than the underlying query

[HK123]

Thanks everyone! I am going to look into these suggestions! it appears some of these may work for what I need.