Double Query

[access2010]

Could we please receive a suggestion on how to create a query in Access 2003 that will group our records once.

If the Trade field has “Purchased” or if Investigate field has “Yes”, than all those matching records will be grouped together for printing.

We do not want to print any duplicate Stock_Names.

Thank you.
Crystal

 

[The_Doc_Man]

Not sure what you are asking. Let me clarify by telling you what it SEEMED like you asked to see.

You want a query or report that will group matching records if EITHER the Trade field says "Purchased" or the Investigate field is TRUE. You want to avoid duplicates of Stock Names. Structurally, that might be as simple as

SELECT bunch-of-fields FROM table-or-query WHERE [Trade]='Purchased' OR [Investigate]=TRUE
GROUP BY some-field ;

The question has to be, if you are doing a group for a printed report, grouping on what field? But if you are doing a query that is to perform grouping, unless you are grouping on Stock_Names, you WILL have duplicates.

The answer to your question depends on what you want to do with the query because Access reports actually ignore grouping in queries, preferring to impose its own sort order on things. So we need a little more on where this putative query is going.

 

[arnelgp]

i created another query qry4Report.
also see the sample report.

 

[plog]

Please demonstrate what data you expect your query to return based on the data in Investments01_tbl. You showed us what data you are starting with, now show us what data you expect to end up with. Again, data, not an explanation, but data. Show us what data you expect.

 

[access2010]

Thank you all for your suggestions.
The reports that we are trying to print using the table “Investments01_tbl” would have the following conditions;

If the Trade field contains “Purchased”
Or if Investigate field contains “Yes”
and do not print duplicate “Stock_Names”

I am sorry for our miscommunications.

Thank you.
Nicole

 

[access2010]

Not sure what you are asking. Let me clarify by telling you what it SEEMED like you asked to see.

You want a query or report that will group matching records if EITHER the Trade field says "Purchased" or the Investigate field is TRUE. You want to avoid duplicates of Stock Names. Structurally, that might be as simple as

SELECT bunch-of-fields FROM table-or-query WHERE [Trade]='Purchased' OR [Investigate]=TRUE
GROUP BY some-field ;

The question has to be, if you are doing a group for a printed report, grouping on what field? But if you are doing a query that is to perform grouping, unless you are grouping on Stock_Names, you WILL have duplicates.

The answer to your question depends on what you want to do with the query because Access reports actually ignore grouping in queries, preferring to impose its own sort order on things. So we need a little more on where this putative query is going.

Thank you, Doc Man for your note.

We would like to print our report if the Trade Field contains "Purchased" or if (not and) if the Investigate field contains yes, BUT not to have any duplicate Stock Names.

Nicole

 

[access2010]

i created another query qry4Report.
also see the sample report.

Thank you Arnelgp for your data base which brings us VERY CLOSE to what we would like.

Using the table "Investments01_tbl", we would like to print our report if the Trade Field contains "Purchased" or if (not and) if the Investigate field contains yes, BUT not to have any duplicate Stock Names.

Nicole

 

[access2010]

Thank you for your suggestions.
This query obtains most of the data that we are looking for BUT the records that are marked with YES are being duplicated.
We would like to have the stock name appear ONLY ONCE.

Your suggestions will be appreciated
Crystal.

 

[arnelgp]

use Total Query?

 

[access2010]

Hello, Arnelgp.

Thank you for your suggestion on using a total Query, but we have had no success in keeping duplicate stock names from appearing.
Would you please edit the attached database to keep, the duplicate stock names from appearing?

Thank you.
Nicole.

 

[SHANEMAC51]

Не могли бы вы отредактировать прикрепленную базу данных, чтобы сохранить дубликаты названий акций от появления?

there is no qry4ReportPurpose query in the database , which is needed for the report

 

[arnelgp]

don't include the Autonumber field in the query if you have duplicate on stock name.

 

[access2010]

Thank you Arnelgp = 100%

Your suggestion has been used in our reports with SUCCESS.

Thank you.
Crystal and Nicole

 

[access2010]

there is no qry4ReportPurpose query in the database , which is needed for the report

Thank you for trying to help us Shaneman.
The suggestion that Arnelgp made works.

Nicole

 

[access2010]

We have tried different queries and this query which we created is close.

Could we please have assistance in having only ONE record printed by our report = Template_Analytic_R_Purchased_Investigate =?

We do not want to print duplicate records that may occur when,
Trade = Purchased and Investigate = Yes

Thank You
Crystal

 

[arnelgp]

I created a query, qryPurchasedYes and use it
as Recordsource of your report.

 

[access2010]

Thank you, Arnelgp for your assistance.

Your suggest does work in our query but we are receiving duplicate records when we go live with our reports.

Could you please look at our report = Template_Analytic_R_Trade_or_Investigate=Nicole

Page # 02 = Western Forest Products = gets repeated, while we would like to print this record only once.

Page # 03 = Whitecap Resources Inc = gets repeated, while we would like to print this record only once.

Page # 04 = Schlumberger Limited = gets repeated, while we would like to print this record only once.

Page # 04 = Zscaler Inc = gets repeated, while we would like to print this record only once.

We would appreciate your suggestion on how to have each stock name printed once.

Thank you for your continual assistance, do you have a favorite charity that we could make a donation to in your name?

Thank you.
Nicole