Counting Up With A New Twist?

ChadDiesel

unread,

Apr 5, 2005, 10:03:37 PM4/5/05

to

I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other employee knows anything about
Access. I've searched Google Groups, and that has been a lot of help, but
there are some questions that I just can't find the answer to. I'll try to
take it easy on the group after this question.

I have one more problem I've been trying to solve, and I hope you guys can
help me out with some suggestions. I posted this question last week, but I
have since had my boss throw me another curve ball and have had to modify
what I wanted to do.

I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to go back to 1 and start counting up again
until the next contract change.

I was given this suggestion.

DCount("*","Label_Info","[Contract]='" & [Contract] & "' And
[Label_Info]![ID] <=" & [Label_Info]![ID])+0

With Contract being the contract number, Label_Info being the table the
contract number is held, adn ID being an autonumber field.

This worked great. My new problem is I have to add another field called
Packed_With to cover parts that are packed in other boxes. The Packed_With
value is going to be C or W (Contains or With). Most of my records will not
contain either C or W, but if I pick C, then the next record will be W. I
want the W record to have the same value as the previous. I might have yet
another W value next if there is more than 1 part. So, I want all the W's
to have the same number value as the C, and I want to start counting where I
left off after the W. I know I am not explaining myself well, so here is
another example:

For example

Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number Value: 3
Contract 1111111 C Box 1 of 1 Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 W Number Value: 4
Contract 1111111 Skid 1 of 1 Number Value: 5
Contract 1111111 Box 1 of 1 Number Value: 6

Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5

Is there a way to modify the DCount method to do what I want? Other
suggestions are welcome also.

I would really appreciate the help. I'm trying to learn as I go.

Chad

jimfo...@compumarc.com

unread,

Apr 6, 2005, 3:38:37 AM4/6/05

to

ChadDiesel wrote:
> I would really appreciate the help. I'm trying to learn as I go.

Aren't we all.

This seemed to work for several of the cases I tried:

qryNumberToAssign:
SELECT Label_Info.ID, Label_Info.Contract, Label_Info.Packed_With,
Label_Info.Box, (SELECT Count(A.ID) FROM Label_Info AS A WHERE
A.Contract = Label_Info.Contract And A.ID <= Label_Info.ID) - (SELECT
Count(Nz(A.Packed_With)) FROM Label_Info AS A WHERE A.Contract =
Label_Info.Contract And A.ID <= Label_Info.ID And A.Packed_With = 'W')
AS NumberToAssign FROM Label_Info;

Perhaps you can convert it into two separate DCount's. Note that this
method requires that the first Contract item not have a 'W' in
Packed_With so be ready for your boss to ask for that :-). If the W's
are contiguous and you really want to get fancy try something like:

