Query that finds value within a range in any of 10 different fields

[BigFamilyGuy]

I have a database that I need to find values that are between a certain range (300-399) in 10 different fields (1, 2, ....10) ONLY IF there is data in another field (WEEKE). I then need to create a report that totals the quantity of parts (Total Req) for each different number found in fields 1-10). The Total Req field is not shown in this screenshot. I am a complete noob to access, so please explain your solution fully. Thanks in advance for your help.

[June7]

This data is not in normalized structure and will require manipulating records into a structure that will allow performing aggregate calcs. This means a UNION query. There is no wizard or designer for UNION, must type (or copy/paste) into the SQL View window of the query builder.

SELECT WEEKE, KOCH, OTHER, [Total Req], [1] AS MachineNumber, "01" AS Source FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [2], "02" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [3], "03" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [4], "04" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [5], "05" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [6], "06" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [7], "07" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [8], "08" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [9], "09" FROM tablename
UNION SELECT WEEKE, KOCH, OTHER, [Total Req], [10], "10" FROM tablename;

Now use that query as the data source for a GROUP BY (Totals) aggregate query.

SELECT MachineNumber, Sum([Total Req]) AS TotalOfParts FROM Query1 WHERE Not WEEKE Is Null AND MachineNumber BETWEEN 300 AND 399 GROUP BY MachineNumber;

Or use the UNION query as the RecordSource for a report and use report Grouping & Sorting with aggregate calcs features. Report will allow display of detail records as well as summary calcs in group footer section.

[BigFamilyGuy]

Thanks for your quick response, June7. I was able to take your commands for the first query and make them work. I am waiting for Management to approve further work on this project before I proceed deeper. The hurdle that I now see is that this will need to be created in a Master Database file and then the data will need to be imported or extracted from each of these individual databases. These files (like the screenshot) are created as an export from a program that produces a cutlist for our saw. We produce 10-30 or more cutlists a day, so the Master Database will need to be developed in such a way to be able to import from multiple cutlist files and produce a report on a daily, weekly, or possibly monthly basis. This process of importing/extracting will need to be fairly painless and quick in order to justify the effort of developing this Master Database and the Queries and Reports. To make things even more interesting, this process will be temporary, because we are implementing an ERP software that will be able to produce this information but only after we update all of our current parts database with these machine numbers (Tens of thousands of parts).