Re: criteria issue

Tech-Archive recommends: Speed Up your PC by fixing your registry

THANKS FOR REPONDING....
THAT'S EXCACTLY WHAT I HAVE RIGHT NOW......THE PROBLEM WITH THAT IS THAT
SINCE I HAVE OrderNo in my select clause, what shows in my query are multiple
OrderNo with that is using the same items, so "items" are repeated and their
quantity needed. visual....

i have.......listbox......sched that has multiple column
OrderNo|RequiredDate|ModelNumer|Description|OrderQty
46001 05/04/07 a33m any 1
40006 05/04/07 a33m any 2

for every model number there are items that need to be ordered from vendors.
Here is where the Purchase Order report is generated. as i select from the
list box above, i click a button that prints the Purchase Order Report for
every selection from the listbox.

Problem: example above shows two different OrderNo of the same ModelNumber,
therefore, multiple of the same items will comeup for order from vendors
since is a multiselect list box.

i do get the items amounts right on the Purchase Order Report, the only thing
that i am having problem with is that same items are repeating. i do have a
test query that doesnt repeate the items, however it doesnt include OrderNo.
if i include OrderNo, the items will repeate since there could be multiple of
the same OrderNo. and if dont include this OrderNo on my test query, since
it's the strWhere clause criteria, i get no result on my report that has row
source test query that has no OrderNo field. sorry even explaining this is
difficult for me at this moment i hope you undertand.
this are the two query that i have......

1.....includes OrderNo, prints the report, but contains repeating items...
since there could be multiple order of the same ModelNum

SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.Type, qrAllItems.
qFill, Sum(qrAllItems.OrderQty) AS CountOfqFill
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.Type, qrAllItems.
qFill
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));


2......does not Include OrderNo, contains the right items and the right
quantity, but doesnt have OrderNo, therefore, doesnt print the report since
the strWhere is OrderNo.....

SELECT [qrAllItemsCountFX-Fab2].Vendor, [qrAllItemsCountFX-Fab2].qFill, Sum(
[qrAllItemsCountFX-Fab2].CountOfqFill) AS Total
FROM [qrAllItemsCountFX-Fab2]
GROUP BY [qrAllItemsCountFX-Fab2].Vendor, [qrAllItemsCountFX-Fab2].qFill,
[qrAllItemsCountFX-Fab2].Type
WITH OWNERACCESS OPTION;


i hope you got an answer for this...thanks again..



Michel Walsh wrote:
You mean the query on which is based your report has no "OrderNo" field?
Can't you include that field in the SELECT clause, even if you don't print
it anywhere in the report, so you can then use a whereCondition argument, in
DoCmd.OpenReport, which will imply the OrderNo.

Vanderghast, Access MVP

Hey Michel got it....work like suga...sweet ...thanks again.....
now i was wondering if you could help me with one other problem i have
[quoted text clipped - 15 lines]
and
more visuals.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1

.

Quantcast