HELP FORMULATING COUNT IN QUERIES

W

Will G

Hello,

iT'S THERE ANYTHING WRONG WITH THIS QUERY two queries, i am trying to get
one from the otherone. or it's there a best way to do this.

query one: name: qrAllItemsCountFX-Fa
SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.OrderQty,
qrAllItems.itemName, Count(qrAllItems.itemName) AS [Count]
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.OrderQty,
qrAllItems.itemName
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

query two: name:qrAllItemsCountFX-Fab
SELECT [qrAllItemsCountFX-Fa].Vendor, [qrAllItemsCountFX-Fa].OrderNo,
[qrAllItemsCountFX-Fa].itemName, [Count]*[OrderQty] AS total
FROM [qrAllItemsCountFX-Fa]
GROUP BY [qrAllItemsCountFX-Fa].Vendor, [qrAllItemsCountFX-Fa].OrderNo,
[qrAllItemsCountFX-Fa].itemName, [Count]*[OrderQty];

what i am trying to get on a report is:
Vendor, ItemName, Total
FF SP100 45

THERE SHOULD NOT BE REPEATING itemName, but that's where i get lost...
can anyone help please.
 
K

KARL DEWEY

This should do what you want.
SELECT qrAllItems.Vendor, qrAllItems.itemName, Sum(qrAllItems.OrderQty) AS
Total
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.itemName
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));
 
W

Will G

that seems to work, even though, i cant get the logic to it...thanks.....
--
need help


KARL DEWEY said:
This should do what you want.
SELECT qrAllItems.Vendor, qrAllItems.itemName, Sum(qrAllItems.OrderQty) AS
Total
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.itemName
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

--
KARL DEWEY
Build a little - Test a little


Will G said:
Hello,

iT'S THERE ANYTHING WRONG WITH THIS QUERY two queries, i am trying to get
one from the otherone. or it's there a best way to do this.

query one: name: qrAllItemsCountFX-Fa
SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.OrderQty,
qrAllItems.itemName, Count(qrAllItems.itemName) AS [Count]
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.OrderQty,
qrAllItems.itemName
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

query two: name:qrAllItemsCountFX-Fab
SELECT [qrAllItemsCountFX-Fa].Vendor, [qrAllItemsCountFX-Fa].OrderNo,
[qrAllItemsCountFX-Fa].itemName, [Count]*[OrderQty] AS total
FROM [qrAllItemsCountFX-Fa]
GROUP BY [qrAllItemsCountFX-Fa].Vendor, [qrAllItemsCountFX-Fa].OrderNo,
[qrAllItemsCountFX-Fa].itemName, [Count]*[OrderQty];

what i am trying to get on a report is:
Vendor, ItemName, Total
FF SP100 45

THERE SHOULD NOT BE REPEATING itemName, but that's where i get lost...
can anyone help please.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Look at it in design view.
--
KARL DEWEY
Build a little - Test a little


Will G said:
that seems to work, even though, i cant get the logic to it...thanks.....
--
need help


KARL DEWEY said:
This should do what you want.
SELECT qrAllItems.Vendor, qrAllItems.itemName, Sum(qrAllItems.OrderQty) AS
Total
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.itemName
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

--
KARL DEWEY
Build a little - Test a little


Will G said:
Hello,

iT'S THERE ANYTHING WRONG WITH THIS QUERY two queries, i am trying to get
one from the otherone. or it's there a best way to do this.

query one: name: qrAllItemsCountFX-Fa
SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.OrderQty,
qrAllItems.itemName, Count(qrAllItems.itemName) AS [Count]
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.OrderQty,
qrAllItems.itemName
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

query two: name:qrAllItemsCountFX-Fab
SELECT [qrAllItemsCountFX-Fa].Vendor, [qrAllItemsCountFX-Fa].OrderNo,
[qrAllItemsCountFX-Fa].itemName, [Count]*[OrderQty] AS total
FROM [qrAllItemsCountFX-Fa]
GROUP BY [qrAllItemsCountFX-Fa].Vendor, [qrAllItemsCountFX-Fa].OrderNo,
[qrAllItemsCountFX-Fa].itemName, [Count]*[OrderQty];

what i am trying to get on a report is:
Vendor, ItemName, Total
FF SP100 45

THERE SHOULD NOT BE REPEATING itemName, but that's where i get lost...
can anyone help please.
Click to expand...
Click to expand...
Click to expand...
 
W

Will G

Hey, i got it...sorry i am not that experienced with database development.

hey while we at this can you help me out with something else.
i have a multiselect list on a form and right now
looks like this.

OrderNo, RequiredDate, CustID, Desc, quantity
001 4/20/07 mdi any 1
001 4/20/07 cis any 2
001 4/23/07 mdi any 2
002 4/2/07 cfc any 5
002 4/3/07 mdi any 10

right now with the code bellow, when i select for example the first OrderNo
= 001, all the 001 are included.....and i know that's because of my criteria.
the strWhere clause. how can i just select every Order Individually? meaning
if i select the first OrderNo = 001, i just get that on my report. thanks for
your help.....

the code that i gathered around to make this listbox fuction.

strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
End If
'DoCmd.PrintOut , , , , quantity
'DoCmd.OpenForm "frmPrint", acNormal
Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"PrintMultRpt_Click"
End If
Resume Exit_Handler
 
K

KARL DEWEY

Do not know what you are asking. I do not know code, I use macros. You need
to ask someone else in a new post.
--
KARL DEWEY
Build a little - Test a little


Will G said:
Hey, i got it...sorry i am not that experienced with database development.

hey while we at this can you help me out with something else.
i have a multiselect list on a form and right now
looks like this.

OrderNo, RequiredDate, CustID, Desc, quantity
001 4/20/07 mdi any 1
001 4/20/07 cis any 2
001 4/23/07 mdi any 2
002 4/2/07 cfc any 5
002 4/3/07 mdi any 10

right now with the code bellow, when i select for example the first OrderNo
= 001, all the 001 are included.....and i know that's because of my criteria.
the strWhere clause. how can i just select every Order Individually? meaning
if i select the first OrderNo = 001, i just get that on my report. thanks for
your help.....

the code that i gathered around to make this listbox fuction.

strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
End If
'DoCmd.PrintOut , , , , quantity
'DoCmd.OpenForm "frmPrint", acNormal
Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"PrintMultRpt_Click"
End If
Resume Exit_Handler
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with report 0
Using "Count" in Queries 2
Joining two Queries to get ALL data 2
Repost, Help with combining queries 2
Count in a query 2
Count in totals query 3
Need to Count by two different criteria in same Totals query 4
help with 2 queries to compare and delete from same source table 0

Top