help with query

[Jack Shelp]

I need some help, I am trying to make a where needed report of sub
items that are required for our jobs by linking via ODBC to the data
tables our shop management software.

I am querying a 3/32 Dia X 3/8 Dowel pin. Access returns the jobnum
for the sub item (9916). I would like to have the sub item return the
Parent partnum (9916-6)

Is there a way to subtract the item number from the fref value, an
then use the result to return to the parent part number which is
9916-6.

The shop software we use is Omnishoppro.

jobnum item partnum matdescr2 ref fref

9916-6 0 19096-@ REV A 12 12
9916 1 ITEM 1 1.00 X 2.624 X 6.00 13 13
9916 2 ND ITEM 2 3/32 DIA. X 3/8 14 14



Thanks in advance..

 

[Tom Ellison]

Dear Jack:

I would like to explain why your post is very difficult to understand.
Perhaps you could then provide some details to remove confusion.

As a woodworker myself, it is great to know the dimensions of your dowel
pins, but probably irrelevant to solving your computer problem. Here, there
is too much detail. Not a big problem, but doesn't add to clarity.

Here are some quotes from your post, and why they are not clear to someone
who is "far from your computer."

- "Is there a way to subtract the item number from the fref value, an[d]
then use the result to return to the parent part number which is 9916-6.

OK, when I look at the sample data, jobnum 9916 / item 1 I see fref is 13.
You say to subtract item number (1) from fref (13) which yields 12. Right?
So, how do I use "12" to magically obtain "9916-6" which is the "parent part
number". Clearly, there needs to be a coherent and consistent way of doing
this, and I expect you may know what this is. Could you explain it?

- "The shop software we use is Omnishoppro."

Sorry, not familiar with that. Perhaps there is someone around who does.
Do they have their own newsgroup or tech support. You might start there to
get a foundation for "what to do" and come here to find out "how to do it."
That seems likely to be a logical approach. I'm guessing no one here knows
"what to do" but given that knowledge, could tell you "how to do it."

I noticed you sequenced the data you gave in a peculiar order. In sorting
jobnum values, which must be alpha-numeric in order to contain hyphens, you
placed 9916-6 BEFROE 9916. This is not normal sorting order if that is the
sorted column. [ref] and [fref] seem more well sorted. Analyzing further,
the desired jobnum 9916-6 has item = 0 and is "like" 9916. Is that the
basis for locating it?

Tom Ellison

 

[jshelp]

On Fri, 24 Feb 2006 04:31:16 -0600, "Tom Ellison"

Below is an example of one item, Our JOB # 9916 contains 47 different
parts numbers. 5 part numbers contain the 3/32 dia. dowel pin.

Below Part number 19096 REV A is our job number 9916-6 that part
contains dowel pins.

When I query the dowel pin size it returns 9916 5 times which on our
write up is part number 23224 REV B which does not require any dowel
pins.


I want to search for sub items such dowel pins and know what job
number they our for.


d> jobnum item partnum matdescr2 ref fref
d>
d> 9916-6 0 19096-@ REV A 12 12
d> 9916 1 ITEM 1 1.00 X 2.624 X 6.00 13 13
d> 9916 2 ND ITEM 2 3/32 DIA. X 3/8 14 14>

I would like to explain why your post is very difficult to understand.
Perhaps you could then provide some details to remove confusion.

As a woodworker myself, it is great to know the dimensions of your dowel
pins, but probably irrelevant to solving your computer problem. Here, there
is too much detail. Not a big problem, but doesn't add to clarity.

Here are some quotes from your post, and why they are not clear to someone
who is "far from your computer."

- "Is there a way to subtract the item number from the fref value, an[d]
then use the result to return to the parent part number which is 9916-6.

OK, when I look at the sample data, jobnum 9916 / item 1 I see fref is 13.
You say to subtract item number (1) from fref (13) which yields 12. Right?
So, how do I use "12" to magically obtain "9916-6" which is the "parent part
number". Clearly, there needs to be a coherent and consistent way of doing
this, and I expect you may know what this is. Could you explain it?

- "The shop software we use is Omnishoppro."

Sorry, not familiar with that. Perhaps there is someone around who does.
Do they have their own newsgroup or tech support. You might start there to
get a foundation for "what to do" and come here to find out "how to do it."
That seems likely to be a logical approach. I'm guessing no one here knows
"what to do" but given that knowledge, could tell you "how to do it."

I noticed you sequenced the data you gave in a peculiar order. In sorting
jobnum values, which must be alpha-numeric in order to contain hyphens, you
placed 9916-6 BEFROE 9916. This is not normal sorting order if that is the
sorted column. [ref] and [fref] seem more well sorted. Analyzing further,
the desired jobnum 9916-6 has item = 0 and is "like" 9916. Is that the
basis for locating it?

Tom Ellison

I need some help, I am trying to make a where needed report of sub
items that are required for our jobs by linking via ODBC to the data
tables our shop management software.

I am querying a 3/32 Dia X 3/8 Dowel pin. Access returns the jobnum
for the sub item (9916). I would like to have the sub item return the
Parent partnum (9916-6)

Is there a way to subtract the item number from the fref value, an
then use the result to return to the parent part number which is
9916-6.

The shop software we use is Omnishoppro.

jobnum item partnum matdescr2 ref fref

9916-6 0 19096-@ REV A 12 12
9916 1 ITEM 1 1.00 X 2.624 X 6.00 13 13
9916 2 ND ITEM 2 3/32 DIA. X 3/8 14 14



Thanks in advance..