Data Extraction Problem

[RD Wirr]

I have a list of data organized in columns with 1000's of rows. One of the
columns contains Serial Numbers which can be present multiple times referring
to a Lot of material and also batches that are taken from that same Lot of
Material. When a Lot is used up there is another "Complete" column that marks
the lot=TRUE. I need to extract a list of the Serial Numbers that are not yet
complete. Just the unique numbers rather than the entire list of instances.
So I have two ways to show that the Lot is complete: One of the records with
the specific S/N is Marked TRUE or the sum of the transactions for that
specific S/N =0. The data looks something like this.

Item Qty Stock S/N Type Complete
Item1 0.2 10-01 Lot
Item2 0.3 10-02 Lot
Item1 -0.2 10-01 Lot TRUE
Item4 10 10-01 Batch

I need to extract this data dynamically since the database is always growing
and this query is used in a Validation Dropdown List.

I have tried Filters which couldn't really get a unique list with these
conditions and also Pivot tables which came close but I couldn't find a way
to hide the S/Ns with 0 values. If anyone has an idea how to do this, I will
very much appreciate leaning about it.

Thanks in advance,
RDW

 

[RD Wirr]

Hi Duke,

Thanks for the tip. This seems like a good idea but I am having a little
trouble with getting it to work. I keep getting a "Syntax Error in From
Clause" and then MS Query won't generate an SQL statement. This happens using
my data and other more simple test data. I have searched MS KnowledgeBase but
there is nothing there and nothing in the Help File about this problem.
Anyway, I suspect there may be something broken in my excel program.

Aside from that, I'd like to ask you a question about this method. I believe
I have to have a separate outside query Workbook to run the query while my
data Workbook is closed, is that correct? This could be a problem because my
Data Workbook is the working database that we enter transactions in and get
reports out of during the workday. I have to be able to update the lists
without shutting it down. Also, for my own education, is there something
special about using a list vs. a named range for the query data? My data is
in a dynamic named range (that I used to define the List) but could I just
use the range name instead of creating a list?

Thanks again for the help.
Regards,
RDW

 

[Duke Carey]

RDW -

I have not run in to that syntax problem before - can you get the query to
work from within MS Query? From within MS Query you can view the SQL
statement (click on the SQL button). If you want to post the SQL code here
I;ll take a look at it.

There is nothing magic about a list, only that it autmatically adjusts the
named range to include all data as your list expands. If you know how to
create a dynamic range name, I think that works just as well.

 

[RD Wirr]

Hi Duke,
That's the problem. MS Query give this error message when I try to build a
query with the tables referenced in the Query grid. When I use the Query
wizard I get the error after I have added the tables. Then it allows me to
edit the query from the Query window, the one that looks like an Access query
window, after creating joins with the tables (I'm experimenting with someone
else's clean data now http://www.exceluser.com/explore/msquery1_1.htm just to
make sure it's not my data). This pops up error messages along the way but
eventually lets me pretend to enter some criteria in the grid but not to run
a query. I click the SQL button and the sql window is empty. I have
researched this error on the internet and found a few people complaining
about it but in most cases, it is data type mismatch with other databases and
VBA routines. Since I can generate any SQL to analyze, I'm kind of stuck.

Well anyway, don't worry if this exceeds the scope of our discussion and
your time. I appreciate your assistance very much and will continue to plug
away at this problem but also looking for other possible solutions. Maybe I
need to consider reorganizing my data.

Thanks for the info on Lists. I couldn't see in the Excel Help that they
actaully act as an expandable range. That explains why you chose that method.
Good idea. Since I already have the data in a dynamic range, I'll just
continue with that.

Thanks n regards,
RDW

 

[Duke Carey]

Sorry that you're getting that error - I have never had any kind of hiccup
with the Query tool and don't have any advice for you.

Good luck