restrict duplicates in a query not a table

[tymperance]

I am linking tables from an existing ODBC data source and running queries from them. In one of the queries I'm trying to get a list of accounts that have had activity in the last year. The problem is if an account has had multiple activity dates it is showing twice or more and I only want to see it once. There is an account number field that I want to restrict to only have the account show once but I don't know how.

Any help would be appreciated.

[MMcCarthy]

I am linking tables from an existing ODBC data source and running queries from them. In one of the queries I'm trying to get a list of accounts that have had activity in the last year. The problem is if an account has had multiple activity dates it is showing twice or more and I only want to see it once. There is an account number field that I want to restrict to only have the account show once but I don't know how.

Any help would be appreciated.

Please post the SQL of the query you are currently using.

[tymperance]

Please post the SQL of the query you are currently using.

I'm still new to this, I think this is what you are wanting, let me know if it is not.

SELECT DISTINCT PBR_PbrAcct1.Ac tnum, PBR_PbrAcctDemM ain1.Patnum, PBR_PbrAcctDemM ain1.Name, PBR_PbrAcctPtAd drEtc1.Addr1, PBR_PbrAcctPtAd drEtc1.Addr2, PBR_PbrAcctPtAd drEtc1.City, PBR_PbrAcctPtAd drEtc1.State, PBR_PbrAcctPtAd drEtc1.Zip, PBR_PbrAcctPtAd drEtc1.HomePhon e, PBR_PbrAcctPtAd drEtc1.OtherPho ne, PBR_PbrAcctTxns Financial1.TxnL ocation
FROM (((((PBR_PbrAct TxnsSerDateX1 INNER JOIN PBR_PbrAcct1 ON PBR_PbrActTxnsS erDateX1.Accoun t = PBR_PbrAcct1.Ac count) INNER JOIN PBR_PbrAcctDemM ain1 ON PBR_PbrAcct1.Ac count = PBR_PbrAcctDemM ain1.Account) INNER JOIN PBR_PbrAcctPtAd drEtc1 ON PBR_PbrAcctDemM ain1.Account = PBR_PbrAcctPtAd drEtc1.Account) INNER JOIN PBR_PbrAcctTxns Financial1 ON PBR_PbrAcctPtAd drEtc1.Account = PBR_PbrAcctTxns Financial1.Acco unt) INNER JOIN PBR_PbrDoctorDi ct1 ON PBR_PbrAcctTxns Financial1.TxnD octor = PBR_PbrDoctorDi ct1.Mnemonic) INNER JOIN PBR_PbrProcDict 1 ON PBR_PbrAcctTxns Financial1.TxnP rocedure = PBR_PbrProcDict 1.Mnemonic
WHERE (((PBR_PbrDocto rDict1.DbLvlB)=[Practice]) AND ((PBR_PbrProcDi ct1.Type)="CHG" ) AND ((PBR_PbrActTxn sSerDateX1.TxnS erDate)>=[from Date] And (PBR_PbrActTxns SerDateX1.TxnSe rDate)<=[Thru Date]));

[MMcCarthy]

Which of these columns has data that's different within the same Account.

BR_PbrAcct1.Act num,
PBR_PbrAcctDemM ain1.Patnum,
PBR_PbrAcctDemM ain1.Name,
PBR_PbrAcctPtAd drEtc1.Addr1,
PBR_PbrAcctPtAd drEtc1.Addr2,
PBR_PbrAcctPtAd drEtc1.City,
PBR_PbrAcctPtAd drEtc1.State,
PBR_PbrAcctPtAd drEtc1.Zip,
PBR_PbrAcctPtAd drEtc1.HomePhon e,
PBR_PbrAcctPtAd drEtc1.OtherPho ne,
PBR_PbrAcctTxns Financial1.TxnL ocation

[tymperance]

Which of these columns has data that's different within the same Account.

BR_PbrAcct1.Act num,
PBR_PbrAcctDemM ain1.Patnum,
PBR_PbrAcctDemM ain1.Name,
PBR_PbrAcctPtAd drEtc1.Addr1,
PBR_PbrAcctPtAd drEtc1.Addr2,
PBR_PbrAcctPtAd drEtc1.City,
PBR_PbrAcctPtAd drEtc1.State,
PBR_PbrAcctPtAd drEtc1.Zip,
PBR_PbrAcctPtAd drEtc1.HomePhon e,
PBR_PbrAcctPtAd drEtc1.OtherPho ne,
PBR_PbrAcctTxns Financial1.TxnL ocation

BR_PbrAcct1.Act num the account number

[MMcCarthy]

BR_PbrAcct1.Act num the account number

You have different Actnum within one account?

If so which field identifies the account?

