Counting Up With A New Twist?

[ChadDiesel]

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("*","Lab el_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

[jimfortune]

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:

qryNumberToAssi gn:
SELECT Label_Info.ID, Label_Info.Cont ract, Label_Info.Pack ed_With,
Label_Info.Box, (SELECT Count(A.ID) FROM Label_Info AS A WHERE
A.Contract = Label_Info.Cont ract And A.ID <= Label_Info.ID) - (SELECT
Count(Nz(A.Pack ed_With)) FROM Label_Info AS A WHERE A.Contract =
Label_Info.Cont ract 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.Cont ract, Label_Info.Pack ed_With,
Label_Info.Box, (SELECT Count(B.ID) FROM Label_Info AS B WHERE
B.Contract = Label_Info.Cont ract And B.Packed_With = 'W' AND B.ID <=
Label_Info.ID AND Label_Info.ID > DMax("ID", "Label_Info ", "Contract =
'" & Label_Info.Cont ract & "' And Packed_With = 'W'")) AS
WsPreviousToThi sNonW 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]

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.

On Wed, 06 Apr 2005 02:03:37 GMT, "ChadDiesel "
<sh************ ***********@yah oo.com> wrote:

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("*","La bel_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

[jimfortune]

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

Some sites related to words:
http://www.m-w.com
http://www.1911encyclopedia.org/index.htm
http://www.askoxford.com/dictionarie...t_oed/?view=uk
http://dictionary.cambridge.org/
http://www.tiscali.co.uk/reference/d...ifficultwords/
http://www.infoplease.com/dictionary.html
http://www.etymonline.com/index.php?...earchmode=none
http://www.bartleby.com/61/
http://www.wordsmyth.net/

[ChadDiesel]

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
<ji********@com pumarc.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

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:

qryNumberToAssi gn:
SELECT Label_Info.ID, Label_Info.Cont ract, Label_Info.Pack ed_With,
Label_Info.Box, (SELECT Count(A.ID) FROM Label_Info AS A WHERE
A.Contract = Label_Info.Cont ract And A.ID <= Label_Info.ID) - (SELECT
Count(Nz(A.Pack ed_With)) FROM Label_Info AS A WHERE A.Contract =
Label_Info.Cont ract 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.Cont ract, Label_Info.Pack ed_With,
Label_Info.Box, (SELECT Count(B.ID) FROM Label_Info AS B WHERE
B.Contract = Label_Info.Cont ract And B.Packed_With = 'W' AND B.ID <=
Label_Info.ID AND Label_Info.ID > DMax("ID", "Label_Info ", "Contract =
'" & Label_Info.Cont ract & "' And Packed_With = 'W'")) AS
WsPreviousToThi sNonW 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

[jimfortune]

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]

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_N umber And A.ID <= Label_Info.ID)-(SELECT
Count(Nz(A.Pack ed_With)) FROM Label_Info AS A WHERE A.HW_Number =
Label_Info.HW_N umber 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_N umber And A.ID <= Label_Info.ID)-(SELECT
Count(Nz(A.Pack ed_With)) FROM Label_Info AS A WHERE A.HW_Number =
Label_Info.HW_N umber And A.ID <= Label_Info.ID And A.Packed_With = 'W') AS
Parcel_ID, Label_Info.Orde r_Number, Label_Info.PO_L ine,
Label_Info.Cont ract_Number, Description.Des cription, Label_Info.SS_N um,
IIf([Box_Num]<10,"0","") & [Box_Num] & "/" & IIf([Total_Boxes]<10,"0","") &
[Total_Boxes] AS Box_Num_Box_Tot al, Label_Info.UM, Label_Info.Weig ht,
Label_Info.Pack ed_With, Label_Info.Load _ID, Ship_Info.Desti nation,
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_N umber
FROM ((Description INNER JOIN Label_Info ON Description.SS_ Num =
Label_Info.SS_N um) INNER JOIN Year_Values_Tab le ON Label_Info.Year =
Year_Values_Tab le.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

<ji********@com pumarc.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

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

[jimfortune]

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]

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
<ji********@com pumarc.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

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

[jimfortune]

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.