If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can I update fields automatically when creating a report?
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 |
#2
|
|||
|
|||
Can I update fields automatically when creating a report?
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 "Bob83652" wrote in message ... 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 |
#3
|
|||
|
|||
Can I update fields automatically when creating a report?
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 "tina" wrote in message ... 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 "Bob83652" wrote in message ... 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 |
#4
|
|||
|
|||
Can I update fields automatically when creating a report?
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. tina wrote: 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 |
Thread Tools | |
Display Modes | |
|
|