Maybe Array? Check for a block of numbers within other blocks

[IronyImpaired]

Hello!

I am trying to create an automated inventory report for a table that I did not design and cannot change. I don’t know how to write the underlying query for this report.

The table is in SQL Server, but I am restricted to working in an Access 2003 front end.

The table tracks serial numbers: tbl_SerialNumberLog.

It does not track individual serial numbers, but only logs the bulk purchase of serial number tags and then tracks the distribution of those tags in smaller allotments to various manufacturers.

The relevant fields are:

SNL_ID Serial Number Record ID.
SNL_Status Status of the block of serial numbers for this record:
"Pending from Printer", "Received from Printer", "Sent to Warehouse", or "Sent To Mfr"
SNL_First First Serial Number in block.
SNL_Last Last Serial Number in block.
All numbers between SNL_First and SNL_Last are continguous and none are skipped.
SNL_Total Total number of serial numbers in the block.*

* I know this should be calculated, not stored, but the original designer isn't too fond of normalization or any other design best practices. :-)
I only included this field in case it was usefull for my needs. I do run regular audits to make sure that the data entry for the SNL_Total is correct, given the SNL_First and SNL_Last.

Here's how the table is used:

EXAMPLE: 5,000 Serial Number Tags are ordered and a record is entered into the tbl_SerialNumberLog:

SNL_ID Autonumber
SNL_Status "Pending from Printer"
SNL_First 15000
SNL_Last 19999
SNL_Total 5000

After the tags are rec'd, a new record is entered in the same table with a status of "Received from Printer".

In some cases the tags are shipped directly to the MFR immediately, so another record is entered for "Sent To Mfr". If the tags go the warehouse for holding, the record would be "Sent to Warehouse" and the "Sent To Mfr" would be added later when the warehouse ships to the MFR.

My task is that I need to write an inventory report showing how many serial number tags fall into each of the SNL_Status types.

The challenge is that we order and receive the tags in 5000 or 10000 increments (usually), but the MFRs order them in smaller increments (anything from 100 to the full 10000). So I can't do a straight-up compaison of a block of SN tags to see where they were shipped because they may have shipped as 1, 2...n number of mini-blocks.

I'm pretty sure that my answer lies in using arrays for comparison, but I haven't ever used arrays before and I'm not sure where to start.

I've done quite a bit with queries and I'm comfortable with VBA (not expert!), so I'm willing to go wherever the solution takes me.

Any ideas? I'm happy to follow up on suggestions, it doesn't have to be a fully fleshed solution.

Thanks very much!!

[Rabbit]

What would the end report look like? With some sample data if you can.

[patjones]

I think you can do this with a pretty straightforward SQL query utilizing a GROUP BY clause, but as Rabbit points out a sample would be good to look at first.

Pat

[IronyImpaired]

Rabbit & zepphead80,

I'm sorry about the delay in reply, I got pulled into a meeting...

For the end report, my boss wants a count of all the SN tags for each SNL_Status, along with the actual detail of the serial numbers which were sent to the warehouse but not yet shipped to the mfr.

Assuming we have ordered 100,000 tags from the printer as the sum total of our entire order history, the report might look like this:


SAMPLE REPORT WITH EXAMPLE DATA:

<<< SUMMARY CALCULATIONS AT BEGINNING OF REPORT >>>
Current Location Inventory: Current Tag Qty Per Location
----------------------------------------------------------------------------------
Pending from Printer 5,000
Headquarters* 0
Warehouse** 10,000
Sent To Mfr*** 85,000

*All the tags ever rec'd from the printer, which were not yet sent either to the warehouse or directly to the Mfr.
**All the tags ever rec'd from Headquarters (they never receive directly from the printer), which were not yet sent to the Mfr.
***All the tags ever sent to the Mfr, regardless of whether or not they were shipped from Headquarters or the Warehouse.

<<< Detail Record ID #15 for Warehouse Inventory: >>>
(all tag blocks which were sent to the warehouse, but never shipped to a Mfr)

SNL_ID Serial Number Record ID: 15
SNL_Status: "Sent to Warehouse"
SNL_First: 15000
SNL_Last: 19999
SNL_Total: 5000

<<< Subreport Begin For This Block Of Tags >>>
The following tags have not yet been shipped to a Mfr:
Tags from 15000 to 19999: 5000 tags


<<< Detail Record ID #20 for Warehouse Inventory: >>>
SNL_ID Serial Number Record ID: 20
SNL_Status: "Sent to Warehouse"
SNL_First: 30000
SNL_Last: 34999
SNL_Total: 5000

<<< Subreport Begin For This Block Of Tags >>>
The following tags have not yet been shipped to a Mfr:
Tags from 31000 to 31999: 1000 tags
Tags from 33000 to 33999: 1000 tags

