Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

create vlookup to exclude list of numbers from certain date forward

Status
Not open for further replies.
2Plan

2Plan

MIS
Mar 29, 2005
55
US
How can I create a vLookup table to exclude a list of numbers (i.e., the seq column in data set) from a certain date forward from the data set below (data set will be from Sep thru Nov)? I will import the data set below into an access DB first.

ts,loc_n,bus,route,ttp,grp,des,seq,tpbc
'2010-10-01 04:51:15.000',1,2208,1,2,1,12,453,4
'2010-10-01 05:05:44.000',2,2016,4,2,1,12,799,4
'2010-10-01 05:13:55.000',1,2203,300,2,1,12,6087,4
'2010-10-01 05:14:27.000',1,2208,1,2,1,12,6284,4
'2010-10-02 17:28:56.000',1,7425,3,2,1,12,5956,4
'2010-10-02 17:28:58.000',1,2217,1,2,1,12,6635,4
'2010-10-02 17:28:59.000',1,7425,3,2,1,12,6524,4
'2010-10-02 17:30:56.000',1,8946,20,2,1,12,392,4
'2010-10-02 17:30:59.000',1,8946,20,2,1,12,1040,4
'2010-10-02 17:32:49.000',1,7433,331,2,1,12,2673,4
'2010-10-02 17:32:59.000',1,8922,1,2,1,12,3681,4
'2010-10-02 17:33:30.000',1,8946,20,2,1,12,2819,4
'2010-10-02 17:34:45.000',1,2214,1,2,1,12,2649,4
'2010-10-02 17:35:28.000',1,2256,3,2,1,12,5718,4
'2010-10-02 17:35:55.000',1,2222,1,2,1,12,324,4
'2010-10-05 20:31:13.000',1,7006,30,2,1,12,1802,4
'2010-10-05 20:31:19.000',1,7440,331,2,1,12,4442,4
'2010-10-05 20:31:24.000',1,7428,331,2,1,12,1588,4
'2010-10-05 20:31:34.000',1,7428,331,2,1,12,4276,4
'2010-10-05 20:31:36.000',1,7428,331,2,1,12,7299,4
'2010-10-05 20:31:40.000',1,7428,331,2,1,12,5317,4
'2010-10-05 20:33:34.000',1,8847,3,2,1,12,3236,4
'2010-10-05 20:33:44.000',2,2008,323,2,1,12,5714,4
'2010-10-05 20:33:45.000',1,8845,1,2,1,12,3558,4
'2010-10-05 20:33:53.000',2,2008,323,2,1,12,3225,4
'2010-10-05 20:34:19.000',1,2070,300,2,1,12,4481,4
'2010-10-05 20:35:02.000',2,2017,338,2,1,12,3940,4
'2010-10-05 20:35:12.000',1,2210,1,2,1,12,2823,4
'2010-10-05 20:35:17.000',2,2031,338,2,1,12,3887,4
'2010-10-05 20:35:24.000',1,8947,1,2,1,12,4852,4
'2010-10-05 20:35:27.000',1,2210,1,2,1,12,3419,4
'2010-10-05 20:35:40.000',1,7429,10,2,1,12,6489,4
'2010-10-05 20:35:53.000',1,8921,20,2,1,12,6643,4
'2010-10-05 20:36:09.000',1,2102,1,2,1,12,109,4
'2010-10-05 20:36:22.000',2,6026,37,2,1,12,6490,4
'2010-10-05 20:36:57.000',1,2217,300,2,1,12,3605,4
'2010-10-05 20:37:01.000',1,2216,1,2,1,12,1046,4
'2010-10-05 20:37:04.000',1,2216,1,2,1,12,1739,4
'2010-10-05 20:37:06.000',1,2061,1,2,1,12,4525,4
'2010-10-05 20:37:06.000',1,2216,1,2,1,12,5332,4
'2010-10-05 20:37:23.000',2,6018,325,2,1,12,4100,4
'2010-10-05 20:37:24.000',1,7440,331,2,1,12,2088,4
'2010-10-05 20:37:27.000',1,2102,1,2,1,12,731,4
'2010-10-05 20:37:27.000',1,8947,1,2,1,12,4746,4
'2010-10-05 20:37:37.000',1,2102,1,2,1,12,1822,4
'2010-10-05 20:37:46.000',1,2102,1,2,1,12,2077,4
'2010-10-05 20:37:49.000',1,2102,1,2,1,12,358,4
'2010-10-05 20:37:51.000',1,2102,1,2,1,12,5848,4
'2010-10-05 20:38:02.000',1,2063,3,2,1,12,1707,4
'2010-10-05 20:38:06.000',1,2063,3,2,1,12,3036,4
'2010-10-05 20:38:08.000',1,2063,3,2,1,12,701,4
'2010-10-05 20:38:13.000',1,2063,3,2,1,12,4992,4
'2010-10-05 20:40:17.000',1,8847,3,2,1,12,1044,4
'2010-10-05 20:40:28.000',1,8847,3,2,1,12,1256,4
'2010-10-05 20:40:28.000',1,2102,1,2,1,12,214,4
'2010-10-05 20:40:31.000',1,8847,3,2,1,12,6883,4
'2010-10-05 20:40:35.000',1,8847,3,2,1,12,1469,4
'2010-10-05 20:41:17.000',2,2012,392,2,1,12,262,4
'2010-10-05 20:41:28.000',1,2210,1,2,1,12,5936,4
'2010-10-05 20:41:48.000',1,2101,300,2,1,12,5417,4
'2010-10-05 20:42:20.000',1,8947,1,2,1,12,6150,4
'2010-10-05 20:42:24.000',1,8947,1,2,1,12,3040,4
'2010-10-05 20:42:26.000',1,8947,1,2,1,12,2841,4
'2010-10-05 20:42:37.000',1,8844,20,2,1,12,1028,4
'2010-10-05 20:42:39.000',1,8913,1,2,1,12,3030,4
'2010-10-05 20:43:12.000',1,8844,20,2,1,12,2818,4
'2010-10-05 20:43:48.000',1,2220,7,2,1,12,6497,4
'2010-10-05 20:43:50.000',1,2206,1,2,1,12,160,4
'2010-10-05 20:44:06.000',1,7429,10,2,1,12,3533,4
'2010-10-05 20:44:33.000',2,2012,392,2,1,12,214,4
'2010-10-06 17:16:17.000',1,8907,10,2,1,12,5625,4
'2010-10-06 17:16:20.000',1,8944,101,2,1,12,5402,4
'2010-10-06 17:16:29.000',1,8928,300,2,1,12,1007,4
'2010-10-06 17:16:38.000',1,8901,1,2,1,12,5507,4
'2010-10-06 17:16:59.000',1,2209,7,2,1,12,2932,4
'2010-10-06 17:17:11.000',1,7060,333,2,1,12,3494,4
'2010-10-06 17:17:11.000',1,9111,982,2,1,12,2964,4
'2010-10-06 17:17:17.000',1,9303,935,2,1,12,5823,4
'2010-10-06 17:17:27.000',1,8850,300,2,1,12,3409,4
'2010-10-06 17:17:38.000',1,7404,10,2,1,12,6847,4
'2010-10-06 17:17:41.000',1,7442,325,2,1,12,1584,4
'2010-10-06 17:18:07.000',1,9303,935,2,1,12,1461,4
'2010-10-21 20:20:03.000',2,2028,19,2,1,12,560,4
'2010-10-21 20:20:05.000',1,2216,1,2,1,12,1379,4
'2010-10-21 20:20:47.000',1,8902,3,2,1,12,5008,4
'2010-10-21 20:21:24.000',2,7059,320,2,1,12,697,4
'2010-10-21 20:22:13.000',2,6028,325,2,1,12,5737,4
'2010-10-21 20:22:51.000',1,8909,1,2,1,12,4816,4
'2010-10-21 20:23:04.000',1,8918,3,2,1,12,1243,4
'2010-10-21 20:23:40.000',1,7010,311,2,1,12,3377,4
'2010-10-21 20:24:05.000',1,2103,300,2,1,12,5476,4
'2010-10-21 20:25:03.000',1,8948,20,2,1,12,881,4
'2010-10-21 20:25:32.000',1,7009,333,2,1,12,7051,4
'2010-10-21 20:26:46.000',1,7017,333,2,1,12,3899,4
'2010-10-21 20:27:06.000',1,2207,1,2,1,12,6141,4
'2010-10-21 20:28:08.000',1,2213,300,2,1,12,1007,4

