Select, Update, Append Query through Code

[daksport00]

I am trying to create a bit of code that will select values from a
particular table, Update them such that only the first 4 characters
remain, then remove the duplicate values that are then created.

I can work this by creating 3 seperate queries, then run the macro when
the program is run. However, doing it this way creates a temporary
table which is not friendly when there are multiple users using the
database.

Here is the SQL for the queries I have right now:

Select Query:
SELECT DISTINCT [Fabrication Drawings].[Drawing Number] INTO NewFabDwg
FROM [Fabrication Drawings];

Update Query:
UPDATE DISTINCTROW NewFabDwg SET NewFabDwg.[Drawing Number] =
Left([Drawing Number],4);

Append Query:
INSERT INTO AllDwgQuery ( [Drawing Number], [Drawing Name], [Furnace
Model], [Job Number], [Serial Number], [Size], Author, [Date], [Current
Revision] )
SELECT [Fabrication Drawings].[Drawing Number], [Fabrication
Drawings].[Drawing Name], [Fabrication Drawings].[Furnace Model],
[Fabrication Drawings].[Job Number], [Fabrication Drawings].[Serial
Number], [Fabrication Drawings].Size, [Fabrication Drawings].Author,
[Fabrication Drawings].Date, [Fabrication Drawings].[Current Revision]
FROM [Fabrication Drawings];

************************
The background for this is that I am creating a database program that
will Create and Organize drawings for my company. These queries are
used to create a list of current drawing numbers whenever a user
selects to create a new drawing or update info for an old drawing.

Drawing numbers look like FD01-01, FD01-02, FD01-03, FD02-01, etc. The
first part (FD01) relates to a specific customer order, the second part
(-01, -02) identifies the drawing itself.

The program is setup such that when you go to create a new drawing
number, you are presented with a form that has a combobox that shows
only the first part of the drawing number, and a text box where a user
will input the second part, or you can type NEW in for the first part
of the drawing number, and the program will create a new drawing number
for you.
************************
Hopefully this is enough info to show what I am trying to do and why I
am trying to do it.

Any help I can get on this would be much appreciated.

-Dave

 

[John Spencer]

This query should give you a unique list of the first 4 characters in the
field [Drawing Number]

SELECT Distinct Left(Drawing Number,4) as NewFabDwg
FROM [Fabrication Drawings]
ORDER BY Left(Drawing Number,4)

You could use the above to populate a combobox.

Beyond that I am not quite sure what you are doing. Although it sounds as
if you need to be using two fields in your [Fabrication Drawings] table to
identify the drawings. One to hold the Prefix (first 4) (?CustomerOrderID)
and a a second to hold the remainder of the [Drawing Number] (?DrawingNo).
You can always combine the two parts when you need to display them as one
item.

From the 3 queries you posted I am unable to detemine the relationship
between the table NewFabDwg and your Insert into AllDwgQuery.

I am trying to create a bit of code that will select values from a
particular table, Update them such that only the first 4 characters
remain, then remove the duplicate values that are then created.

I can work this by creating 3 seperate queries, then run the macro when
the program is run. However, doing it this way creates a temporary
table which is not friendly when there are multiple users using the
database.

Here is the SQL for the queries I have right now:

Select Query:
SELECT DISTINCT [Fabrication Drawings].[Drawing Number] INTO NewFabDwg
FROM [Fabrication Drawings];

Update Query:
UPDATE DISTINCTROW NewFabDwg SET NewFabDwg.[Drawing Number] =
Left([Drawing Number],4);

Append Query:
INSERT INTO AllDwgQuery ( [Drawing Number], [Drawing Name], [Furnace
Model], [Job Number], [Serial Number], [Size], Author, [Date], [Current
Revision] )
SELECT [Fabrication Drawings].[Drawing Number], [Fabrication
Drawings].[Drawing Name], [Fabrication Drawings].[Furnace Model],
[Fabrication Drawings].[Job Number], [Fabrication Drawings].[Serial
Number], [Fabrication Drawings].Size, [Fabrication Drawings].Author,
[Fabrication Drawings].Date, [Fabrication Drawings].[Current Revision]
FROM [Fabrication Drawings];