SELECT Label_Info.ID, Label_Info.Contract, Label_Info.Packed_With,
Label_Info.Box, (SELECT Count(B.ID) FROM Label_Info AS B WHERE
B.Contract = Label_Info.Contract And B.Packed_With = 'W' AND B.ID <=
Label_Info.ID AND Label_Info.ID > DMax("ID", "Label_Info", "Contract =
'" & Label_Info.Contract & "' And Packed_With = 'W'")) AS
WsPreviousToThisNonW FROM Label_Info;

I.e., the DMax function can be used inside a DCount function's Criteria
string to help get functionality that only your boss can imagine.

James A. Fortune

Steve Jorgensen

unread,

Apr 6, 2005, 4:11:23 AM4/6/05

to

One thing that might change your thinking about the whole design...

The whole idea of using the order of records in the table to describe
containership relations is really bad relational design. Yes, you might want
to -present- the data that way, but not describe it that way.

Instead, what I would recommend is that you have a table of shipping items,
and a table of shipping sub-items. Each sub-item has a foreign key
relationship to an item. The hierarchy looks like this:

Shipment:
Item:
Sub-Item

Now, only item records have numbers, not sub-item records, so you no longer
need to hassle with how to increment the numbering scheme for the "item"
cases, and not for the "sub-item" cases.

jimfo...@compumarc.com

unread,

Apr 6, 2005, 12:22:22 PM4/6/05

to

Steve Jorgensen wrote:
> One thing that might change your thinking about the whole design...
>
> The whole idea of using the order of records in the table to describe
> containership relations is really bad relational design. Yes, you
might want
> to -present- the data that way, but not describe it that way.
>
> Instead, what I would recommend is that you have a table of shipping
items,
> and a table of shipping sub-items. Each sub-item has a foreign key
> relationship to an item. The hierarchy looks like this:
>
> Shipment:
> Item:
> Sub-Item
>
> Now, only item records have numbers, not sub-item records, so you no
longer
> need to hassle with how to increment the numbering scheme for the
"item"
> cases, and not for the "sub-item" cases.

That's a great idea. This little SQL puzzle was fun but it wasn't
necessary.

James A. Fortune

ChadDiesel

unread,

Apr 6, 2005, 7:18:42 PM4/6/05

to

That worked great, but I have a new problem. It works fine for my labels,
but the query also feeds a "Load Sheet" report that is grouped by an Order
Number. I get the following error message when I try to load the report:

"Multi-level Group By Clause is not allowed in a subquery."

Is there a way to modify this so I don't get this error?

Thanks for the help.

Chad

<jimfo...@compumarc.com> wrote in message
news:1112773117.1...@z14g2000cwz.googlegroups.com...

jimfo...@compumarc.com

unread,

Apr 7, 2005, 1:00:55 PM4/7/05

to

ChadDiesel wrote:
> That worked great, but I have a new problem. It works fine for my
labels,
> but the query also feeds a "Load Sheet" report that is grouped by an
Order
> Number. I get the following error message when I try to load the
report:
>
> "Multi-level Group By Clause is not allowed in a subquery."
>
> Is there a way to modify this so I don't get this error?
>
> Thanks for the help.
>
> Chad

Whatever happened to "I'll try to take it easy on the group after this
question." :-)?

Would you show me a sample of how OrderNumber looks in the table and
the SQL you are trying to use as the RecordSource of the report? You
were doing so well at providing examples up til now.

James A. Fortune

New Amsterdam - town founded 1625 on Manhattan Island by the Dutch;
renamed New York 1664 by the British -- www.m-w.com

ChadDiesel

unread,

Apr 8, 2005, 8:35:06 AM4/8/05

to

Sorry, I meant after I figured out the answer to this question :-) There
might be sub-questions about this question.

First off, I was counting by the order number and grouping on the report by
contract number. I said earlier that I was counting by the contract number,
but I misunderstood what the boss was saying.

This is the code I used for the counting-the one you gave me.

Parcel_ID: (SELECT Count(A.ID) FROM Label_Info AS A WHERE A.HW_Number =
Label_Info.HW_Number And A.ID <= Label_Info.ID)-(SELECT
Count(Nz(A.Packed_With)) FROM Label_Info AS A WHERE A.HW_Number =
Label_Info.HW_Number And A.ID <= Label_Info.ID And A.Packed_With = 'W')

HW number is the order number.
Packed with is C or W
Label_Info is the table.

Works perfect for the query, but gives me the

"Multi-level Group By Clause is not allowed in a subquery."

error in the report.

I put this in a query, along with Description, Type of Box, Weight, etc...
and it feeds a report called Load_Sheet

I have the report grouped by the company's contract number, so I have a
group header and footer. I get the multi-level group by clause when I try
to run the report. I tried making a new report without grouping, and the
report runs fine. As soon as I add grouping, I have the problem.

I selected the query as my form source and dragged and dropped the fields
into the report. The only other thing I did was add =Sum([Weight]) and a
couple other =Sum() for the different box totals in the group footer to show
a total weight of the load.

I've searched Google Groups for an answer and several people have wirtten
that reports have problems with subqueries. Some people suggested using the
First() around the subquery, but that is either not working in my situation,
or I am not using it in the right place. Others suggested trying the
DCount, but I wasn't sure how to set it up 2 DCounts. These people were
receiving the same message, but may not have the same situtation as me.

As for the SQL of the query (take it easy, I'm new :-) ):

SELECT (SELECT Count(A.ID) FROM Label_Info AS A WHERE A.HW_Number =
Label_Info.HW_Number And A.ID <= Label_Info.ID)-(SELECT
Count(Nz(A.Packed_With)) FROM Label_Info AS A WHERE A.HW_Number =
Label_Info.HW_Number And A.ID <= Label_Info.ID And A.Packed_With = 'W') AS
Parcel_ID, Label_Info.Order_Number, Label_Info.PO_Line,
Label_Info.Contract_Number, Description.Description, Label_Info.SS_Num,
IIf([Box_Num]<10,"0","") & [Box_Num] & "/" & IIf([Total_Boxes]<10,"0","") &
[Total_Boxes] AS Box_Num_Box_Total, Label_Info.UM, Label_Info.Weight,
Label_Info.Packed_With, Label_Info.Load_ID, Ship_Info.Destination,
Ship_Info.Ship_Date, "684451-" & IIf((Month([Ship_Date]))<10,"0","") &
Month([Ship_Date]) & IIf((Day([Ship_Date]))<10,"0","") & Day([Ship_Date]) &
Right((Year([Ship_Date])),2) & [BOL] AS ASN, Ship_Info.BOL,
Label_Info.HW_Number
FROM ((Description INNER JOIN Label_Info ON Description.SS_Num =
Label_Info.SS_Num) INNER JOIN Year_Values_Table ON Label_Info.Year =
Year_Values_Table.Year) INNER JOIN Ship_Info ON Label_Info.Load_ID =
Ship_Info.Load_ID;

If I take out the Parcel_ID field, the query and report work fine.

Again, counting by Order Number in query, and grouping by contract number.
Sorry for the confusion earlier. I hope this extra information helps.

Thanks,

Chad

<jimfo...@compumarc.com> wrote in message
news:1112893255.6...@z14g2000cwz.googlegroups.com...

jimfo...@compumarc.com

unread,

Apr 10, 2005, 1:15:45 AM4/10/05

to

ChadDiesel wrote:
> Sorry for the confusion earlier. I hope this extra information
helps.
>
> Thanks,
>
> Chad

I looked up that thread using the text of the error message. One of
the posters mentioned that changing the query into a Make Table Query
and using the resulting table as the RecordSource for the report
worked. Post back if that solution is unworkable.

James A. Fortune

ChadDiesel

unread,

Apr 10, 2005, 4:31:17 PM4/10/05

to

I've never used a make-table query before. The database records will
continue to grow as we put in more and more shipments. Will the size of the
database keep growing, or can you save over existing tables? I'd like to
modify the query or use Dcount. The code you gave me works great except for
the grouping on the report. I realize that defining part of the Parcel_ID
by counting up until the order number changes is probably not a proper way
to do things, but this is the system in place with the company we deal with,
and we have to do things that way. Thanks for the help. You've gone well
beyond the call of duty. Anytime you get tired of me asking questions, let
me know :-)

Chad

<jimfo...@compumarc.com> wrote in message
news:1113110145.5...@f14g2000cwb.googlegroups.com...

jimfo...@compumarc.com

unread,

Apr 10, 2005, 5:46:23 PM4/10/05

to

ChadDiesel wrote:
> I've never used a make-table query before. The database records will

Go into query design mode and change your select query into a
make-table query. Access will ask you for the name of the new table.

> continue to grow as we put in more and more shipments. Will the size
of the
> database keep growing, or can you save over existing tables? I'd
like to

You can save over existing tables.

> modify the query or use Dcount. The code you gave me works great
except for

Let's wait until your make-table grows too long. I realize the
make-table route is not very elegant but it's enough for now. Perhaps
you or someone else will figure a better way to do it before then.

> the grouping on the report. I realize that defining part of the
Parcel_ID
> by counting up until the order number changes is probably not a
proper way
> to do things, but this is the system in place with the company we
deal with,
> and we have to do things that way. Thanks for the help. You've gone
well
> beyond the call of duty. Anytime you get tired of me asking
questions, let
> me know :-)

