473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Find the missing numbers

1
I have a table named:tblIntCode, column name: interestcode, data type is Text. I have some missing interest code and i want to reuse those missing code. Example: P123, P125, N479, N482,.... I need to use the missing code P124, N480, N481...What I need is a query that can generate the code P124 or N480 in one entry.
The codes are combination in Alpha numeric,(e.g., P123, IB012)and a maximun lenght of 5 characters. Thanks!!!

JJ
Sep 29 '11 #1
4 2333
Rabbit
12,516 Expert Mod 8TB
You'll have to use VBA unless you create a table with the numbers from 0 through 999 and a table with each alpha type.

If you use VBA, you'll just need a loop to loop through and check the numbers from 0 to 999 for each alpha type.

If you use create the tables, then you can use SQL to cross join the new tables and then outer join that to your table.
Sep 29 '11 #2
ADezii
8,834 Expert 8TB
Are we talking about 26,000 possible Codes, namely:
Expand|Select|Wrap|Line Numbers
  1. A000, A001, A002...Z000, Z001....Z997, Z998, Z999
Sep 29 '11 #3
Rabbit
12,516 Expert Mod 8TB
More, you have have up to two alpha characters. But I assume the alpha characters have a meaning and so it limits them down to just a handful. But that's just an assumption.
Sep 29 '11 #4
NeoPa
32,556 Expert Mod 16PB
JanJau:
I have some missing interest code
Interesting, but nowhere do you explain what a missing interst code is nor how one would recognise one within the structure of your database. This leaves the question all but pointless - hence experts are trying to guess what your question should have been (NB a prime candidate for reporting) instead of being in a position to give a helpful answer.

Please consider providing the basic information required in order for any response to be helpful.
Sep 29 '11 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Mansoor Azam | last post by:
I have 2 tables say table1 and table2 with the same structure. Each record is identified by a field 'SerialNo'. Now there should be a total of 500000 records in both tables with serialno from 1 to...
4
by: SQLJunkie | last post by:
Here is an issue that has me stumped for the past few days. I have a table called MerchTran. Among various columns, the relevant columns for this issue are: FileDate datetime , SourceTable...
9
by: Harsha Srinath | last post by:
Athough this might not be directly relayed to C syntax, I thought some of you may find this an interesting problem :- 1.One number, in an array integers from 1 to 1,000,000 is present twice. How...
17
by: Justin Emlay | last post by:
I'm hopping someone can help me out on a payroll project I need to implement. To start we are dealing with payroll periods. So we are dealing with an exact 10 days (Monday - Friday, 2 weeks). ...
1
by: Byomokesh | last post by:
Hi All, This XML file is 2 errors. My table is 3 columns. One is extra column and another is missing one columne. How i will find those error through perl script. XML file ------------ ...
7
by: Daine | last post by:
Thanks to everyone who gave me pointers last time, I got what I needed done, now I have a new one. We have pulled the invoices from our sales systems into access. The dockets should be...
1
by: Silver8 | last post by:
Dear Expert, I have a problem on creating a function for the user to search any missing numbers in a table. The program may request the user to find for the start and end number and thus comparing...
8
by: Engrm | last post by:
In Access, I have a table of customers, each cust has sequence of numbers that are supposed to be in sequential order and some are missing, i want list of all missing sequence number of each...
20
by: kpfunf | last post by:
Hey all, I have a table of receipts used (Table Name: receipts, Field Name: receiptNumber) and a table with receipts issued (Table Name: Receipts Batches, Field Names: BatchID, BegRng, EndRng)....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.