************************
The background for this is that I am creating a database program that
will Create and Organize drawings for my company. These queries are
used to create a list of current drawing numbers whenever a user
selects to create a new drawing or update info for an old drawing.

Drawing numbers look like FD01-01, FD01-02, FD01-03, FD02-01, etc. The
first part (FD01) relates to a specific customer order, the second part
(-01, -02) identifies the drawing itself.

The program is setup such that when you go to create a new drawing
number, you are presented with a form that has a combobox that shows
only the first part of the drawing number, and a text box where a user
will input the second part, or you can type NEW in for the first part
of the drawing number, and the program will create a new drawing number
for you.
************************
Hopefully this is enough info to show what I am trying to do and why I
am trying to do it.

Any help I can get on this would be much appreciated.

-Dave

 

[daksport00]

That code works perfectly.

I changed it around slightly to allow me not to use the newfabdwg
table, as this was a temp table created by the above queries.

strSQLFabDwg = "SELECT DISTINCT Left([Drawing Number],4) " & _
"FROM [Fabrication Drawings]"
cmbFDwgNumber.RowSource = strSQLFabDwg
cmbFDwgNumber.Requery

Sry for the error in the query. I pasted the wrong one.

This query should give you a unique list of the first 4 characters in the
field [Drawing Number]

SELECT Distinct Left(Drawing Number,4) as NewFabDwg
FROM [Fabrication Drawings]
ORDER BY Left(Drawing Number,4)

You could use the above to populate a combobox.

Beyond that I am not quite sure what you are doing. Although it sounds as
if you need to be using two fields in your [Fabrication Drawings] table to
identify the drawings. One to hold the Prefix (first 4) (?CustomerOrderID)
and a a second to hold the remainder of the [Drawing Number] (?DrawingNo).
You can always combine the two parts when you need to display them as one
item.

From the 3 queries you posted I am unable to detemine the relationship
between the table NewFabDwg and your Insert into AllDwgQuery.

I am trying to create a bit of code that will select values from a
particular table, Update them such that only the first 4 characters
remain, then remove the duplicate values that are then created.

I can work this by creating 3 seperate queries, then run the macro when
the program is run. However, doing it this way creates a temporary
table which is not friendly when there are multiple users using the
database.

Here is the SQL for the queries I have right now:

Select Query:
SELECT DISTINCT [Fabrication Drawings].[Drawing Number] INTO NewFabDwg
FROM [Fabrication Drawings];

Update Query:
UPDATE DISTINCTROW NewFabDwg SET NewFabDwg.[Drawing Number] =
Left([Drawing Number],4);

Append Query:
INSERT INTO AllDwgQuery ( [Drawing Number], [Drawing Name], [Furnace
Model], [Job Number], [Serial Number], [Size], Author, [Date], [Current
Revision] )
SELECT [Fabrication Drawings].[Drawing Number], [Fabrication
Drawings].[Drawing Name], [Fabrication Drawings].[Furnace Model],
[Fabrication Drawings].[Job Number], [Fabrication Drawings].[Serial
Number], [Fabrication Drawings].Size, [Fabrication Drawings].Author,
[Fabrication Drawings].Date, [Fabrication Drawings].[Current Revision]
FROM [Fabrication Drawings];

************************
The background for this is that I am creating a database program that
will Create and Organize drawings for my company. These queries are
used to create a list of current drawing numbers whenever a user
selects to create a new drawing or update info for an old drawing.

Drawing numbers look like FD01-01, FD01-02, FD01-03, FD02-01, etc. The
first part (FD01) relates to a specific customer order, the second part
(-01, -02) identifies the drawing itself.

The program is setup such that when you go to create a new drawing
number, you are presented with a form that has a combobox that shows
only the first part of the drawing number, and a text box where a user
will input the second part, or you can type NEW in for the first part
of the drawing number, and the program will create a new drawing number
for you.
************************
Hopefully this is enough info to show what I am trying to do and why I
am trying to do it.

Any help I can get on this would be much appreciated.

-Dave