Your question helped me a lot. I was totally unaware of the report
grouping problem. Perhaps the renaming of the subquery results using
AS (as suggested by MGFoster?) would get around it.

>
> Chad

James A. Fortune

on the wire - billiards handicapping term that refers to giving a
preset number of wins in advance to the other player. A wire with
sliding wooden or plastic markers used to be used to keep score in
straight pool (a.k.a. 14.1 Continuous) during its heyday.

ChadDiesel

unread,

Apr 10, 2005, 9:09:25 PM4/10/05

to

Thanks James. I will give that a shot tomorrow and let you know how things
turn out.

Chad

<jimfo...@compumarc.com> wrote in message
news:1113169583.5...@z14g2000cwz.googlegroups.com...

ChadDiesel

unread,

Apr 12, 2005, 6:37:01 PM4/12/05

to

The make table worked great. Thanks for the info. The program as a whole
seems to be working well. It always asks me if I'm sure I want to save over
the table when I hit the print button on my form which runs the query and
prints the records. I'm sure there is a way to stop that message, but I'll
look that up later. Thanks for all the help. You saved me.

Chad

<jimfo...@compumarc.com> wrote in message
news:1113169583.5...@z14g2000cwz.googlegroups.com...

ChadDiesel

unread,

Apr 15, 2005, 8:11:05 AM4/15/05

