Remove Characters From Table / Eliminate Duplicate Values

[daksport00]

I am creating a database program for my company.

I am on a part right now where I am dealing with Drawing Numbers,
namely creating, updating, maintaining.

The particular drawing series that I am working with is Fabrication
Drawings. These are logged by the job that they pertain to.

A sample drawing number is FB12-02, where FB12 is the next number in an
incremental list, and -02 refers to the drawing type.

I already have a table that is setup with all the existing numbers in
it. My problem is on the subject of creating a new drawing.

I would like to have a combo box where the user can select the F?-##
number from the drop down, and be able to add another -## to it.

So, what I need is to create a query, or do this through code, where I
can eliminate the -## form all the drawings in the table, and be able
to eliminate duplicate values, so that the list doesnt have the same
number (say FB12) 12 times (if there happenes to be 12 drawings for
that job).

Yea, I am bad at explaining things, but I hope this is enough to get
across what I am looking to do.

I envision that there will be use of the command LEFT([Drawing
Number],4), Im just not sure about the duplicate values.

 

[Jon Ley]

Design yourself a query that gets back the drawing numbers. All you need to
do to just get back the unique ones is open the query properties window
(while in design view of the query) and change 'Unique Values' to Yes.

With regard to the Left([Drawing Number], 4), what happens when you get to
FB100-02? There are a couple of possible answers to this question. You could
either split up the drawing number into its constituent parts and put each
part in a separate field. This way, you won't have to 'parse' the number to
strip out the bit before the dash. When it comes to displaying the drawing
number to users, it is very easy to put the parts back together again to
match the format that they are used to.

If for whatever reason you are unable to adopt this approach, the other
alternative would be to use

Left([Drawing Number], InStr([Drawing Number], "-") - 1)

Hope this is useful,

Jon.

 

[daksport00]

Design yourself a query that gets back the drawing numbers. All you need to
do to just get back the unique ones is open the query properties window
(while in design view of the query) and change 'Unique Values' to Yes.

Ok, I will try this out and get back to you. I knew there was a
setting to not allow duplicate values, just couldn't remember what it
was.

With regard to the Left([Drawing Number], 4), what happens when you get to
FB100-02? There are a couple of possible answers to this question. You could
either split up the drawing number into its constituent parts and put each
part in a separate field. This way, you won't have to 'parse' the number to
strip out the bit before the dash. When it comes to displaying the drawing
number to users, it is very easy to put the parts back together again to
match the format that they are used to.

When you get to FB100-02, it becomes FC00-02.

 

[daksport00]

Ok, I got this to work, somewhat.

Basically, I have:
a make-table query that pulls the drawing numbers into another table
an update query that formats the number the way I need it
a select query that eliminates the duplicate values

This is all run by a Macro, which is initiated whenever I click the
option button to create a new Fabrication drawing, this way you always
get a new list of drawings.

The only thing I have left for this entire project is incrementing the
alphabetic character.

 

[daksport00]

ok, I have having some problems using a macro to do this. The macro,
first, opens the last query (because it is a select query). I can add
code to close the query, but then the macro will not run again after
the first time.

What I am looking to do, it be able to click on an option button, then
get values from 1 column of a particular table, eliminate the last 3
characters from these values, then remove the duplicate values that are
created therein.

Right now, I have a make table query to copy the values to a second
table.
An Update query that removes the unwanted characters
and a Select query to remove the duplicate values.

Is it possible to do something like this using SQL code??