Something Complicated?

[waynewe]

Hi gurus

I'm looking for query to extract records from one table linked to another table based on different ranges. Now here goes.

SELECT Invoice.dbInvoiceNum, Invoice.dbInvoiceDate, Invoice.dbFirstName+' '+Invoice.dbFamilyName) AS dbPatient, Invoice.dbdob, Invoice.dbServiceProvider, InvoiceItem.dbItemNum, Invoice.dbPayerName, Invoice.dbTotal, Invoice.dbTotal-Invoice.dbBalance AS dbPaid, InvoiceItem.dbServiceTax, TransactionLog.dbTransCode
FROM ((Invoice INNER JOIN InvoiceItem ON Invoice.dbInvoiceId = InvoiceItem.dbInvoiceId) INNER JOIN LEFT JOIN TransactionLog ON Invoice.dbInvoiceId = TransactionLog.dbInvoiceId
WHERE (dbInvoiceDate Between #4/1/2006# And #4/30/2006#) AND (TransactionLog.dbTransCode <> "CRDC") AND (Invoice.dbActualTotal > 0) AND (InvoiceItem.dbItemCode Between '57506' AND '58115') AND (InvoiceItem.dbItemCredit = False)
ORDER BY Invoice.dbInvoiceDate, Invoice.dbInvoiceNum, TransactionLog.dbTransCode DESC;

Now problem from above query is that I not only want invoices with items from the range

(InvoiceItem.dbItemCode Between '57506' AND '58115')

also want invoices with items in range of

(InvoiceItem.dbItemCode Between '1' AND '5267') AND/OR (InvoiceItem.dbItemCode Between '57506' AND '58115')

Invoices with items could either be just in range of '1' AND '5267' AND '57506' AND '58115'.
I am not interested in invoices in range '1' AND '5267' without the range '57506' AND '58115'. For every invoice in the range of '57506' AND '58115' it will have a range '1' to '5267'.

The above query just rips out the invoices with items in the range '57506' AND '58115' and showing duplicates and not the items also contained in range '1' to '5267'.

Writing separate queries and extracting and looping takes far too long especially if total database dump was involved. It could take 20 mins to create report due to volume of records could be somewhat over 300K. I have hope and done before in one query that takes little time, however this seems little complicated.

Any help would be much appreciated.

 

[neileg]

AND/OR is not a valid SQL operator. Your syntax should be:
((InvoiceItem.dbItemCode Between '1' AND '5267') OR (InvoiceItem.dbItemCode Between '57506' AND '58115'))

 

[waynewe]

your mising the point.

I don't want just dbItemCode Between '1' AND '5267' OR dbItemCode Between '57506' AND '58115'

If it's Between '1' AND '5267' then want to ensure, and select records that also contain items Between '57506' AND '58115'

I am not interested in just either range, want all records with items contained in both.

a record may contain items

RecordId = 700 ItemCode = 7
RecordId = 700 ItemCode = 116
don't want this record just Between 1 AND 5267

however
RecordId = 713 ItemCode = 7
RecordId = 713 ItemCode = 57716
want this as it meets criteria Between 1 AND 5267 also Between '57506' AND '58115'

and this also falls in that range
RecordId = 1020 ItemCode = 56
RecordId = 1020 ItemCode = 58102

 

[neileg]

So use AND instead of OR.

 

[waynewe]

it doesn't return anything

i know there are records in the db that meet the criteria


here is a simplified version

SELECT Invoice.dbInvoiceNum, Invoice.dbInvoiceDate, Invoice.dbFamilyName+' '+Invoice.dbFirstName AS dbPatient, Invoice.dbDob, Invoice.dbPayer
FROM InvoiceItem INNER JOIN Invoice ON InvoiceItem.dbinvoiceId = Invoice.dbinvoiceId
WHERE ((InvoiceItem.dbItemCode Between '1' And '5267' AND (Invoice_Item.dbItemCode Between '57506' And '58115') AND (Invoice_Item.dbdos Between #4/1/2006# And #4/30/2006#));

the problem is the Item Code will never be in both ranges, you'd agree with me here however an Invoice containing items could be in either range. I just not want either range, I want invoices with Items in both like the logical operator 1 . 1 = 1 not interested in other 1 . 0 = 0 and 0 . 1 = 0

 

[Banana]

Perhaps instead of using a query to do both, use a open recordset to create a temporary table/query; base your recordset on query pulling records between 4/1/2006 to 4/30/2006, then adding records to the temporary table if they are in either ranges...

 

[waynewe]

yeah thanks

i didn't want to but decide temp table way to go.

 

[Banana]

I wonder if SQL can accept "BETWEEN A AND B OR C AND D"? (as opposed to two BETWEEN clauses?)

 

[Rich]

Why are you surrounding numerical values with quotes?

 

[waynewe]

because there actually stored as text due to that no only items can be numeric but alpha numeric

ie.

110
74123
0MYT99
etc.

 

[waynewe]

yeah tried "BETWEEN A AND B OR C AND D" like everything else however doesn't return correct records if at all

 

[Banana]

While I'm not surprised that it returns nothing when you use AND clause for both set of ranges, as this would require a record to be contained in both set, which is impossible, hence zero returns, I don't get why Access wouldn't use OR clause to collect records in both sets.

If you're interested and have time, try this small test:

Use your original query that used the OR clause for both set of ranges. Run it against a query. First time, sort it by itemcode ascending. Then again but sorted descending.

My theory is that Access will return 1-5200 for ascending query but not 52000-57000, then vice versa when it's descending. If it is indeed the case, this tells us that when Access gets a OR clause, it's thinking it has to choose one of two, as opposed to testing if it meets either sets.

 

[waynewe]

thankyou, however created a temp table to store the records required as follow. this seems to work fine.


sSql = "SELECT DISTINCT InvoiceItem.dbinvoiceno, Invoice.dbdispinvoiceNo, Invoice.dbinvdate, Invoice.dbpayername, [dbgivenname]+' '+[dbsurname] AS dbpatient, Invoice.dbdob, Invoice.dbtotal, [dbtotal]-[dbbalance] AS dbpaid, Invoice.dbinvoiceno INTO temp_invoices
FROM InvoiceItem INNER JOIN Invoice ON InvoiceItem.dbinvoiceno = Invoice.dbinvoiceno
WHERE ((Invoice_Item.dbcredited=False) AND (IIf(IsNumeric([dbItemCode]),CLng([dbItemCode]),0) Between 55028 And 64991) AND (Invoice.dbactualtotal>0));"
db.Execute sSql


'to trap any non numeric not required IIf(IsNumeric([dbItemCode]),CLng([dbItemCode]),0) Between 55028 And 64991

sSql = "SELECT temp_invoices.dbdispinvoiceno, temp_invoices.dbinvdate, temp_invoices.dbpayername, temp_invoices.dbpatient, temp_invoices.dbdob, temp_invoices.dbtotal, temp_invoices.dbpaid, Invoice_Item.dbdos, InvoiceItem.dbItemCode, Invoice_Item.dbitemdesc, InvoiceItem.dbextradesc, InvoiceItem.dbamount, InvoiceItem.dbgst, InvoiceItem.dbserviceby
FROM temp_invoices INNER JOIN InvoiceItem ON temp_invoices.dbinvoiceno = InvoiceItem.dbinvoiceno
WHERE (((InvoiceItem.dbcredited)=False));"

Set rs = db.OpenRecordset(sSql, dbOpenDynaset, dbReadOnly)


sSql = "DROP TABLE temp_invoices"
db.Execute sSql


sSql = "DROP TABLE temp_distinctitems"
db.Execute sSql


this gives me the correct recordset that contains records with invoices in both ranges as where the inner join was incorrect.

thanks again

 

[neileg]

I've reread your postings and I think you misunderstand how the OR operator works in the WHERE clause. I still think my original advice was correct, ie:

((InvoiceItem.dbItemCode Between '1' AND '5267') OR (InvoiceItem.dbItemCode Between '57506' AND '58115'))