search field and table name change required

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Hi Damon,

Thanks for your reply. My combo box has dwg.no. Right now when i type the
no. i am looking for it is going to that location on the combo box. That was
quite a dumb question to ask , sorry about that.

As far as customizing the table names. I found out how to do it. Thanks a
ton.
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Your two tables are redundant. You really should modify your table into
one:
Stock:
ID, ItemNo, in, out -also other fields, like description, etc.

Why would you have two different item numbers for the same item(sin_item_no,
so_item_no)?

I understand the reluctance to modify tables, but the basics of relational
database design is the tables. You need a strong foundation to build a good
"data" house. It will pay dividends when you have unexpected demands on the
data.

Damon


vandy said:
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 said:
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 said:
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

:

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

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.
Click to expand...
Click to expand...
Click to expand...
 
HI Damon,

I took your advise and have input all the values in a table called
inventorytransactions. When I query the database using the item_no as the
reference i am getting the QOH.

SELECT InventoryTransactions.Stock_item_no,
Sum(InventoryTransactions.UnitsReceived) AS SumofUnitsReceived,
Sum(InventoryTransactions.UnitsUsed) AS SumofUnitsUsed,
Sum(([UnitsReceived]-[UnitsUsed])) AS QOH
FROM InventoryTransactions
GROUP BY InventoryTransactions.Stock_item_no
HAVING (((Sum(InventoryTransactions.UnitsReceived))<>0));

This is the desired result i want. thanks once again.
I wanted to also have a search field the combo box which searches for the
item no and if not found displays a msg saying item no not found. how can
this be done.

Thanks for your help

Damon Heron said:
Your two tables are redundant. You really should modify your table into
one:
Stock:
ID, ItemNo, in, out -also other fields, like description, etc.

Why would you have two different item numbers for the same item(sin_item_no,
so_item_no)?

I understand the reluctance to modify tables, but the basics of relational
database design is the tables. You need a strong foundation to build a good
"data" house. It will pay dividends when you have unexpected demands on the
data.

Damon


vandy said:
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 said:
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

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

:

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

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top