formatting even numbers on report

[LDA via AccessMonster.com]

Hello
I have a report with the field samplenumber which has been barcoded to allow
for easy data transfer.

Having the samplenubers in column form isn't practical, due to the mistake of
recording wrong barcode.

I would like to align the text sample number to the left side of the report
if odd and to the right side of the report if even.

I have attempted to have a control and use the following:

IIf(['*' & [SampleNumber] & '*", Mod 2=0, Then <--After this point i am lost

I have also tried in the formating event under the group to have:
IIf(['*'&[samplenumber]&'*'], mod2 = 0, True) then
align = Left
Else
IIf(['*'&[samplenumber]&'*'], mod2 = 0, True) = align = Right
End If
end sub,

But that too isn't working..

any suggestions are grateful

LDA

 

[Duane Hookom]

You could add code to the On Format event of the section of the report
containing the text box like:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtSampleNumber Mod 2 = 1 Then
Me.txtSampleNumber.Left = 0
Else
Me.txtSampleNumber.Left = Me.Width - Me.txtSampleNumber.Width
End If
End Sub

 

[lda via AccessMonster.com]

I believe the code will work fine.. but the field is a text field.. is there
are way to input a conversion in the code you rendered below from text to
number???

lda

You could add code to the On Format event of the section of the report
containing the text box like:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtSampleNumber Mod 2 = 1 Then
Me.txtSampleNumber.Left = 0
Else
Me.txtSampleNumber.Left = Me.Width - Me.txtSampleNumber.Width
End If
End Sub

Hello
I have a report with the field samplenumber which has been barcoded to allow

[quoted text clipped - 23 lines]

LDA

 

[Duane Hookom]

Actually, I would add an invisible text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Then use this text box to move your txtSampleNumber regardless of its value.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtCount Mod 2 = 1 Then
Me.txtSampleNumber.Left = 0
Else
Me.txtSampleNumber.Left = Me.Width - Me.txtSampleNumber.Width
End If
End Sub

--
Duane Hookom
Microsoft Access MVP

I believe the code will work fine.. but the field is a text field.. is there
are way to input a conversion in the code you rendered below from text to
number???

lda

You could add code to the On Format event of the section of the report
containing the text box like:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtSampleNumber Mod 2 = 1 Then
Me.txtSampleNumber.Left = 0
Else
Me.txtSampleNumber.Left = Me.Width - Me.txtSampleNumber.Width
End If
End Sub

Hello
I have a report with the field samplenumber which has been barcoded to allow

[quoted text clipped - 23 lines]

LDA

 

[LDA via AccessMonster.com]

Duane,
I think I am doing something wrong... and I don't understand exactly what you
mean by: "use this text box to move your txtSampleNumber "
I did as you said and entered the following: (text142, is the name of control)


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtCount Mod 2 = 1 Then
Me.Text142.Left = 0
Else
Me.Text142.Left = Me.Width - Me.Text142.Width
End If
End Sub

when I ran the report I am prompted to Enter the Parameter Value????

when I click off my barcodes (odd and even) are now all on the right side of
the report.

Did I do something wrong??

Thanks

LDA

Actually, I would add an invisible text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Then use this text box to move your txtSampleNumber regardless of its value.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtCount Mod 2 = 1 Then
Me.txtSampleNumber.Left = 0
Else
Me.txtSampleNumber.Left = Me.Width - Me.txtSampleNumber.Width
End If
End Sub

I believe the code will work fine.. but the field is a text field.. is there
are way to input a conversion in the code you rendered below from text to

[quoted text clipped - 17 lines]

 

[LDA via AccessMonster.com]

Hello,
Its working!! the only issue is that is seems to be effecting my page
settings....
any advice

Thanks for it all

Duane,
I think I am doing something wrong... and I don't understand exactly what you
mean by: "use this text box to move your txtSampleNumber "
I did as you said and entered the following: (text142, is the name of control)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtCount Mod 2 = 1 Then
Me.Text142.Left = 0
Else
Me.Text142.Left = Me.Width - Me.Text142.Width
End If
End Sub

when I ran the report I am prompted to Enter the Parameter Value????

when I click off my barcodes (odd and even) are now all on the right side of
the report.

Did I do something wrong??

Thanks

LDA

Actually, I would add an invisible text box to the detail section:
Name: txtCount

[quoted text clipped - 16 lines]

 

[KARL DEWEY]

What about a simple way --
ShiftedField: IIF([txtSampleNumber] Mod 2 = 1, [txtSampleNumber],
Space(66) & [txtSampleNumber])

Hello,
Its working!! the only issue is that is seems to be effecting my page
settings....
any advice

Thanks for it all

Duane,
I think I am doing something wrong... and I don't understand exactly what you
mean by: "use this text box to move your txtSampleNumber "
I did as you said and entered the following: (text142, is the name of control)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtCount Mod 2 = 1 Then
Me.Text142.Left = 0
Else
Me.Text142.Left = Me.Width - Me.Text142.Width
End If
End Sub

when I ran the report I am prompted to Enter the Parameter Value????

when I click off my barcodes (odd and even) are now all on the right side of
the report.

Did I do something wrong??

Thanks

LDA

Actually, I would add an invisible text box to the detail section:
Name: txtCount

[quoted text clipped - 16 lines]

LDA

 

[LDA via AccessMonster.com]

Hello,
Thanks for response. was able to get barcode to alternate accordingly with in
the margins.

I have noticed since doing that a problem i was trying to avoid is still
happening.

samplenumbers that are odd and follow each other are still making it hard to
scan the barcode.

is it possible to forget all the even odd and just have for example the first
samplenumber on the left margin, then the next on the right and so forth?

Thanks

LDA

What about a simple way --
ShiftedField: IIF([txtSampleNumber] Mod 2 = 1, [txtSampleNumber],
Space(66) & [txtSampleNumber])

Hello,
Its working!! the only issue is that is seems to be effecting my page

[quoted text clipped - 33 lines]

 

[KARL DEWEY]

You can use Ranking in a Group query and use the Mod 2 on the Rank.
Here is an example --
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

If you have a problem applying it then post your report query name and
fields so I can put it into the Rank query to feed the report.
Then in the report you would use the Rank Mod on the [txtSampleNumber] like
this --
ShiftedField: IIF([Rank] Mod 2 = 1, [txtSampleNumber], Space(66) &
[txtSampleNumber])

Hello,
Thanks for response. was able to get barcode to alternate accordingly with in
the margins.

I have noticed since doing that a problem i was trying to avoid is still
happening.

samplenumbers that are odd and follow each other are still making it hard to
scan the barcode.

is it possible to forget all the even odd and just have for example the first
samplenumber on the left margin, then the next on the right and so forth?

Thanks

LDA

What about a simple way --
ShiftedField: IIF([txtSampleNumber] Mod 2 = 1, [txtSampleNumber],
Space(66) & [txtSampleNumber])

Hello,
Its working!! the only issue is that is seems to be effecting my page

[quoted text clipped - 33 lines]

LDA

 

[LDA via AccessMonster.com]

thanks for the help, but i am lost.

the report name is duke_icpmass_byjob and the query is: De_Backlog_BK0073

Fields:

samplenumber, customerid, matrix, site, duedate, customersamplenumber,
shippingmethod, barcode

the sql is below:

SELECT SampleDetails.SampleNumber, Orders.CustomerID, SampleDetails.Matrix,
OrderDetails.Site, SampleDetails.DueDate, OrderDetails.CustomerSampleNumber,
Orders.ShippingMethod, ([sampledetails].[DueDate]-Date()-2) AS Daystodo,
QCBatch_RunSequence.QCBatchID, SampleDetails.OrderID, SampleDetails.Test,
("*" & [sampledetails].[samplenumber] & "*") AS Barcode
FROM QCBatch_RunSequence RIGHT JOIN ((SampleDetails LEFT JOIN Orders ON
SampleDetails.OrderID = Orders.OrderID) LEFT JOIN OrderDetails ON
(SampleDetails.OrderID = OrderDetails.OrderID) AND (SampleDetails.
SampleNumber = OrderDetails.SampleNumber)) ON QCBatch_RunSequence.
SampleNumber = SampleDetails.SampleNumber
WHERE (((SampleDetails.Test) Like "ims*")) OR (((("*" & [sampledetails].
[samplenumber] & "*"))=CStr("barcode")));

end sql

I was looking at older post.. could a rowcount also help???

thanks again,

LDA

You can use Ranking in a Group query and use the Mod 2 on the Rank.
Here is an example --
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

If you have a problem applying it then post your report query name and
fields so I can put it into the Rank query to feed the report.
Then in the report you would use the Rank Mod on the [txtSampleNumber] like
this --
ShiftedField: IIF([Rank] Mod 2 = 1, [txtSampleNumber], Space(66) &
[txtSampleNumber])

Hello,
Thanks for response. was able to get barcode to alternate accordingly with in

[quoted text clipped - 21 lines]

 

[KARL DEWEY]

This will number each record in a field named Rank. It may not be in the
order you wish but you can re-arrange the ORDER BY as you want it to be.
SELECT Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode < Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.barcode)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.barcode;

In your report Grouping and Sorting select the field Rank.

To display the SampleNumber put this in the report text box --
IIF([Rank] Mod 2 = 1, [SampleNumber], Space(66) & [SampleNumber])

thanks for the help, but i am lost.

the report name is duke_icpmass_byjob and the query is: De_Backlog_BK0073

Fields:

samplenumber, customerid, matrix, site, duedate, customersamplenumber,
shippingmethod, barcode

the sql is below:

SELECT SampleDetails.SampleNumber, Orders.CustomerID, SampleDetails.Matrix,
OrderDetails.Site, SampleDetails.DueDate, OrderDetails.CustomerSampleNumber,
Orders.ShippingMethod, ([sampledetails].[DueDate]-Date()-2) AS Daystodo,
QCBatch_RunSequence.QCBatchID, SampleDetails.OrderID, SampleDetails.Test,
("*" & [sampledetails].[samplenumber] & "*") AS Barcode
FROM QCBatch_RunSequence RIGHT JOIN ((SampleDetails LEFT JOIN Orders ON
SampleDetails.OrderID = Orders.OrderID) LEFT JOIN OrderDetails ON
(SampleDetails.OrderID = OrderDetails.OrderID) AND (SampleDetails.
SampleNumber = OrderDetails.SampleNumber)) ON QCBatch_RunSequence.
SampleNumber = SampleDetails.SampleNumber
WHERE (((SampleDetails.Test) Like "ims*")) OR (((("*" & [sampledetails].
[samplenumber] & "*"))=CStr("barcode")));

end sql

I was looking at older post.. could a rowcount also help???

thanks again,

LDA

You can use Ranking in a Group query and use the Mod 2 on the Rank.
Here is an example --
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

If you have a problem applying it then post your report query name and
fields so I can put it into the Rank query to feed the report.
Then in the report you would use the Rank Mod on the [txtSampleNumber] like
this --
ShiftedField: IIF([Rank] Mod 2 = 1, [txtSampleNumber], Space(66) &
[txtSampleNumber])

Hello,
Thanks for response. was able to get barcode to alternate accordingly with in

[quoted text clipped - 21 lines]

LDA

 

[misschanda via AccessMonster.com]

Hey karl,
I inserted everything like you said and got the following error:

"Multi-level group by clause is not allowed in subquery."

the report is grouped by order id and then by sample number.

Here is the sql:

SELECT Q.samplenumber, Q.orderid, Q.QCBatchID, Q.DaysToDo, Q.customerid, Q.
matrix, Q.site, Q.duedate, Q.customersamplenumber, Q.shippingmethod, Q.
barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate& Q1.
orderid& Q1.QCBatchID& Q1.DaysToDo&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode < Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.barcode& Q.orderid& Q.QCBatchID& Q1.DaysToDo)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.orderid, Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo,
Q.barcode;