The following tags have *ALREADY* been shipped to a Mfr:
Tags from 30000 to 30999: 1000 tags
Tags from 32000 to 32999: 1000 tags
Tags from 34000 to 34999: 1000 tags


<<< END SAMPLE REPORT >>>

As you can see, when the blocks of tags are shipped to the warehouse, they are frequently broken into mini-blocks to ship to Mfrs. This makes it very difficult to determine which tags are still at the warehouse since I can't simply write a query to match first and last serial numbers to determine the location of a block of serial numbers. I even tried writing some stacked queries: start with a qry of all blocks sent to the warehouse that drops all blocks sent to MFRs where there is an exact match of start and end numbers. Then, take the results and match all start numbers with start numbers sent to Mfrs (which don't match the end numbers) and re-set the start numbers based on the end number of the mini-block sent to the mfr...then continue to loop this process as I peel off each layer. Maybe I could do this through a VBA loop, but I think an array comparison might be best. The issue is that one block of tags sent to the warehouse might be broken into 50 or 60 mini-blocks parcelled out to manufacturers.

FWIW...The reason my boss wants this report is that we have been tracking inventory loosely in a poorly-maintained spreadsheet, even though we have also been entering the data for the past seven years into the sql database...we just never set up any reporting from the database. Now the clerk that tracked the spreadsheet has been let go and nobody understands how she tracked the numbers in the spreadsheet. Using the spreadsheet, my boss started to verify the current inventory at Headquarters and at the Warehouse and the rough totals look like there are some very significant discrepencies. That's why she now wants the specific numbers straight from the database and she wants to be able to send the detail report with the specific serial numbers (or blocks of serial numbers) to the warehouse for a hands-on inventory.

In the future, all the inventory will be managed from the database and not a spreadsheet.

[NeoPa]

IronyImpaired:
My task is that I need to write an inventory report showing how many serial number tags fall into each of the SNL_Status types.

This seems to imply you want a report which gives the data illustrated by your heading <<< SUMMARY CALCULATIONS AT BEGINNING OF REPORT >>> from post #4. You seem to go on then, in that post, to introduce new requirements. Rather than clarifying your original request, this actually complicates matters further (as it doesn't match the original request it is harder to fit this into a consistent whole).

That said, there has clearly been a lot of careful thinking and preparation gone into this question, which warrants congratulations. It's rare indeed for such a complicated question to be put across so clearly from the start as here. I included the above paragraph mainly because you give the impression that you will understand the importance of the point.

Access reports don't easily lend themselves to separate sections, as you seem to require, except within certain limited bounds and predefined scenarios. A Detail section can be followed by sections that can reflects totals for groups within the data; the pages; or even the whole report. Having a totals section, followed by a more detailed breakdown is not supported. Such a requirement would need to be handled by separate reports in Access.

As for the original question, that of producing total information pertaining to the various possible status values, that can be achieved by an aggregated (GROUP BY) query which shows the Sum() of the [SNL_Total] field :

SELECT   [SNL_Status]
       , Sum([SNL_Total]) As TotalSerNos
FROM     [tbl_SerialNumberLog]
GROUP BY [SNL_Status]

It's so much easier to work on information that includes the names of the items we need to work with. Well done for including all that in your first post.

Hopefully, that gives you as much as you need to be getting on with this, as I'm away for a week or so now. That said, if further help is required then my fellow experts will be more than capable of filling in any blanks for you.

[patjones]

What NeoPa laid out, for the summary section at the top of the report is just what I had in mind. If you're using VBA, you can embed this SQL in the VBA as the recordsource for your summary report. To keep it simple, I would have the summary report by itself.

For the detailed report, you've got a lot going on and it is explained very well. We'll probably need a little back and forth here to get to a good solution, so I'll start by asking for clarification on something.

For detail record ID #20, this is SNL_Status "Sent to Warehouse" - and you then say that two blocks have not been sent to the manufacturer while three blocks have been. Yet, I thought the definition of that SNL_Status is

**All the tags ever rec'd from Headquarters (they never receive directly from the printer), which were not yet sent to the Mfr.

So how is it that there are some blocks in the warehouse SNL_Status that were indeed sent to the manufacturer?

Pat

[IronyImpaired]

I'm very excited about the replies and I love any and all help! Seeing the replies is easily the best thing about my day. That being said...

I had a high priority issue come up at work yesterday and my boss had me put this project on hold until Tuesday.

So, I'll respond to the questions raised by both NeoPa and zepphead80 next Wednesday (2011-09-21) when we've wrapped the unrelated safety problem.

Again, thanks so much and I'll see you next Wednesday morning!

Have a great weekend,

IronyImpaired.

[NeoPa]

I await your updates :-)