Huge Database - Need help with query please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to setup a database query on all the items we stock in our warehouse.
We have 88,000 part numbers and right now I just have everything in 2 tables,
but I know I will have to change that in order for a query to work.

What we use is a 3 digit code for the mfg., the part number, and a
description, however, none of those fields are uninque. For example - we
would have an AC Delco spark plug part number 44 in our system as: ACD (mfg
code) 44 (part number) spark plug (description) everything else we have in AC
Delco will also have the ACD mfg code and every other spark plug we sell will
have 'spark plug' for the description and with 88,000 part numbers there is
bound to be more than one part number 44 under some other mfg code. The
ultimate goal is to have web users filter the db by those 3 fields. If they
want to see everything we have in ACD, or everything we have in spark plugs,
etc. My access is very rusty, I haven't used it in more than 2 years and my
books from college are no help. Anyone have any advice? Suggestions? Links
to more information?

Thanks in advance.
 
I need to setup a database query on all the items we stock in our warehouse.
We have 88,000 part numbers and right now I just have everything in 2 tables,
but I know I will have to change that in order for a query to work.

What we use is a 3 digit code for the mfg., the part number, and a
description, however, none of those fields are uninque. For example - we
would have an AC Delco spark plug part number 44 in our system as: ACD (mfg
code) 44 (part number) spark plug (description) everything else we have in AC
Delco will also have the ACD mfg code and every other spark plug we sell will
have 'spark plug' for the description and with 88,000 part numbers there is
bound to be more than one part number 44 under some other mfg code. The
ultimate goal is to have web users filter the db by those 3 fields. If they
want to see everything we have in ACD, or everything we have in spark plugs,
etc. My access is very rusty, I haven't used it in more than 2 years and my
books from college are no help. Anyone have any advice? Suggestions? Links
to more information?

Thanks in advance.
Click to expand...

I'm not at all sure what the problem is. First off, 88,000 rows is a
rather modest table - ten times that is getting a bit large, 8 million
rows is probably getting a bit too big to be comfortable.

Secondly, an Access query can use any (well, hundreds) of criteria. If
I understand aright, you have three numeric (? or text?) fields for
Manufacturer, Part Number, and Description; you can query any of these
fields, individually or in combination.

What specific problem are you having?

And why do you have TWO tables? You've described one. What's the
other?

John W. Vinson[MVP]
 
If you haven't already look into the northwind sample database included with
access. It may give you some ideas on how to get started.
 
OK I'll do that, I think I have all those cases downloaded to this computer
already.
Thanks for the suggestion.
 
I have two tables because the first table won't take any more records after i
reached 65,536 rows.
 
I have two tables because the first table won't take any more records after i
reached 65,536 rows.
Click to expand...

That sounds like your table is in Excel, rather than Access. I have
several databases with over half a million rows.

Please explain more about your context.

John W. Vinson[MVP]
 
haha no, i may not be a pro with access but i do know the difference between
access and excel. i have 4 headers with one being the primary key and 3
being the fields i need - mfg, part number, and description.

there are 88,000+ rows of that information, but after i got to 65536 i had
to put the remaining records into a different table. maybe..i bet i know
what the deal is, i don't remember for sure, but i bet i imported the info
into the table from an excel document - we have to update all our part
numbers in excel every month so we can send those spreadsheets out to our
customers in excel format. the information in this table comes from that
spreadsheet so nobody would have to sit and key in 90,000 records every
month. can you just copy and paste the info into the table from the excel
document instead of importing it?

wow, no wonder i couldnt get the query to work correctly.

thank you.
 
haha no, i may not be a pro with access but i do know the difference between
access and excel. i have 4 headers with one being the primary key and 3
being the fields i need - mfg, part number, and description.
ok...

there are 88,000+ rows of that information, but after i got to 65536 i had
to put the remaining records into a different table. maybe..i bet i know
what the deal is, i don't remember for sure, but i bet i imported the info
into the table from an excel document - we have to update all our part
numbers in excel every month so we can send those spreadsheets out to our
customers in excel format. the information in this table comes from that
spreadsheet so nobody would have to sit and key in 90,000 records every
month. can you just copy and paste the info into the table from the excel
document instead of importing it?
Click to expand...

That would do it. Yes, you can use File... Get External Data... Link
to link to a spreadsheet; you can update existing records by creating
an Update query, joining the primary key of your Access table to the
pk in the linked table, and updating the fields to
[Linkedtablename].[fieldname] (using your own table and field names of
course). You can also run an Append query based on the (two) linked
tables into your main table; only records with a new primary key will
be added.
wow, no wonder i couldnt get the query to work correctly.
Click to expand...

Since I've not yet seen the query or really understood the problem I
can't comment... <g>

John W. Vinson[MVP]
 
Back
Top