Finding all instances of an item appearing in a table

[KyleMac]

Hi guys,



I have a database with 15 fields for item input. Each field for an item has a field for quantity. (i.e. I1 and Q1, I2 and Q2, etc.). I need to search for each unique item in the entirety of the table and for every "I" field each item is in, grab the associated "Q" field. Ex: I1: Boots Q1: 3; qty = qty + Q1. Next record there are boots in I4, so pull Q4 and add to the total qty...also, there could be another pair of boots in I7 of that same record, so fields: I1-I15 has to be searched for every item. This in itself is not hard to code and I've tried a couple different methods but none live up to a stress test. When I throw 50K entries in and try and run the grab/count it is incredibly slow. First I tried brute force by running several do while loops, then I tried filtering. I didn't notice any improvement in speed. Any hints on speeding up the search would be greatly appreciated!

Ah, I should also say the records will be filtered between dates and another field, so it's not literally every record in the table being searched.

Kyle

[June7]

This is not a normalized data structure and this is only one experience of frustration you will face.

What do you mean by 'searched for every item'? Are you trying to find records for a specified criteria or are you trying to aggregate ALL items? How many items are there?

A UNION query can rearrange the data into a normalized structure then that query (like a table) can be the source for filtering and aggregate query. However, I don't know if even that will be fast enough with 50K records.

SELECT ID, datefield, 1 AS Source, I1 AS Item, Q1 As Qty FROM tablename
UNION SELECT ID, datefield, 2, I2, Q2 FROM tablename
...;

[orange]

I agree that non-normalized structures will be the source of much frustration if your are using relational database management software.
Do yourself a big favor and restructure/normalize your database tables- the sooner the better.

Work through some tutorials at Rogers Access Library dealing with database design and get a better grasp of basic concepts.
Good luck.