[tymperance]

You have different Actnum within one account?

If so which field identifies the account?

No, I'm sorry I misread what you asked. The only one that might be different is PBR_PbrAcctTxns Financial1.TxnL ocation. They could go to multiple locations, but they may not. They could very well have multiple TxnProcedure on the same TxnSerDate at the same TxnLocation.

[MMcCarthy]

No, I'm sorry I misread what you asked. The only one that might be different is PBR_PbrAcctTxns Financial1.TxnL ocation. They could go to multiple locations, but they may not. They could very well have multiple TxnProcedure on the same TxnSerDate at the same TxnLocation.

That's why the distinct isn't working. To retrieve distinct records all of the values returned must be distinct.

If that field cannot me omitted let me know and I'll help redesign the query as an aggregate/group by query rather than a distinct.

[tymperance]

That's why the distinct isn't working. To retrieve distinct records all of the values returned must be distinct.

If that field cannot me omitted let me know and I'll help redesign the query as an aggregate/group by query rather than a distinct.

Unfortunately it can not be removed, I have to have production totals for location.

[MMcCarthy]

Unfortunately it can not be removed, I have to have production totals for location.

But for which location. How do you identify which record is to be returned?
e.g. The most recent or by some other method.

[tymperance]

But for which location. How do you identify which record is to be returned?
e.g. The most recent or by some other method.

I spoke with the customer and they have agreed to remove location for now (we'll end up doing that as a seperate report). Even when I remove the location the distinct row isn't working. I'm still receiveing a row per TXNProcedure. Is there something else I need to look at?

Thank you so much for your help!

[MMcCarthy]

I spoke with the customer and they have agreed to remove location for now (we'll end up doing that as a seperate report). Even when I remove the location the distinct row isn't working. I'm still receiveing a row per TXNProcedure. Is there something else I need to look at?

Thank you so much for your help!

You need to decide which TXNProcedure value you wish to return.

[tymperance]

You need to decide which TXNProcedure value you wish to return.

Even if I'm not having it return the TXN Procedure? I'm using that as selection criteria so it only analyzes charge activity and not payments but I'm not actually pulling it on the view. If that is the case then it looks like I need to find another route.

[MMcCarthy]

Even if I'm not having it return the TXN Procedure? I'm using that as selection criteria so it only analyzes charge activity and not payments but I'm not actually pulling it on the view. If that is the case then it looks like I need to find another route.

Post the query as you are using it at the moment. If you are not returning it then no you shouldn't be getting a record for each value.

[tymperance]

Post the query as you are using it at the moment. If you are not returning it then no you shouldn't be getting a record for each value.

SELECT DISTINCT PBR_PbrAcct1.Ac tnum, PBR_PbrAcctDemM ain1.Patnum, PBR_PbrAcctDemM ain1.Name, PBR_PbrAcctPtAd drEtc1.Addr1, PBR_PbrAcctPtAd drEtc1.Addr2, PBR_PbrAcctPtAd drEtc1.City, PBR_PbrAcctPtAd drEtc1.State, PBR_PbrAcctPtAd drEtc1.Zip, PBR_PbrAcctPtAd drEtc1.HomePhon e, PBR_PbrAcctPtAd drEtc1.OtherPho ne
FROM (((((PBR_PbrAct TxnsSerDateX1 INNER JOIN PBR_PbrAcct1 ON PBR_PbrActTxnsS erDateX1.Accoun t = PBR_PbrAcct1.Ac count) INNER JOIN PBR_PbrAcctDemM ain1 ON PBR_PbrAcct1.Ac count = PBR_PbrAcctDemM ain1.Account) INNER JOIN PBR_PbrAcctPtAd drEtc1 ON PBR_PbrAcctDemM ain1.Account = PBR_PbrAcctPtAd drEtc1.Account) INNER JOIN PBR_PbrAcctTxns Financial1 ON PBR_PbrAcctPtAd drEtc1.Account = PBR_PbrAcctTxns Financial1.Acco unt) INNER JOIN PBR_PbrDoctorDi ct1 ON PBR_PbrAcctTxns Financial1.TxnD octor = PBR_PbrDoctorDi ct1.Mnemonic) INNER JOIN PBR_PbrProcDict 1 ON PBR_PbrAcctTxns Financial1.TxnP rocedure = PBR_PbrProcDict 1.Mnemonic
WHERE (((PBR_PbrDocto rDict1.DbLvlB)=[Practice]) AND ((PBR_PbrProcDi ct1.Type)="CHG" ) AND ((PBR_PbrActTxn sSerDateX1.TxnS erDate)>=[from Date] And (PBR_PbrActTxns SerDateX1.TxnSe rDate)<=[Thru Date]));