Duplicate record

[timely]

I am working with data that has to be pulled by way of txt file due to the database is propriatary. The information goes into a excel spreedsheet first from the txt file so that I can cut it up and grap only what I need. The next step is sending it to an access database. The only thing is that the report is ran every 4 hours and the txt file will hold the last 2 weeks at that given time. So there is 42 periods of information and 41 peices of it will be repeated each time the report is ran. What I need is that VBA compare what is in the data base versus what is on the spreadsheet; comparing date, shift, and middle/end of shift (all included in the report). That way then I only send what is new to the database.

Basically saying I not sure how to do a exception rule between excel and access.

Also this all needs to be activated from excel. This is a user form that I am creating for tracking.

Thank you,
Tim

 

[SkipVought]

Hi,

Could you post an example of the 41 periods that are repeated and the 1 that is not?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[timely]

Okay here is what would show up in the excel spreedsheet when I ran it on 8/25 Shift A at end of shift. Well the report would be ran again for 8/25 Shift B and T would be M. You would only have one new line or after the weekend you may have 12 new lines and 36 old due to no one here to run the report during the weekend. It is possible at times the number of line already in the database and what is new may vary due to various situations.

In the case of data below everything through 8/25 Shift A T is M would have already been copied to the database but not the new line.

Date Shift T %SISCrew StdProd #Emp<Min
08/25/05 A E 0 49 23
08/25/05 A M 0 59 18
08/25/05 C E 0 60 18
08/25/05 C M 0 74 12
08/24/05 B E 0 69 13
08/24/05 B M 0 83 11
08/24/05 A E 0 55 18
08/24/05 A M 0 68 17
08/24/05 C E 0 71 11
08/24/05 C M 0 85 9
08/23/05 B E 0 61 16
08/23/05 B M 0 83 10
08/23/05 A E 0 46 27
08/23/05 A M 0 57 16
08/23/05 C E 0 56 17
08/23/05 C M 0 73 12
08/22/05 B E 0 59 18
08/22/05 B M 0 73 12
08/22/05 A E 0 47 25
08/22/05 A M 0 59 19
08/22/05 C E 0 57 20
08/22/05 C M 0 72 12
08/21/05 B E 0 55 16
08/21/05 B M 0 81 7
08/21/05 A E 0 54 18
08/21/05 A M 0 73 11
08/21/05 C E 0 73 8
08/21/05 C M 0 82 5
08/20/05 B E 0 46 23
08/20/05 B M 0 69 11
08/20/05 A E 0 64 18
08/20/05 A M 0 73 15
08/20/05 C E 0 65 14
08/20/05 C M 0 76 8
08/19/05 B E 0 66 13
08/19/05 B M 0 82 10
08/19/05 A E 0 52 21
08/19/05 A M 0 72 13
08/19/05 C E 0 60 16
08/19/05 C M 0 72 13
08/18/05 B E 0 68 13
08/18/05 B M 0 87 8
08/18/05 A E 0 52 24
08/18/05 A M 0 61 19
08/18/05 C E 0 59 17
08/18/05 C M 0 83 10
08/17/05 B E 0 64 14
08/17/05 B M 0 83 10
08/17/05 A E 0 47 25
08/17/05 A M 0 64 13
08/17/05 C E 0 65 11
08/17/05 C M 0 79 8
08/16/05 B E 0 55 16
08/16/05 B M 0 73 10
08/16/05 A E 0 49 21
08/16/05 A M 0 63 16
08/16/05 C E 0 63 15
08/16/05 C M 0 76 8
08/15/05 B E 0 55 17
08/15/05 B M 0 74 10
08/15/05 A E 0 46 24
08/15/05 A M 0 53 16
08/15/05 C E 0 58 18
08/15/05 C M 0 73 9
08/14/05 B E 0 42 19
08/14/05 B M 0 69 9
08/14/05 A E 0 47 22
08/14/05 A M 0 67 14
08/14/05 C E 0 64 11
08/14/05 C M 0 68 11
08/13/05 B E 0 51 18
08/13/05 B M 0 73 9
08/13/05 A E 0 58 19
08/13/05 A M 0 69 12
08/13/05 C E 0 63 12
08/13/05 C M 0 72 10
08/12/05 B E 0 63 13
08/12/05 B M 0 86 10
08/12/05 A E 0 65 14
08/12/05 A M 0 70 13
08/12/05 C E 0 62 14
08/12/05 C M 0 76 11
08/11/05 B E 0 69 10
08/11/05 B M 0 79 10

 

[SkipVought]

You're assuming that I understand YOUR PROCESS and I do NOT. Please provide explanation clearly, concisely and completely.

So this is what you IMPORT from a text file.

What is the next thing that happens?

What data might be added?

What is the logic that must be applied?

What is the result that you need to get?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[timely]

Okay, lets say that the data you see is the first time that data will be sent to access. The same report will be ran again in 4 hours or in 48 hours but now the first line or first 12 lines will be new data, not already in the database. At this point the new data is the only information I want going into the database. The logic at the point that I am sending it to the database is:

Check everyline
If(lines Date, shift and T) are not already in database
Then place that line of information into the database
Else go to the next line and repeat

This way no matter when we run the report and manipulate it in excel, only the new data goes to Access and no repeats.

Tim

 

[SkipVought]

The simplest way would be to QUERY the Access Table directly from Excel via MS Query using menu item data/Get External Data/New Database Query......

Once the QueryTable has been established, all you need to do is Data/refresh to get current data.

then do a lookup of the data you posted against the data you queried. The data that does not match is new.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

Here some samlpe SQL for the above solution

SELECT A.Date, A.Shift, A.T, A.`%SISCrew`, A.StdProd, A.`#Emp<Min`

FROM  `D:\My Documents\vba\adb`.`Sheet1$` A

Where A.Date&A.Shift&A.T Not In (SELECT A.Date&A.Shift&A.T From `D:\My Documents\vba\adb`.`'Sheet1 (2)$'` A)

where 'Sheet1 (2)$' us the query result from ACCESS.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]