February 21st, 2013, 06:50 PM
Beginner stuck and looking for guidance
I'm fairly new to Access, but thought it would be a good tool for a project that I'm working on. I volunteer at a small warehouse that processes packages. The current method of processing involves placing new packages on numbered shelves and putting a blank sticky label on each box. I then write the recipients name name, date, shelf number, and a "box number" on the label. The "box number" starts at 1, counts up for each new box on the shelf, and resets for each shelf and for each day. Once all boxes are labeled, I fill out a pre-printed form that duplicates the information on the label and has 4 forms per sheet of paper, which are separated and delivered to the box recipients. This serves as notification that they have a package available for pickup and also allows me to find the package when they arrive to pick it up. The problem is that much of the data is the same each day, so manually filling it out is unnecessarily time consuming, which is where I'm trying to use Access.
As a first step, I am trying to use Access to create the notice slips that have the date, shelf number, and "box number" already filled in, making it so that I only need to write the name from the corresponding package label.
I currently have:
- "Shelf Number" table
- Report that has all the labels, current date, and takes up 1/4 sheet of paper
Now I'm stuck as to how to create a form that will use the total number new packages per shelf along with shelf location to populate individual entries in a "Packages" table, Then, how I would be able to use that data to print 4 notification slips per sheet of paper using my report layout.
I'm by no means asking anyone to do any work for me. After trying to learn as much about Access as I can and still not knowing what to search for to proceed, I just want to know if it is do-abe and where to look next. I'm definitely over my head and have a bunch of learning to do. Thanks!
February 22nd, 2013, 07:09 AM
If you select your Shelf Number table, then go to create form, it should create a rough draft of a form that you can use to enter your required information. You can modify to look better and suit your needs. In the properites set the orderby property to the shelf number (assuming they're numerical and starting with 1) Don't forget to turn it on in the properties. Each day when the form opens it will start at the first shelf. You enter the package info, hit the next button. 2nd number, enter package info and hit next. and so on.
As for your report - Create a query to looks up the records you want. Pick a field and use "IS NOT NULL" as the criteria, this will elimiate the shelves that have no packages. Use the query as the record source for your report.
February 22nd, 2013, 02:00 PM
Hi meratigoerr! Thanks for the starting point. I was able to see that by creating a form from the "shelf number" database and ordering by shelf number, my database does not contain the appropriate tables to make my query work. Here is more information and clarification:
I started over using simple data to get a better understanding of the process. To avoid confusion, I'm focusing on getting the data in to the database first and will worry about report output once that process is perfected.
I created a table called "Shelves"
- with one "Shelf" field
-- which contains records "A", "B", "C", and "D"
I created a "Packages" table
- with one "Package #" field and the "Shelf" field from the "Shelves" table
I then created a "Packages" form from the "Packages" table.
When using the form, I can select the shelf number and enter the package numbers individually for each shelf, and add the entries to the the Packages table, which is great, but I think it is more tedious than should be necessary. As all package numbers for any given shelf will always start with 1 and end with the total number of packages on each shelf, I'm now trying to figure out how to simplify the data entry and add multiple records with one entry on the form.
Eg. - I select shelf "A" on the form. Shelf "A" has 6 new packages, so I enter "4" in a box labeled "Packages on shelf".
On entry, the form adds the following records to the "Packages" table:
Package # | Shelf Number
I then select shelf B, which has 3 new packages, so I type 3, hit enter and get:
The data will reset each day, as the date is on each package and notice, so there will never be a duplicate entry.
It's what I do with the box labeled "Packages on shelf" that is my holdup now!
Again, thanks for the help!
February 23rd, 2013, 09:13 PM
I've been playing around with this for the last few days and still can't seem to get more than one record entered at a time. Can anyone direct me to the terminology of what I'm trying to do so that I can better search for a solution?
February 24th, 2013, 05:31 PM
I'm having some difficulty visualizing your scenario. Perhaps you should upload a copy of your db to a shared drive like box.net so that we can analyze it while reading your description and perhaps offer a viable solution
Office 2010, 2007, 2002
If I helped you, then click "give rep" button in the lower left corner.
February 25th, 2013, 06:41 AM
I get the feeling you've made this more complicated than it has to be. I'm assuming that your self can't change size or shape everyday. So why not just have every possible shelf and position combination permanant in the database. When a package comes in just, choose your shelf and spot number and assign the package to it. You can clear the packages everyday, but leave the shelf and spot assignments.