whats going wrong?

This will number each record in a field named Rank. It may not be in the
order you wish but you can re-arrange the ORDER BY as you want it to be.
SELECT Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode < Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.barcode)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.barcode;

In your report Grouping and Sorting select the field Rank.

To display the SampleNumber put this in the report text box --
IIF([Rank] Mod 2 = 1, [SampleNumber], Space(66) & [SampleNumber])

thanks for the help, but i am lost.

[quoted text clipped - 48 lines]

 

[LDA via AccessMonster.com]

karl.
I found some other post which advised making a union query. the result is
below.
SELECT Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo,
Q.barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode &Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo< Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.orderid& Q.QCBatchID& Q.DaysToDo& Q.barcode)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.barcode, Q.orderid, Q.QCBatchID,
Q.DaysToDo



UNION ALL Select Null, null, null, null, null, null, null, null, null, null,
null, null
From [DateFormulas]
Where DateID=99999999999999999999999999999999999999999999999999999;

End Query

Once I made the union query, I inserted into the textbox the following: =IIf(
[Rank] Mod 2=1,[barcode],Space(66) & [barcode])

My only problem now is that Ranks that are not equal to Mod 2=1 are not
showing up... As I pictured those that are not equal would appear on the
right side of the report and those were Rank mod2 =1 would be on the left
margin..