to

I fixed the warning with the Set.Warnings code. Thanks for all the help.

Chad

"ChadDiesel" <shaqattack19...@yahoo.com> wrote in message
news:h4Y6e.8696$44....@newsread1.news.atl.earthlink.net...

ChadDiesel

unread,

Apr 19, 2005, 8:34:30 AM4/19/05

to

Hello Again,

Yeah-it's me again. This has been working great for our shipping labels.
My boss would like to add the feature to this database that would allow for
the the values with W to be on a different HW_Number. So, it's funny you
said earlier that my boss would ask for the the first value of the HW_Number
be W :-). Is there any way to do what we have been doing, but if the record
is coded with an C, the following W will have the same Parcel ID regardless
of HW_Number?

Example:

Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number Value: 3
Contract 1111111 C Box 1 of 1 Number Value: 4

Contract 1123456 W Number Value: 4

I don't know if anyone will still check this thread, but I didn't want to
post a new topic after all the posts I've made here.

Thanks,

Chad

<jimfo...@compumarc.com> wrote in message
news:1113169583.5...@z14g2000cwz.googlegroups.com...

jimfo...@compumarc.com

unread,

Apr 20, 2005, 1:04:05 AM4/20/05

to

To quote the commercial, "You're not messing with me, are you?" Funny
pronounced backwards is enough! Please stand by whilst I apply some
cogitational Botox to this latest wrinkle.

James A. Fortune

jimfo...@compumarc.com

unread,

Apr 20, 2005, 4:27:53 AM4/20/05

to

Here's what I have so far:

qryNumberToAssign:
SELECT Label_Info.ID, Label_Info.Contract, Label_Info.Packed_With,

(SELECT A.Packed_With FROM Label_Info AS A WHERE A.ID = Label_Info.ID
-1) AS PrevLetter, Label_Info.Box, (SELECT Count(A.ID) FROM Label_Info
AS A WHERE A.ID <= Label_Info.ID AND A.Packed_With = 'C') AS
RunningCCount, (SELECT Count(A.ID) FROM Label_Info AS A WHERE A.ID <=
Label_Info.ID AND A.Packed_With = 'W') AS RunningWCount, (SELECT
Count(A.ID) FROM Label_Info AS A WHERE A.Contract =
Label_Info.Contract And A.ID <= Label_Info.ID) AS
ItemCountThisContract, (SELECT Count(Nz(A.Packed_With)) FROM
Label_Info AS A WHERE A.Contract = Label_Info.Contract And A.ID <=
Label_Info.ID And A.Packed_With = 'W') AS RunningWCountThisContract,
(SELECT A.Contract FROM Label_Info AS A WHERE A.ID = Label_Info.ID -1)
AS PrevContract, Abs([PrevLetter]='C' And Label_Info.Packed_With='W'
And [PrevContract]<>Label_Info.Contract) AS
NewContractWAfterCCondition, Abs(Label_Info.Packed_With='W' And
[PrevContract]<>Label_Info.Contract) AS NewContractWCondition,
Abs([NewContractWAfterCCondition])*6 AS Adjustment,
[ItemCountThisContract]-[RunningWCountThisContract]+[Adjustment] AS
NumberToAssign FROM Label_Info;

The 6 should actually be the NumberToAssign from the previous record
but I haven't figured out how to get that yet using a single query.

This mostly worked on the previous example but when I tried a 'C' on
the last item followed by a 'W' on the first item, here's what
happened:

Label_Info
1 Contract 1111111 Box 1 of 2
2 Contract 1111111 Box 2 of 2
3 Contract 1111111 Skid 1 of 1
4 Contract 1111111 C Box 1 of 1
5 Contract 1111111 W
6 Contract 1111111 W
7 Contract 1111111 W
8 Contract 1111111 C Skid 1 of 1
9 Contract 1111111 C Box 1 of 1
10 Contract 2222222 W Box 1 of 2
11 Contract 2222222 W Box 2 of 2
12 Contract 2222222 Skid 1 of 1
13 Contract 2222222 Bundle 1 of 2
14 Contract 2222222 Bundle 2 of 2