The sequence list and dates to exclude are below:


1 9/14/10 12:00 AM
27 9/14/10 12:00 AM
44 9/14/10 12:00 AM
59 9/14/10 12:00 AM
68 9/14/10 12:00 AM
88 9/14/10 12:00 AM
122 9/14/10 12:00 AM
135 9/14/10 12:00 AM
226 9/14/10 12:00 AM
279 9/14/10 12:00 AM
283 9/14/10 12:00 AM
316 9/14/10 12:00 AM
318 9/14/10 12:00 AM
320 9/14/10 12:00 AM
350 9/14/10 12:00 AM
488 9/14/10 12:00 AM
603 9/14/10 12:00 AM
703 9/14/10 12:00 AM
705 9/14/10 12:00 AM
737 9/14/10 12:00 AM
759 9/14/10 12:00 AM
762 9/14/10 12:00 AM
768 9/14/10 12:00 AM
889 9/14/10 12:00 AM
927 9/14/10 12:00 AM
972 9/14/10 12:00 AM
979 9/14/10 12:00 AM
996 9/14/10 12:00 AM
999 9/14/10 12:00 AM
1119 9/14/10 12:00 AM
1124 9/14/10 12:00 AM
1139 9/14/10 12:00 AM
1141 9/14/10 12:00 AM