Also, it is running really slow... can the speed be increased???

Thanks,

LDA

Hey karl,
I inserted everything like you said and got the following error:

"Multi-level group by clause is not allowed in subquery."

the report is grouped by order id and then by sample number.

Here is the sql:

SELECT Q.samplenumber, Q.orderid, Q.QCBatchID, Q.DaysToDo, Q.customerid, Q.
matrix, Q.site, Q.duedate, Q.customersamplenumber, Q.shippingmethod, Q.
barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate& Q1.
orderid& Q1.QCBatchID& Q1.DaysToDo&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode < Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.barcode& Q.orderid& Q.QCBatchID& Q1.DaysToDo)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.orderid, Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo,
Q.barcode;

whats going wrong?

This will number each record in a field named Rank. It may not be in the
order you wish but you can re-arrange the ORDER BY as you want it to be.

[quoted text clipped - 19 lines]

 

[KARL DEWEY]

I do not know purpose your union query serves.

You have an error in WHERE statement --
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
to be
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q1.duedate&

You have to have all Q1's on one side and Q's on the other of the Less Than
sign.

Also they must be in the same order. You have --
Q1.barcode &Q1.orderid& Q1.QCBatchId& Q1.DaysToDo <
and
Q.orderid& Q.QCBatchID& Q.DaysToDo& Q.barcode