produced:

ID
Contract
Packed_With
PrevLetter
Box
RunningCCount
RunningWCount
ItemCountThisContract
RunningWCountThisContract
PrevContract
NewContractWAfterCCondition
NewContractWCondition
Adjustment
NumberToAssign

1 Contract 1111111 Null Null Box 1 of 2 0 0 1 0 Null Null Null
Null
2 Contract 1111111 Null Null Box 2 of 2 0 0 2 0 Contract 1111111
0 0 0 2
3 Contract 1111111 Null Null Skid 1 of 1 0 0 3 0 Contract 1111111
0 0 0 3
4 Contract 1111111 C Null Null 1 0 4 0 Contract 1111111
0 0 0 4
5 Contract 1111111 W C Null 1 1 5 1 Contract 1111111
0 0 0 4
6 Contract 1111111 W W Null 1 2 6 2 Contract 1111111
0 0 0 4
7 Contract 1111111 W W Null 1 3 7 3 Contract 1111111
0 0 0 4
8 Contract 1111111 C W Skid 1 of 1 2 3 8 3 Contract 1111111
0 0 0 5
9 Contract 1111111 C C Box 1 of 1 3 3 9 3 Contract 1111111
0 0 0 6
10 Contract 2222222 W C Box 1 of 2 3 4 1 1 Contract 1111111
1 1 6 6
11 Contract 2222222 W W Box 2 of 2 3 5 2 2 Contract 2222222
0 0 0 0
12 Contract 2222222 Null W Skid 1 of 1 3 5 3 2 Contract 2222222
0 0 0 1
13 Contract 2222222 Null Null Bundle 1 of 2 3 5 4 2 Contract 2222222
0 0 0 2
14 Contract 2222222 Null Null Bundle 2 of 2 3 5 5 2 Contract 2222222
0 0 0 3

Even with the 6 filled in manually the second W caused a problem.
Using the brackets to duplicate previously calculated values like I saw
someone in this NG do recently is adding another dimension to my
queries but this problem is still pretty tricky. Also, if a 'W'
continues after a 'C' from a previous HW_Number and is followed by
another 'W' as shown here, should that item also have the same number
assigned? To summarize, using the order of the records to compare
values from the previous record(s) will often lead to nasty situations
like this. Take Steve's advice and find a way to organize the tables
so that simple queries can be used to calculate your shipping labels.
The bracketed expressions in the queries allow for calculations that
are quite powerful but that power can be put to use better on tables
that are organized to avoid dependencies on the order in which the
records are stored.

James A. Fortune

ChadDiesel

unread,

Apr 20, 2005, 8:19:40 AM4/20/05

to

I swear I'm not messing with you James :-). This final part of the project
has turned into a cruel joke, but the joke is on me. I'm going to talk to
my boss again about the way we assign numbers, but it falls into the "this
is the way we've always done thing". Before, this database, they did the
load list with one Excel spreadsheet, the labels with another.--Then, they
printed out the results, and then one of the office workers entered the data
again in another computer. The "C" and "W" does not happen every time, but
it does happen. The database is running great except for this last part. I
can go in and manually change the output that is transfered to the order
computer to get by temporarily. I know we are probably going to run into
problems with this numbering system-especially when someone goes in and
changes or adds something.

Chad

<jimfo...@compumarc.com> wrote in message
news:1113985673....@l41g2000cwc.googlegroups.com...

shaqattack19...@yahoo.com

unread,

Apr 20, 2005, 11:03:00 AM4/20/05

to

Also, to answer your question, any "W" that follows a "C" should be the
same parcel ID number. There might be a "C" followed by 2 or 3 "W"s
with different HW_numbers.

Chad

ChadDiesel

unread,

Apr 24, 2005, 9:59:55 PM4/24/05

to

Hello,

Could you elaborate a little on rearranging tables? We have already stared
to use the system, and have data in the tables already. I'd like to modify
the current database if possible. Maybe it isn't. The "Packed With"
(sub-items) need to have the same parcel ID as the "Contains" main items.
How will I do this with a rearranged table structure? For reference, here
is a screen capture of the form I am using:

http://www.chadworld.com/images/shippingform.jpg

Any information would be appreciated.

Thanks You,

Chad

<jimfo...@compumarc.com> wrote in message
news:1113985673....@l41g2000cwc.googlegroups.com...