Well yes that is what i need, i wanted to check for both but the above code, wouldn't that check the whole query ? i need to check on a row because the MIRseq resets when it reaches 65535 from 1 and there can repeating tkt numbers which is ok as it can be for refunds and voids. what was error on my dlook ? just curious to learn.
Yes it will check the whole query but you dont want to check the whole query?
Your DLookup? Thought you was using DCount?? *my poor head*
Yes I do have a headache IRL, my being a pain here isnt really your fault....
Code:
Yours:
If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq) And
DCount("FileID", "qryMIR", "[TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication
vs
Mine:
If Dcount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") then
The difference is you do 2 seperate DCounts, thus two seperate queries, with seperate records. My way only has 1 DCount
Ok reason to check it by a row was that in an actual MIR file a MIRseq is unique to a MIR file and thus unique to the data within. once these are in query, a file is represented by a row so to check for duplicates the ideal way is to check for a MIRSeq from the processing file and check the TicketNumber in the corresponding row rather than checking MIRSeq and TicketNumber randomly. Hope that explains.
OK so the FileId represents each file thus each row are the contents of each file. From the spreadsheet you can see that there are files with same MIRSeq but not same file with both MIRSeq and TicketNumber the same.
If DCount("FileID", "qryMIR", "[MIRSeq] =" & MIRSeq & " and [TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication
ElseIf DCount("FileID", "qryMIR", "[MIRType]=""" & MIRType & """ and [TicketNumber]=""" & tkt & """") Then ' validate data for duplicates with MIRType and TicketNumber
It could be a personal thing, but I prefer to keep things a little more readable by doing something like:
Code:
If DCount("FileID", "qryMIR", "[MIRSeq] =" & MIRSeq & _
" and [TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication
This keeps things visible and on screen particularly if you get more and more "AND/OR" lines
These two Dcounts are very simular, can you not capture them in one select?
Code:
dim rsCheck as dao.recordset
'Can replace the qryMIR by the source table if there is a table that holds the 3 columns
rsCheck = currentdb.openrecordset (" Select MirSeq, MIRType, TicketNumber " & _
" From qryMIR " & _
" Where ( [MIRSeq] =" & MIRSeq & _
" and [TicketNumber]=""" & tkt & """ ) " & _
" or ( [MIRType]=""" & MIRType & """" & _
" and [TicketNumber]=""" & tkt & """ ) "
If not rscheck.eof then
' duplicate found
if MIRSeq = rsCheck!MirSeq then
' MIRSeq message
elseif Mirtype = rscheck!MirSeq then
' mirtype message
endif
Goto NextFile
Endif
rsCheck.close
set rscheck = nothing
This would only do one select instead of 2 Dcounts removing a suplus search on your DB.
What A04 lines do you want to count?
The (duplicate) file now being read?
The file already existing in the db?
The number of A04 lines as they are being read from (any) file into the db?
A simple check in the database could be something like
Code:
DCount("*", "A04Table", "FileID = " & Fileid)
Or
"Select count(*) from A04Table where fileid = " & FileID
Could also incorporate the A04table and count into the above select statement to (further) reduce the processing time required.
The mirseq and mirtype are in tblfiles and ticket number is in tblpax. So i guess the select statement will not work here. Need to check on the elseif state and see if i can get it check the mirseq, mirtype and tikt number at one go.
About the A04 line, need to check the number of lines as they are being read from file into the db as a files named sectors.
The select statement will work with any number of tables... Reducing the number of tables to the bare minimum on seperate queries reduces the amount of memory needed as well as the I/O required to fetch the data. So instead of using your qryMIR which uses 10 tables, using a seperate select with only 2 tables will cause less delay in your code execution. Maybe not hugely noticable but small bits add up over time and as you get more and more data, it will become more and more noticable.
About A04
Well you can easily count them A04 lines while reading them, would seem kind off redundant since once the individual lines are in the db, a simple count(*) can count them for you. Unless you are experiencing performance issues with using a Count(*) solution it is by far prefered to not store calculatable values in your database.
What kind off situation would you need the number of A04 lines for?
Ok ! If i use a query with just the tables with mirseq, mirtype and tktnumber fields, will that be helpful or should i use the select query itself ?
About the A04
The A04 has the sectors, so each line represents a sector. I just need to count the number of sectors in a file. Isn't counting them as the file is read better than using a function in a query as the db grows.
As simple a select as you can make it, remove any surplus if you can
A04
Counting them as they come in the file will save you processing time when you are reporting/looking for the data. However with proper indexing and querying that time should be near 0. Unless you need this data A LOT....
Obviously while reading your A04 lines you can simply do
CounterA04 = CounterA04 +1
and simply write the final number into some table column.