Can I update fields automatically when creating a report?

[Bob83652]

Hi,
It's my first message and my first go at creating something in Access, not
sure if I'm thinking too big for my first go, I've had a few lessons and
maybe it's a case of 'no knowledge makes you dangerous'!
I understand the logic of it and am looking to create something similar to
Northwind (I guess). The problem is I'm not sure where to look for what I
need in the sample database.
I haven't created a table yet as they sign 'design is everything'.
The scenario I envisage is : I can get users to input sales to customers all
day long. At the end of the day, I want to create a report for my suppliers
to replace the stock that has been ordered.
The only way I can get my head round this is to have an invisible 'Ordered
from Supplier?' field on the order page with a default value of '0'. I can
then create an order to each supplier based on the '0' fields through a
'Create Supplier Orders' button, incorporated in this I then need to change
the 'Ordered from Supplier?' value to '1' so it doesn't show up again. Is
this the right way of going about things? If so, is this something that can
be done within Access' commands or is it a bit of script that I'll have to
learn?
Thanks,
Bob

 

[tina]

well, i have no experience in writing applications that track inventory, so
i can't speak to the effectiveness of the setup with any authority at all.
but i'm wondering if you're just wanting to generate a report, a "list" of
stock to be ordered, for somebody to order and track elsewhere? if so, your
proposal sounds simple enough. i might use a Date/Time data type for the
"Reordered" field; null when the sales order is entered by the user, then
changed to the current date/time when the report is generated. you could do
that by basing your report on a SELECT query that pulls only sales order
records where the Reordered field Is Null. run the report, then use a
duplicate of that query, changed to an Update query, to update the Reordered
field to Now() (system current date/time).

however, if you're wanting to track what stock was received after being
ordered, etc., it will require a more complex setup of tables, forms, etc.

hth

 

[Pete D.]

I like tina have no real experience with inventory control but have been
looking at it and reading what I can find. You might find this link from
Allen Browne's WEB helpful or at least very informative.
http://allenbrowne.com/AppInventory.html

 

[Barrett]

Tina's idea is great if you thinking of running this report more than once
per day. If you would be running it just once per day, then running a report
based on a query where the entry date = date() (This is today's date) would
be fine. You may run into a late order that is entered after you run the
report. This would cause you to run it again and risk "double ordering" or
missing that entry altogether. You could run the report the following day and
for all orders entered (date()-1). This would capture all orders from the
previous day without omitting or duplicating anything.

well, i have no experience in writing applications that track inventory, so
i can't speak to the effectiveness of the setup with any authority at all.
but i'm wondering if you're just wanting to generate a report, a "list" of
stock to be ordered, for somebody to order and track elsewhere? if so, your
proposal sounds simple enough. i might use a Date/Time data type for the
"Reordered" field; null when the sales order is entered by the user, then
changed to the current date/time when the report is generated. you could do
that by basing your report on a SELECT query that pulls only sales order
records where the Reordered field Is Null. run the report, then use a
duplicate of that query, changed to an Update query, to update the Reordered
field to Now() (system current date/time).

however, if you're wanting to track what stock was received after being
ordered, etc., it will require a more complex setup of tables, forms, etc.

hth

Hi,
It's my first message and my first go at creating something in Access, not

[quoted text clipped - 17 lines]

Thanks,
Bob