F1 Please - Interesting Design /Logic Puzzle to solve 2

[Deadline]

*You may want to refer to the Posting Titled Comparing Array Elements - F1 please before reading this *

The actual requirement is to read from a comma separated text file that may contain duplicate rows.

The text file contains : EmpId,TripNumber,CtryCode

An employee may actually travel to same country-different cities in a single trip and in that situation, the text file will contain:

222,90000,US
222,90000,US

etc.

Also he may travel to different countries in a single trip. Then the text file will be like

222,900001,UK
222,900001,RU
222,900001,FR

Another possibility is that he may travel to just one country also; Then

222,900002,US




My requirement is that I should capture all these info and then to move it into tableA. BEFORE inserting into TableA, I have to check if the same info is present there (TableA)already. The comparison is based on the trip number.
ie, if the trip number is present already or not.

If it is, then I have to move those values into another table called TableAArchive; Else, simply insert into TableA.

We are not all concerned about the CtryCode or EmpId.

The whole process may appear simpler than it actually is.

Can anyone suggest a strategy for this ?

I hope this presents an interesting Design Problem for you to solve.

Meanwhile I am also trying hard to crack this.

Thanks a lot in advance.

RR.

 

[lionelbrown]

Here's a quick crack at it:

Assuming you mean if the trip already exists in Table A before you insert ANY of the values of the text file:

(By the way this may not give you the best performance, but I'm just giving a quick solution that could be cleaned up).

1. Import the text file into a empty temporary table (I'll call it tblTempTripFile).

2. Using an SQL statement, open a recordset that contains unique trip values from tblTempTripFile (I'll call the recordset rstUniqueTrips).

3. Step through each record in rstUniqueTrips in code. Start at the first record in rstUniqueTrips.
Open another recordset on TableA:
"Select * from TableA where TableA.TripNumber = " & rstUniqueTrips.TripNumber" (I'll call this rstTripExists).
Test for rstTripExists.eof to see if recordset is empty.

If it is then the trip is not in TableA, so run an append query to add the rows from tblTempTripFile where TripNumber = rstUniqueTrips.TripNumber to TableA.

If rstTripExists.eof is not true then the trip already exists, so run an append query to add the rows from tblTempTripFile where TripNumber = rstUniqueTrips.TripNumber to the archive table.

Move to the next record in rstUniqueTrips.

Loop Step 3 until rstUniqueTrips.eof is true (no more unique trip numbers).




By the way the above assumes you want to add all lines of the text file into the tables. If you want to only add 1 line for each trip (just the trip number for example), you can just add that value from the rstUniqueTrips, instead of doing the select from the temp table.

 

[lionelbrown]

I may have misunderstood the archive part in my last post.

If you find a trip in a text file that is already in TableA and you want to move those values from TableA to the archive instead of appending the values from the temp table then:

the append would just select from TableA instead of the temp table, and then delete those rows from TableA.

If you want to make sure that they aren't moved to the archive and then added to TableA again (for example if the user processes the same text file more than once), then you would check to see if the trip is in TableA OR the archive table in step 3 before appending the values to TableA (if not in either, append to TableA ... if in TableA move rows from TableA to archive ... if in archive, skip and go to next unique trip number)

 

[MatsHulten]

Or you could do it this way:

Create a dissconnected recordset and populate it with the contents of the file (rsOffline). ('Fraid you will have to read the file row by row and manually assign the fields. Haven't found any easier way of loading a file into a recordset.)

Read the first rows TripNumber field, and use it to filter the recordset.
Open a second recordset (rsOnline) from the database something like this: "... WHERE TripNumber = " & rsOffline("TripNumber&quot

You now have two recordset where you can match the individual records. IE, if rsOnline.BOF then you add the records visible in rsOffline, otherwise you add them to TableAArchive.

Delete the filtered records from rsOffline and restore the filter to show all records.

Loop while not rsOffline.EOF and rsOffline.BOF (IE, until you have deleterd all records from the recordset.)

Good Luck
-Mats Hulten

 

[Deadline]

Thank you very much People.

That was very helpful.

Thank you once again.


RR.