Query off of a SQL Server (1 Viewer)

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
I'm pulling data off of a SQL server through an ODBC connection. My query is pulling in various transactional data for a hospital. All of the data comes out successfully in the query. I was also able to apply a filter by date. The issue I run into is trying to apply a filter based on a specific code (It is a number however, the field is in a short text format). Is there a particular way that I need to apply a filter for a short text field? I'm a complete novice so please forgive me if this is a stupid question. :banghead:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
What is the issue (error, etc)? What have you tried? I would expect to be able to put a form reference or "123" in the criteria in design view. If the field has a numeric data type, no quotes.
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
What is the issue (error, etc)? What have you tried? I would expect to be able to put a form reference or "123" in the criteria in design view. If the field has a numeric data type, no quotes.

I'm not getting any errors. It pulls in all of the data. When I apply a filter in the design view it pulls in all of the data as if I have not applied a filter. The field is actually listed as a Short Text field but the content is numeric. I spoke with our DBA and he's thinking that this is something related to the way that Access interacts with SQL. We reviewed the SQL code in the SQL view and it looks correct as well. We're not really sure why the data is not filtering correctly.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
I think the DBA is all wet, but I'll reserve judgement until we solve it. What is the SQL of your query?
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
I think the DBA is all wet, but I'll reserve judgement until we solve it. What is the SQL of your query?

He was able to run the query against the SQL database directly using the virtually the same SQL code I had in Access and did so successfully.

When I used the Make Table button using my existing query it worked with no problems. It's only happening when I run the query directly. Here's the code:

SELECT ENCOUNTER.MRNTreatHospitalAbbreviation,
FINANCIALTRANSACTION.ServiceDate,
FINANCIALTRANSACTION.PostingDateFormat,
FINANCIALTRANSACTION.ActivityCode,
FINANCIALTRANSACTION.ActivityCodeDescription,
FINANCIALTRANSACTION.CPTCode,
FINANCIALTRANSACTION.TransBillUnits,
FINANCIALTRANSACTION.Units,
FINANCIALTRANSACTION.DetailTotalAmount,
FINANCIALTRANSACTION.AccountingPeriod,
FINANCIALTRANSACTION.AccountingYear, [Formulary List with NDCs and CDMs].[NDC Code], [Products NDC to GCN].GCN, FINANCIALTRANSACTION.RevenueCenterCode
FROM (ENCOUNTER RIGHT JOIN FINANCIALTRANSACTION ON ENCOUNTER.AccountandFacility = FINANCIALTRANSACTION.AccountandFacility) LEFT JOIN ([Formulary List with NDCs and CDMs] LEFT JOIN [Products NDC to GCN] ON [Formulary List with NDCs and CDMs].[NDC Code Text] = [Products NDC to GCN].NDC) ON FINANCIALTRANSACTION.ActivityCode = [Formulary List with NDCs and CDMs].[Charge Code]
WHERE (((FINANCIALTRANSACTION.PostingDateFormat)>"07/01/2015") AND ((FINANCIALTRANSACTION.RevenueCenterCode)="30200"));
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
So you're saying codes other than 30200 are returned? I use Access queries against linked SQL Server tables all the time, and I don't think I've ever had that problem.

Is PostingDateFormat a text field or date/time? If date/time, the date should be surrounded by #, not ".
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
So you're saying codes other than 30200 are returned? I use Access queries against linked SQL Server tables all the time, and I don't think I've ever had that problem.

Is PostingDateFormat a text field or date/time? If date/time, the date should be surrounded by #, not ".

Correct. I get all of the available codes and their relevant data in the query. Only when I Make Table, do I actually get the filtered data. Posting date is a date field. I adjusted the date field and it is working. It's only the revenue code that isn't being filtered appropriately.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
Access and SQL Server work pretty well together, and like I said I don't think this has ever happened to me. I wonder if it's in the joins. Try this as a test:

SELECT *
FROM FINANCIALTRANSACTION
WHERE FINANCIALTRANSACTION.PostingDateFormat>#07/01/2015# AND FINANCIALTRANSACTION.RevenueCenterCode="30200"
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
Access and SQL Server work pretty well together, and like I said I don't think this has ever happened to me. I wonder if it's in the joins. Try this as a test:

SELECT *
FROM FINANCIALTRANSACTION
WHERE FINANCIALTRANSACTION.PostingDateFormat>#07/01/2015# AND FINANCIALTRANSACTION.RevenueCenterCode="30200"

