Re: search field and table name change required

Hi Damon,

Thanks for your query. I have modified it to suit my table.

table:

Stock_in
id, sin_item_no,stock_in_qty

Stock_out
id,so_item_no, stock_out_qty

where there is one single transaction that is one item is received and
shipped this query works like a charm.

When there are more that 2 or more transaction for the item than it doubles
the receiving qty and halves the shipped qty and calculates qoh.

EG:

SIN_ITEM_NO SUMOFUNITS RECEIVED SUMOFUNITS USED QOH
DUMMY100 10 5
15
DUMMY12 360 160
200
DUMMY11 200 20
180


ORIGINAL VALUE SHOULD BE
SIN_ITEM_NO SUMOFUNITS RECEIVED SUMOFUNITS USED QOH
DUMMY100 10 5
15
DUMMY12 360 160
200
DUMMY11 200 20
180

DUMMY11 HAS BEEN RECEIVED ONLY ONCE ALL OTHERS WERE RECIEVED AS PARTIAL
SHIPMENTS.
ID FOR STOCK IN AND STOCK OUT TABLE AUTOGENERATES EVERYTIME THERE IS A
TRANSACTION.

I think it is the way i split the table . is there any way i can use this
query without changing the table structure.
thanks




SELECT STOCK_OUT.SO_ITEM_NO, Sum(stock_in.STOCK_IN_QTY) AS
SumOfUnitsReceived, Sum(STOCK_OUT.STOCK_OUT_QTY) AS umOfUnitsUsed,
Sum(([stock_in_qty]-[stock_out_qty])) AS QOH
FROM STOCK_OUT INNER JOIN stock_in ON STOCK_OUT.SO_ITEM_NO =
stock_in.SIN_ITEM_NO
GROUP BY STOCK_OUT.SO_ITEM_NO
HAVING (((Sum(stock_in.STOCK_IN_QTY))<>0));


"Damon Heron" wrote:

This query will give you the QOH of all your inventory with > 0 received.
Change names, etc. to suit your table.

SELECT tblInventoryTransactions.SupplyID,
Sum(tblInventoryTransactions.UnitsReceived) AS SumOfUnitsReceived,
Sum(tblInventoryTransactions.UnitsUsed) AS SumOfUnitsUsed,
Sum(([UnitsReceived]-[UnitsUsed])) AS QOH
FROM tblInventoryTransactions
GROUP BY tblInventoryTransactions.SupplyID
HAVING (((Sum(tblInventoryTransactions.UnitsReceived))<>0));

You can use this as a query or in a report.

Damon

"vandy" <vandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:74E84567-9245-420E-AC38-8E81361CEBE7@xxxxxxxxxxxxxxxx
Hi Damon,

I am not sure if we can post another question in the same thread. Anyways!
I
have a text box which calculates Qty on hand on the fly. For each items
received in and issued out it would calculate the QOH. I am unable to
display
it as a query since it is not stored in a table. Is there anyway I can
query
the value to display as a report or a query. Let me know if you need
further
details.

thanks in advance for your help

"Damon Heron" wrote:

Your combobox has only numbers? Are these 200 numbers ones that you can
recall easily? You might consider the row source for the combo to
include a
name as well. In any event, you can type in the first few characters in
the
combobox to go to that item num. As an example, suppose your item
numbers
are really text - like AX12345 thru ZZ67890. If you type in the first
few
characters, then your list goes there and you dont have to scroll the
entire
combobox. I am guessing because your question isn't that clear.

Your other question concerning field names-- referring to the table or a
form? You can design the table to have almost any name you want, same
with
a form field. The query will have to be amended to select from your
table
with the new name, however.

HTH
Damon

"vandy" <vandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:02C08321-6ED3-4B24-B9C4-13F4697FC87C@xxxxxxxxxxxxxxxx
Hi All,

I have a scenario were i have a combo box which has item numbers
populated.
when i select the item no the subsequent detailed description gets
listed
in
the specific feilds. The problem now is there are more than 200 items
in
the
combo box and it is getting difficult to search through each item. Is
there
any way i can have a search feild included which points to the specific
item
in the combo box and once selected populates all the other data as
well.

The other problem is I have query which calculates the sum of stock
no.
The
feild name by default is sumofstock_in_qty i need to cutomize this is
this
possible.

thanks in advance.








.

Relevant Pages

  • Re: search field and table name change required
    ... Thanks for your query. ... the receiving qty and halves the shipped qty and calculates qoh. ... SIN_ITEM_NO SUMOFUNITS RECEIVED SUMOFUNITS USED QOH ...
    (microsoft.public.access.formscoding)
  • Re: search field and table name change required
    ... Thanks for your query. ... the receiving qty and halves the shipped qty and calculates qoh. ... SIN_ITEM_NO SUMOFUNITS RECEIVED SUMOFUNITS USED QOH ...
    (microsoft.public.access.formscoding)
  • Importing a .DBF file
    ... I'm receiving the following error message: ... Query must have at least one destination ...
    (microsoft.public.access.externaldata)
  • Re: Hide the Next Button
    ... the entire query rather than just the filtered version. ... And for whatever reason the recordcount I ... was receiving was actually for the larger query and not the filtered ... with large volumes so I stuck with this rather than debugging why the ...
    (microsoft.public.access.formscoding)
  • RE: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)