Populating one field first, rest later

[Jay]

Hi, I posted a request yesterday, but it didn't seem to go through, so here
we go again. Any help will be greatly appreciated.
I have the following table(MS2003):
tJobNos
JobNos - num / PK
BatchNo - txt
BatchDate - dtm
OrderNo - txt
OrderDate - txt
TyreID - txt / FK
PattAskID - num / FK
PattGet - num / FK
etc.

My DB's main function is to track smooth tyres that get sent to the factory
for retreading. These tyres get written up in a Job Ticket Book where each
Ticket has a pre-determned number, and each book has a set amount of tickets,
ie 7100401-7100500.
I would like to load the complete job ticket book once off, and afterwards
(as the tyres get written up) add the rest of the record info for that
particular Job Ticket.
There are two reasons for doing this: 1) to avoid duplicates, and 2) to
ensure that every single job ticket gets captured so that none gets
overlooked/skipped.
Any insight would be greatly appreciated

 

[NevilleT]

Hi Jay

One way to approach it is to have a function that creates new records in a
batch. For example it might create 100 records from 7100400 to 7100500.
When it is updated - for example you could check if a field was no longer
null or blank, you create the next batch. If the job number is greater than
7100450 you create 7100501 to 7100600..

Neville Turbit
www.projectperfect.com.au

 

[Clifford Bass]

Hi Jay,

You could use a numbers table and an append query to add the desired
rows. This is a variation of an idea posted by another person.

Create a table named "tblNumbers" with one column, named "The_Number",
which will be an integer and will be the primary key. Create ten rows, one
for each integer between 0 and 9, inclusive.

The append query, which will allow for up to 1000 tickets. Create a
new query and cancel the Add Table dialog box. Change to the SQL View and
copy and paste the following into the window, replacing the existing text.
Change to Design View to see how it looks in the designer. Try running it
using the Exclamation point. It will ask for starting and ending ticket
numbers.

PARAMETERS [Starting Ticket Number:] Long, [Ending Ticket Number:] Long;
INSERT INTO tJobNos ( JobNos )
SELECT [Starting Ticket
Number:]+[Hundreds].[The_Number]*100+[Tens].[The_Number]*10+[Ones].[The_Number] AS Ticket_Number
FROM tblNumbers AS Hundreds, tblNumbers AS Tens, tblNumbers AS Ones
WHERE ((([Starting Ticket
Number:]+[Hundreds].[The_Number]*100+[Tens].[The_Number]*10+[Ones].[The_Number])<=[Ending Ticket Number:]));

Hope this helps,

Clifford Bass