Still got all of the rev codes. :(
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
Okay, this is a puzzler. What is the T-SQL that works for the DBA? I realize you can't post the db since the back end is SQL Server, but how about a screen shot of the results you're seeing?
 

Minty

AWF VIP
Local time
Today, 09:43
Joined
Jul 26, 2013
Messages
10,353
I wonder if it's how the tables where linked in - sometimes when linking SQL tables it asks you to select the Unique ID / record field - if you don't I've seen some strange results...?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
It will do that if there's no primary key defined in SQL Server. The only result I've seen from not selecting one is a read-only table, but who knows?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:43
Joined
Jan 20, 2009
Messages
12,849
The only result I've seen from not selecting one is a read-only table, but who knows?

I have seen other problems where a non-unique field is inappropriately selected as a key during the import. The data in all fields of a record with a particular value in the field selected as a key can be displayed for other records sharing that same "key", instead of what is actually in them.

I have also seen this lead to the appearance that the query criteria is not being applied. I think it is very likely that the problem is the inappropriate choice of the primary key during import.
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
Okay, this is a puzzler. What is the T-SQL that works for the DBA? I realize you can't post the db since the back end is SQL Server, but how about a screen shot of the results you're seeing?

Unfortunately I'm not able to share any of the data. It's PHI so I'm limited in what I can provide. We're fairly puzzled on our end as well. And again, the stranger part is that running a direct SQL query yields the correct results.
 

Minty

AWF VIP
Local time
Today, 09:43
Joined
Jul 26, 2013
Messages
10,353
You already posted the SQL of the non working code - surely you can post the SQL that worked on the Server? We don't need or want to see the data just the query that worked.
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
I wonder if it's how the tables where linked in - sometimes when linking SQL tables it asks you to select the Unique ID / record field - if you don't I've seen some strange results...?

So I removed all links and just tried to search against the transactional data alone. I was still getting the same unfiltered revenue codes.
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
You already posted the SQL of the non working code - surely you can post the SQL that worked on the Server? We don't need or want to see the data just the query that worked.

Oh sorry. I misunderstood. I thought you were asking for the results. I'd have to ask the DBA to provide it to me. I don't have SQL. Just access to the data warehouse.
 

oakey66

Registered User.
Local time
Today, 05:43
Joined
Oct 27, 2015
Messages
12
Okay, this is a puzzler. What is the T-SQL that works for the DBA? I realize you can't post the db since the back end is SQL Server, but how about a screen shot of the results you're seeing?

Sorry for the delay folks. Here's the SQL Query:

Code:
SELECT        ENCOUNTER.MRNTreatHospitalAbbreviation, FINANCIALTRANSACTION.ServiceDate, FINANCIALTRANSACTION.PostingDateFormat, 
FINANCIALTRANSACTION.ActivityCode, FINANCIALTRANSACTION.ActivityCodeDescription, 
FINANCIALTRANSACTION.CPTCode, 
FINANCIALTRANSACTION.TransBillUnits,
FINANCIALTRANSACTION.Units, 
FINANCIALTRANSACTION.DetailTotalAmount, 
ENCOUNTER.PrimaryCarrierNameFreeText, FINANCIALTRANSACTION.RevenueCenterCode
FROM            ENCOUNTER INNER JOIN
                   FINANCIALTRANSACTION ON ENCOUNTER.AccountandFacility = FINANCIALTRANSACTION.AccountandFacility
WHERE        (FINANCIALTRANSACTION.PostingDateFormat >= '07/01/2015') AND (FINANCIALTRANSACTION.RevenueCenterCode = '30200')
 

Minty

AWF VIP
Local time
Today, 09:43
Joined
Jul 26, 2013
Messages
10,353
That would indicate that the Revenue centre number is actually text not a number.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:43
Joined
Aug 30, 2003
Messages
36,118
Minty, that was stated at the outset.

Oakey, this is really bugging me. I note the join in the T-SQL is different, but you said even a straight query against the FINANCIALTRANSACTION table alone wasn't working, right? I've got a SQL Server table with a similar field, ie a text field that holds numbers. I query against it all the time using that field in the criteria; never had this problem. Can you confirm the data type on the Access side? Maybe it didn't translate correctly. Or try treating like a numeric field:

SELECT *
FROM FINANCIALTRANSACTION
WHERE FINANCIALTRANSACTION.PostingDateFormat>#07/01/2015# AND FINANCIALTRANSACTION.RevenueCenterCode=30200
 

Users who are viewing this thread

Top Bottom