Add separate record query

  • Thread starter Thread starter h2fcell
  • Start date Start date
H

h2fcell

Hello,
I’m using Access 2007 and I need help building a query that does a specific
task.
I have a table with the four following fields.
1. oid type Number
2. unique_id type Text
3. ticket_number type Text
4. ticketed_date type Date/Time
The issue is the ticket_number field. It can contain records like
7385226878
Or
7385226961/62

I need to take the records like 7385226961/62 and replace it with two
separate records using the same oid, unique_id and ticketed_date of the
original record.
19924123, 3, 7385226961, 11/24/2008
19924123, 3, 7385226962, 11/24/2008

My alternative, which is not ideal, would be to create a new field called
second_ticket.
Any recommendations are greatly appreciated as my brain has programmers block.
Thanks.
 
If you only have records like 7385226961/62 but never like 7385226961/65 then
it is easy.
UNTESTED BACKUP DATABASE UNTESTED BACKUP DATABASE
Create and append query like this --
INSERT INTO YourTable ( oid, unique_id, ticket_number, ticketed_date )
SELECT oid, unique_id, Left([ticket_number], 8) & Right([ticket_number], 2),
ticketed_date
FROM YourTable
WHERE InStr([ticket_number], "/") >0;

Then an update query --
UPDATE YourTable SET YourTable.[ticket_number] = Left([ticket_number], 8)
WHERE InStr([ticket_number], "/") >0;
 
Back
Top