1723 10/6/10 12:00 AM
3092 10/6/10 12:00 AM
5766 10/6/10 12:00 AM
1119 10/6/10 12:00 AM
1565 10/6/10 12:00 AM
1575 10/6/10 12:00 AM
3701 10/6/10 12:00 AM
5638 10/6/10 12:00 AM
3885 10/6/10 12:00 AM
1456 10/6/10 12:00 AM
1988 10/6/10 12:00 AM
1085 10/6/10 12:00 AM
4365 10/6/10 12:00 AM
3126 10/6/10 12:00 AM
972 10/6/10 12:00 AM
6053 10/6/10 12:00 AM
2658 10/6/10 12:00 AM
318 10/6/10 12:00 AM
4535 10/6/10 12:00 AM
3541 10/6/10 12:00 AM
4233 10/6/10 12:00 AM
11192 10/6/10 12:00 AM
3404 10/6/10 12:00 AM
5248 10/6/10 12:00 AM
381 10/6/10 12:00 AM
954 10/6/10 12:00 AM
3631 10/6/10 12:00 AM
64 10/6/10 12:00 AM
994 10/6/10 12:00 AM
1280 10/6/10 12:00 AM
1261 10/6/10 12:00 AM
350 10/6/10 12:00 AM
882 10/6/10 12:00 AM
1558 10/6/10 12:00 AM
0 10/6/10 12:00 AM
4922 10/6/10 12:00 AM
3185 10/6/10 12:00 AM
1236 10/6/10 12:00 AM
3291 10/6/10 12:00 AM
2651 10/6/10 12:00 AM

 
Sort by date Sort by votes
How do import the 'ts' (timestamp field above) as a date. Currently, I have to import 'ts' as a text field, but I want to import as a date field.

Thanks, for any assistance
 
Upvote 0 Downvote
Not sure if I understand the question but maybe
If you import the two tables.
tblSeqNumbers
and
tblExclude

You can then do a delete query something like.


DELETE tblSeqNumbers.seq, WHERE (((tblSeqNumbers.seq) In (select seqNumber from tblExclude)) AND ((tblSeqNumbers.[TS]) Between #9/1/2010# And #11/1/2010#));
 
Upvote 0 Downvote
You can import as text
Then make 2 small update queries

updateTime1:
UPDATE tblSeqNumbers SET tblSeqNumbers.ts = Replace([ts],"'","");

updateTime2:
UPDATE tblSeqNumbers SET tblSeqNumbers.ts = Replace([ts],".000","");

Run the queries. Then you can in table design, change the TS field to time and it will work.

Or do the same clean up on the front end. Remove the ' and the .000
 
Upvote 0 Downvote
Trying to be more clear:

I need to query the above data set, while excluding certain sequences that were badlisted on a specific date.

In other words if the sequence number was badlisted on 9/13/10 then that sequence number would appear in the data set up until 9/13/10, but not after that date.

We get a list of about 50 sequence numbers each month that we have to badlist on one particular day in the month that we have to exclude from that point forward.

I'm not sure how we filter out these badlisted sequences from a certain date forward.

Thanks for any assistance.
 
Upvote 0 Downvote
Not sure if I understand.
But lets say in the exclude table I have.
Code: 
ID      Seq     excludeDate
1       1       10/5/2010
This says that after 10/5/2010 to exclude records with a sequence of 1?

Then I could query the the sequence table like
[/code]
SELECT
tblSeqNumbers.ID,
tblSeqNumbers.ts,
tblSeqNumbers.seq
...other fields
FROM
tblSeqNumbers
where
tblSeqNumbers.Seq not in (Select A.Seq from tblExclude as A where A.excludeDate < tblSeqNumbers.ts);
[/code]
This returns any records with a sequence of 1 prior to 10/5/2010 but not after.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
4K
Norman Sherlock
Norman Sherlock
ziffer68
  • Locked
  • Question
Pass Through query to create an alias for DB2 member
Replies
0
Views
2K
ziffer68
ziffer68
Moss100
  • Locked
  • Question
Find unique rent amount and date this was first charged
Replies
4
Views
658
dhookom
dhookom
Viswa.R
  • Locked
  • Question
Dimension import from Autocad to Excel
Replies
0
Views
404
Viswa.R
Viswa.R
BFroeschlKC
  • Locked
  • Question
Publisher Automation from Access Frustration
Replies
3
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top