marcenmoni
New member
- Local time
- Today, 14:15
- Joined
- Sep 5, 2010
- Messages
- 3
Premise:
I use Access 2003.
I sell stamps on Ebay. I already have an Access database in place to automate the creation of my listings, but I'm looking to revamp my workflow to optimize my time to allow me to list more stamps. The new workflow will require an entirely different Access database, which I've started to tinker with.
The database structure has a catalog table, linked to a jpeg table (a list of scanned images of individual stamps, along with condition remarks), and a lot table (i.e. lots posted on Ebay or Delcampe or elsewhere- the lot table contains all the information for the listing, including a reference to the jpegname(s) of the stamp(s), the total catalog value, the sale price of the lot, etc.).
The jpeg table and lot table have a many-to-many relationship. In the majority of cases, one lot will equal one stamp (or jpeg). Sometimes, a lot may contain two stamps or more. Sometimes a lot may not sell so I discontinue the lot and reuse the same jpeg(s) in a new lot(s).
So I have a junction table to tie the jpeg list to the lot list. The junction table also gives me the freedom to have a quantity field, in case in the future I want to offer repeat quantities of a given lot.
My intended workflow would be as follows: I scan a bunch of jpegs at one time. I import the directory list of filenames into Access. This adds records to my jpeg table. I have Access automatically create linked lots for each jpeg since most times I will have one stamp per lot. For some lots however I will want to add other jpegs, and therefore reassign their lot IDs.
To do this, the easiest way for me would be to see a set of jpegs in a form, say 9-12 at a time in a grid. Each jpeg would be shown as a small thumbnail, and under the thumbnail, I have a text that indicates the lot number to which the jpeg is assigned to. Next to the text I'm thinking there could be three check boxes, labelled "first", "remaining" and "unassign". By default, none of the checkboxes of the thumbnails displayed would be checked.
So if I want to add jpegs to a certain lot, I simply check "first" check box of the lot I want to add to, and then check the "remaining" check box of the jpegs I want to add, and hit a 'confirm' button on the form that would run a module which would reassign the lotIDs of the selected jpegs.
I would also want to have a subform or datasheet on the side of the form showing the lot selected and its resulting parameters. In this way I can confirm the jpegs attached to the lot and the resulting lot sale price (based on the catalog value of the jpegs selected).
If I don't like the result, I could check the "unassign" checkbox and have Access reassign each jpeg to a unique lot, as before.
Three questions:
1) I'm assuming this form would necessarily have to be an Unbound form with perhaps a subform of the lot table on one side, and perhaps a continuous form of the jpeg table on the other side, arranged in some sort of grid of thumbnails. Am I correct with this assumption, or should I be looking at a form and subform layout referencing the junction table?
2) In order to arrange the jpeg list as a grid of thumbnails, I've found Dbpix online, and I'm assuming this program has code that will allow me to create a continuous form of a grid of thumbnails. However if someone knows of a form template that already does this, I'd like to know so I can check it out.
3) In the above workflow, reassigning the lot ID numbers means I will ultimately have unassigned lot numbers and lot IDs. Should I consider reindexing the lot IDs to avoid this? If it's a few 1000, it may not be an issue, but if I have 20,000 lots or more, I'm thinking reindexing may become a real problem. I do need to have sequential lot numbers for invoice tracking purposes, so I plan to have a lotID as primary key and a lot number as a text field that I can have the Access module regenerate (for the rows affected) when I confirm reassignments or unassignments of jpegs.
I'll be checking in tomorrow evening. Thanks in advance for reading and replying.
Marcello
I use Access 2003.
I sell stamps on Ebay. I already have an Access database in place to automate the creation of my listings, but I'm looking to revamp my workflow to optimize my time to allow me to list more stamps. The new workflow will require an entirely different Access database, which I've started to tinker with.
The database structure has a catalog table, linked to a jpeg table (a list of scanned images of individual stamps, along with condition remarks), and a lot table (i.e. lots posted on Ebay or Delcampe or elsewhere- the lot table contains all the information for the listing, including a reference to the jpegname(s) of the stamp(s), the total catalog value, the sale price of the lot, etc.).
The jpeg table and lot table have a many-to-many relationship. In the majority of cases, one lot will equal one stamp (or jpeg). Sometimes, a lot may contain two stamps or more. Sometimes a lot may not sell so I discontinue the lot and reuse the same jpeg(s) in a new lot(s).
So I have a junction table to tie the jpeg list to the lot list. The junction table also gives me the freedom to have a quantity field, in case in the future I want to offer repeat quantities of a given lot.
My intended workflow would be as follows: I scan a bunch of jpegs at one time. I import the directory list of filenames into Access. This adds records to my jpeg table. I have Access automatically create linked lots for each jpeg since most times I will have one stamp per lot. For some lots however I will want to add other jpegs, and therefore reassign their lot IDs.
To do this, the easiest way for me would be to see a set of jpegs in a form, say 9-12 at a time in a grid. Each jpeg would be shown as a small thumbnail, and under the thumbnail, I have a text that indicates the lot number to which the jpeg is assigned to. Next to the text I'm thinking there could be three check boxes, labelled "first", "remaining" and "unassign". By default, none of the checkboxes of the thumbnails displayed would be checked.
So if I want to add jpegs to a certain lot, I simply check "first" check box of the lot I want to add to, and then check the "remaining" check box of the jpegs I want to add, and hit a 'confirm' button on the form that would run a module which would reassign the lotIDs of the selected jpegs.
I would also want to have a subform or datasheet on the side of the form showing the lot selected and its resulting parameters. In this way I can confirm the jpegs attached to the lot and the resulting lot sale price (based on the catalog value of the jpegs selected).
If I don't like the result, I could check the "unassign" checkbox and have Access reassign each jpeg to a unique lot, as before.
Three questions:
1) I'm assuming this form would necessarily have to be an Unbound form with perhaps a subform of the lot table on one side, and perhaps a continuous form of the jpeg table on the other side, arranged in some sort of grid of thumbnails. Am I correct with this assumption, or should I be looking at a form and subform layout referencing the junction table?
2) In order to arrange the jpeg list as a grid of thumbnails, I've found Dbpix online, and I'm assuming this program has code that will allow me to create a continuous form of a grid of thumbnails. However if someone knows of a form template that already does this, I'd like to know so I can check it out.
3) In the above workflow, reassigning the lot ID numbers means I will ultimately have unassigned lot numbers and lot IDs. Should I consider reindexing the lot IDs to avoid this? If it's a few 1000, it may not be an issue, but if I have 20,000 lots or more, I'm thinking reindexing may become a real problem. I do need to have sequential lot numbers for invoice tracking purposes, so I plan to have a lotID as primary key and a lot number as a text field that I can have the Access module regenerate (for the rows affected) when I confirm reassignments or unassignments of jpegs.
I'll be checking in tomorrow evening. Thanks in advance for reading and replying.
Marcello
