HELP FORMULATING COUNT IN QUERIES

G

Guest

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.
 
G

Guest

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"));
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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
 
G

Guest

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

Using "Count" in Queries 1
Repost, Help with combining queries 2
Combining queries in an adp 2
help with count (maybe) in query 7
Help - Trying to Combine 3 Queries into 1 4
Problem with Query and count/sum/totals/calculation 2

Top