karl.
I found some other post which advised making a union query. the result is
below.
SELECT Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo,
Q.barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode &Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo< Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.orderid& Q.QCBatchID& Q.DaysToDo& Q.barcode)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.barcode, Q.orderid, Q.QCBatchID,
Q.DaysToDo



UNION ALL Select Null, null, null, null, null, null, null, null, null, null,
null, null
From [DateFormulas]
Where DateID=99999999999999999999999999999999999999999999999999999;

End Query

Once I made the union query, I inserted into the textbox the following: =IIf(
[Rank] Mod 2=1,[barcode],Space(66) & [barcode])

My only problem now is that Ranks that are not equal to Mod 2=1 are not
showing up... As I pictured those that are not equal would appear on the
right side of the report and those were Rank mod2 =1 would be on the left
margin..

Also, it is running really slow... can the speed be increased???

Thanks,

LDA

Hey karl,
I inserted everything like you said and got the following error:

"Multi-level group by clause is not allowed in subquery."

the report is grouped by order id and then by sample number.

Here is the sql:

SELECT Q.samplenumber, Q.orderid, Q.QCBatchID, Q.DaysToDo, Q.customerid, Q.
matrix, Q.site, Q.duedate, Q.customersamplenumber, Q.shippingmethod, Q.
barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate& Q1.
orderid& Q1.QCBatchID& Q1.DaysToDo&
Q1.customersamplenumber& Q1.shippingmethod& Q1.barcode < Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.barcode& Q.orderid& Q.QCBatchID& Q1.DaysToDo)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.orderid, Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate,
Q.customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo,
Q.barcode;

whats going wrong?

This will number each record in a field named Rank. It may not be in the
order you wish but you can re-arrange the ORDER BY as you want it to be.

[quoted text clipped - 19 lines]

LDA

 

[LDA via AccessMonster.com]

Hello,
The union query idea came from

http://www.accessmonster.com/Uwe/Fo...el-GROUP-By-Clause-is-not-allowed-in-subquery


It eventually began to work, but real slow and data was missing...

So i scrapped that ideal and took your adviced and re-ordered teh Where
Statement. I am not getting the following message

"invalid use of '.', '!', or '()' in query experession Q1.samplenumber& Q1.
customerid& Q1.matrix& Q1.site& Q1.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo& Q1.barcode <Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shi'.

The re-ordered sql is bleow. and once again, I am not certain of what I am
doing incorrectly...

Do you have any more suggestions.

Thanks,

LDA

I do not know purpose your union query serves.

You have an error in WHERE statement --
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
to be
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q1.duedate&

