A coding puzzle (1 Viewer)

Alright, so here's a fun one to figure out.

I've got an inventory file in which I determine whether a group of boxes is stacked on a pallet or not. (Anything 12 boxes or more is a pallet--anything less is loose.) If it's a pallet, we count it as One Pallet. If it's not a pallet, we count the loose boxes Per Box.

Now, I've got to figure out a way to integrate this into a report. I want the report to ask for a beginning date and an ending date, and then take all of the entries within that date range and count how many single pallets there are, and how many loose boxes there are.

So for an example...

In the inventory file:


And so on.

So then in the report, if all of the above were in May,


Does this make sense? And can someone help me figure out the coding I'll need to create this report?

Thanks!

the report design is entirely up to you. And so is the data extraction. Honestly, there are too many possibilities with something like this to give a definitive answer. A lot more information would be needed...

select 'Pallets:' as UOM, count(*) from Uvuriel03sDateQuery where QTY >= 12
union
select 'Boxes:' as UOM, sum(QTY) from Uvuriel03sDateQuery where QTY < 12;

That should get you pretty close. I didn't test it but it seems right enough, if I understand your requirements correctly.

The design I can do.

I'll try to give a little more detail. Well, a lot. Hold on tight.

For each entry, there are three parts to the quantity: Cases (aka boxes), Packs, and Quantity per pack.

So for example, if we received 3 boxes of envelopes, we might receive 3 cases with 5 packs in each case, and each pack has 100 envelopes for a total of 3*5*100=1,500 envelopes.

That first number, the 3, determines whether it's a pallet or not. If we had received 13 boxes, it would be a pallet. Anything less than 12, and they are counted as loose boxes.

Now here's the tricky part. If it's a pallet, it gets assigned a pallet ID number. The problem is, sometimes if there's an oddball quantity, it gets entered on a new line, so there are 'two' pallet IDs.

For example:
We might receive 15 cases @ 5 packs @ 100 envelopes, and along with that, 1 case @ 2 packs @ 100 envelopes. The 1@2@100 would be put on a different line because of the oddball quantity, but it would still be a part of that pallet, and so both lines would have the same Pallet ID number.

Envelopes-------15 cases @ 5 packs @ 100 envelopes---------Pallet ID 100
Envelopes--------1 case @ 2 packs @ 100 envelopes----------Pallet ID 100

This is what I've figured out.

• B:B is the row that displays either "Pallet" or "Case", depending on the case count.
• R:R is the row that displays the Pallet ID, if there is one.

Pallet Total: =COUNTIF(B:B,"Pallet")
This gives me a total count of anything listed as "Pallet", including all of the duplicate pallet IDs.

Total Pallet IDs: =COUNT(R:R)
This gives me a total count of JUST the pallet lines with pallet IDs, including duplicates.

Single Pallet IDs: =SUM(IF(FREQUENCY(R:R,R:R)>0,1))
This gives me a count of how many of the Pallet IDs are unique ones,

Duplicate Pallet IDs: =D86-D87
Here's how many of the Pallet IDs are duplicates.

Total Pallets: =D85-D88
Subtract the duplicates from the total count, and this gives me a total count of unique pallets.

To put numbers to it, let's say I have a total of 26 lines that are listed as "Pallet". 4 pairs of lines (8 lines total) share a pallet ID with one other line. Total, there are only 22 actual unique pallets.

Pallet Total: 26
Total Pallet IDs: 20
Single Pallet IDs: 16
Duplicate Pallet IDs: 4
Total Pallets: 22

Now how do I put THAT in a report? I need it to get me that result, between any two given dates.

Ummm...are you using Excel or Access reporting?

I need to get an Access report to do the above, but I actually did the above in Excel. (The inventory is in Excel and is linked into Access.) I was trying to figure out what formulas I would need to get the above results, and I figured out how to get the pallet count in Excel. I can't do a date-based query in Excel, though, and I'm having problems getting an Access report to give me the same results.

Did that clear it up for you?