Query

[MIKE]

I have a query set up so that it is linked to a table. I
would like to make it so after each new entry the query
would go to a new row in the table instead of just
changing the data in the same row each time. Also for
other columns I would like it to have the data from the
previous row and add the new data onto that number.
It is inventory query that i have set up to go to a given
product and i need to have the number on hand for each new
entry from the previous row but a new row for the units
ordered and the units used to help keep track of
transactions. I would rather not use a query instead just
have it open to the product from the product table by
entering the P/N. So if any one has an idea of what i
want and has an idea it would be appreciated. thanks
Mike

 

[Tim Ferguson]

I
would like to make it so after each new entry the query
would go to a new row in the table instead of just
changing the data in the same row each time.

Always use a form for data entry: you get all the control required in the
posting.

Also for
other columns I would like it to have the data from the
previous row and add the new data onto that number.

Remember that in a database application, there is no concept of a
"previous" record, unless you have sorted the recordset in some way. I am
wondering if this application would be easier to create in Excel..?

It is inventory query that i have set up to go to a given
product and i need to have the number on hand for each new
entry from the previous row but a new row for the units
ordered and the units used to help keep track of
transactions.

Now I'm confused: you have mentioned at least three different entities here
-- products, units, and orders, and probably transactions as well --, so is
it safe to assume that you have four related tables?

I would rather not use a query instead just
have it open to the product from the product table by
entering the P/N.

You can do all kinds of search using a form, depending on how good your
programming skills are. But integrating four tables on one form is likely
to need at least some programming to avoid fagging out the user completely.

All the best


Tim F

 

[Mike]

I was afraid i might be a litle confusing. I know what i
want it to do but dont know enough about access to now how
to explain it. This could be why i cant get it to do what
i would like because I dont know what to use. I have
tryed to figure out how to do some programming to get it
to do what i would like but my programming skills are a
little lacking so I abandoned that.
I am working with just 2 tables i believe, a products
table and a transaction table. Using a query I set it up
a parameter query to enter P/N to to go to that part. I
then set up a form based on this query with units orders,
used, and units on hand on it but i would like it to
retain the number on hand and just update it by adding the
number ordered to it and reset the rest of the fields. In
the inventory control database the original product form
does almost all i would like it to do but I would like to
enter a P/N and go directly to it instead of searching
through the list. that is Y i tryed the query. I hope
this helps clear things up a bit and there might be more
ideas.
Thanks

 

[Tim Ferguson]

I am working with just 2 tables i believe, a products
table and a transaction table. Using a query I set it up
a parameter query to enter P/N to to go to that part. I
then set up a form based on this query with units orders,
used, and units on hand on it but i would like it to
retain the number on hand and just update it by adding the
number ordered to it and reset the rest of the fields.

I am still a little bit suspicious about your table design. The Products
table will just contain details of the products: VendorNumber,
CatalogueCode, Colour and so on. Presumably the Transactions table lists
all uses and orders, so that you can tell how many items you have on hand
by simply adding all the orders and subtracting all the items used. There
should not be any fields anywhere for things like NumberOnHand, because
this is all derived from the basic data. Your form should simply have a
method of adding a Transaction record for an order or a use, and have a
text box using DSum() somewhere for the user to see how many are left in
store.

I would like to
enter a P/N and go directly to it instead of searching
through the list. that is Y i tryed the query. I

Simplest way is query-by-form -- have you tried this? If it does not meet
your need, then you will need to get into some VBA programming.

HTH


Tim F