You have to have all Q1's on one side and Q's on the other of the Less Than
sign.

Also they must be in the same order. You have --
Q1.barcode &Q1.orderid& Q1.QCBatchId& Q1.DaysToDo <
and
Q.orderid& Q.QCBatchID& Q.DaysToDo& Q.barcode

karl.
I found some other post which advised making a union query. the result is

[quoted text clipped - 64 lines]

 

[lda via AccessMonster.com]

Hello,
The union query idea came from

http://www.accessmonster.com/Uwe/Forum.aspx/access-reports/34625/Multi-Level-GRO

UP-By-Clause-is-not-allowed-in-subquery

It eventually began to work, takes a while loading and data it starts the
rank count at 2.

So i scrapped that idea and took your advice and re-ordered the Where
Statement. I am now getting the following message

"invalid use of '.', '!', or '()' in query experession Q1.samplenumber& Q1.
customerid& Q1.matrix& Q1.site& Q1.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo& Q1.barcode <Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shi'.

The re-ordered sql is bleow. and once again, I am not certain of what I am
doing incorrectly...

SELECT Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate, Q.
customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo, Q.
barcode, (SELECT COUNT(*) FROM
[De_Backlog_BK0073] Q1
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q1.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo& Q1.barcode <Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shippingmethod& Q.orderid& Q.QCBatchID& Q.DaysToDo& Q.barcode)+1 AS Rank
FROM De_Backlog_BK0073 AS Q
ORDER BY Q.samplenumber, Q.customerid, Q.matrix, Q.site, Q.duedate, Q.
customersamplenumber, Q.shippingmethod, Q.orderid, Q.QCBatchID, Q.DaysToDo, Q.
barcode;


Do you have any more suggestions.

Thanks,

LDA

Hello,
The union query idea came from

http://www.accessmonster.com/Uwe/Fo...el-GROUP-By-Clause-is-not-allowed-in-subquery

It eventually began to work, but real slow and data was missing...

So i scrapped that ideal and took your adviced and re-ordered teh Where
Statement. I am not getting the following message

"invalid use of '.', '!', or '()' in query experession Q1.samplenumber& Q1.
customerid& Q1.matrix& Q1.site& Q1.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo& Q1.barcode <Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shi'.

The re-ordered sql is bleow. and once again, I am not certain of what I am
doing incorrectly...

Do you have any more suggestions.

Thanks,

LDA

I do not know purpose your union query serves.

[quoted text clipped - 16 lines]

 

[KARL DEWEY]

I would make sure there was a space after every comma and a space before and
after each ampersand.

Hello,
The union query idea came from

http://www.accessmonster.com/Uwe/Fo...el-GROUP-By-Clause-is-not-allowed-in-subquery


It eventually began to work, but real slow and data was missing...

So i scrapped that ideal and took your adviced and re-ordered teh Where
Statement. I am not getting the following message

"invalid use of '.', '!', or '()' in query experession Q1.samplenumber& Q1.
customerid& Q1.matrix& Q1.site& Q1.duedate&
Q1.customersamplenumber& Q1.shippingmethod& Q1.orderid& Q1.
QCBatchId& Q1.DaysToDo& Q1.barcode <Q.samplenumber&
Q.customerid& Q.matrix& Q.site& Q.duedate& Q.customersamplenumber&
Q.shi'.

The re-ordered sql is bleow. and once again, I am not certain of what I am
doing incorrectly...

Do you have any more suggestions.

Thanks,

LDA

I do not know purpose your union query serves.

You have an error in WHERE statement --
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q.duedate&
to be
WHERE Q1.samplenumber& Q1.customerid& Q1.matrix& Q1.site& Q1.duedate&

You have to have all Q1's on one side and Q's on the other of the Less Than
sign.

Also they must be in the same order. You have --
Q1.barcode &Q1.orderid& Q1.QCBatchId& Q1.DaysToDo <
and
Q.orderid& Q.QCBatchID& Q.DaysToDo& Q.barcode

karl.
I found some other post which advised making a union query. the result is

[quoted text clipped - 64 lines]

LDA

 

[LDA via AccessMonster.com]

Thanks for all your help...
Seems to be working just fine...
LDA

I would make sure there was a space after every comma and a space before and
after each ampersand.

Hello,
The union query idea came from

[quoted text clipped - 42 lines]