Tables and relationships?
in [Tables]
|
Prev: Access Log in
Next: How do you normalize a table?
From: John W. Vinson on 17 Jan 2010 19:11 On Sun, 17 Jan 2010 13:15:01 -0800, awsmitty <awsmitty(a)discussions.microsoft.com> wrote: >I see what you�re doing with the items table. I figured that 38+ fields >with a dozen rows at most was a bad idea, but I couldn�t figure out any other >way. What drew me towards this were things like the beds. Using your >example, I guess I could also have a table linked to beds, which contains all >the possibilities, twin, full, qn, etc, along with mattress, box spring, >rails, headboard, etc. Sometimes we get it all, sometimes we only get one >piece. Well, I'd suggest that you just store a record of what you actually got. You're not going to take apart a single bed and send the box spring to one place and the headboard to another, are you??? If you could just search for headboards (as items), and for beds (as items), would that meet your needs? I suppose if you were given just a headboard and frame you could enter that as two items; but I'd be inclined to enter a complete bed just as a bed. If circumstances required you could extract the components later! -- John W. Vinson [MVP]
From: awsmitty on 17 Jan 2010 21:34 John, The items are just what we pick up, not deliver; the list is only to give the drivers and the dispatchers on idea of how big the loads are getting for that day. These trucks do 6-10 pickups per day. It turns out to be several loads per day, per truck. I don't think I would ever be searching the items, maybe, but I'm not planning on it. The only searching is to get the grid from the address. So, yes, I'm wondering if these other tables tblItems, etc, all of them really, need to be linked. I mentioned this in my first post. I really truly don't know. The way I see it, and I could be wrong, what's needed is some temporary storage place, temporary – from the time the donation is called in to the time we pick it up, could be a few days, might be several months (honestly, some churches schedule months in advance to make sure we are there). Once the ticket is printed, at that point the whole ticket with all of its information, goes to some table to be stored indefinitely (I hear 7 years, for IRS purposes, but I really don't know). Now that table may need to be searchable. John makes a donation, files his taxes, but two years later the IRS comes along and questions it. In the meantime John has lost his receipt. John, or for that matter the IRS might come to us to verify that John donated all this stuff. I can find John easily enough, just give me his address and I'll use the same routine as when I found him the first time. I assume what Tom was trying to do, and what I'm interested in doing just as an educational exersize, is to manage the data efficiently and conveniently, and learn a little along the way. I assume Tom's method would be more efficient. Convenient, well, for you guys that know this forwards and backward, maybe so, but for me, I have to really keep my eye on the ball or I'll loose track of what's gong on. But, it could prove to be interesting. -- awsmitty "awsmitty" wrote: > First, the overall objective. A few others and I work in a dispatch office > in a homeless shelter. (I will just be upfront about it.) We are > responsible for … scheduling the trucks to pickup donations, keeping track of > pending pickup and the expected items, and keeping track of past donations > incase the IRS questions the legitimacy. > > I, for the life of me, cannot figure out the necessary tables and their > relationships. Here is what I'm thinking. There are four trucks, so, > tblTrucks, which has only one field (and an index field), trucks, A, B, C, > and D. There needs to be a table which lists the items, and there are many, > beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items, > and a memo box to cover the loose ends, all of these would be the fields. > The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or > for beds, fields like twin, full, queen, king, crib, twin with rails, > headboard, etc. Most of these once on the final ticket, would be simple > integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the > thought is to have these ultimately put on a form as combo boxes. (I hope > this is making sense!) There is a table of map grids. The drivers navigate > via maps grids so this table contains things like street, city, zip, hundred > block, and of course, the map grid, which truck(s) covers that map grid, and > this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems > that the truck picks up, these items obviously are common both truck and > donor, and of course the address is also common to the donor. Which brings > us to the next to the last table, one for the donor, not much left, name, > phone(s), that's about it, but I was also going to include on this table call > date, pickup date, ticket number, special pickup instructions, and this is > called tblPatrons. Finally the last table, which is a composite of all of > the above, tblDonations, the records of which would printed out the day of > the pickups and serve the driver, who hands it to the patron as a receipt, > which is also stored here for tax purposes. If you're like me, I'm saying to > myself, “What a sha-bang”, but that is what I'm thinking. > > I am open to criticism, suggestions, alterations, or dump the whole thing > and start over. The above is bad enough, but now the relationships. I can > see the relationship between the tblDonations and all the rest of the tables. > That's easy, and I'm hoping that's enough. So, envision tblDonations in the > center with four spokes coming of, on for each remaining table. Or, should > it be tblDonations related to truck, related to mapgrid, related to patron, > related to items. Or, does it really matter? I don't want to sound stupid, > but I really don't know. I am new; this is my very first at Access. > > I think you may also need what I'm shooting for as the final “product”, or > form. I'm hoping for one form with eight tabs, tab1, given the street > address, search for and find the corresponding map grid and truck. This is > already done. Tab2, a place were the final “ticket” is prepared, This would > be temporarily stored until the date of the pickup and would be identical to > the records of tblDonations, and this table is were it would be permanently > stored. (As you can probably see, I am hoping this is done “on the fly”, in > other words, as the donor is on the phone this whole thing is filled out.) > Tab3, simple, today's schedule, for all 4 trucks, on one form. Each record > printed off the morning of the pickup and given to the driver. Tab4, same > thing except for tomorrow. Tab5-8, the schedule for each truck as far into > the future as I can get. These may be datasheet, something I can scroll from > top to bottom. > > So, there's were I'm at. If anyone could comment on the tables, their > relationships, or just the overall structure it would be appreciated. > > Thanks > > I will try next time not to be so wordy, it's just that I need to remove > this from my head and put it into yours, as accurately as possible. > -- > awsmitty
From: BruceM via AccessMonster.com on 18 Jan 2010 07:37 Here are some links to Access tutorials and so forth. A better understanding of relational database principles will be a big help, I think: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/accessjunkie/resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials IMHO Crystal's tutorial is as good a place as any to get started. To sum up a point Tom made, field names are not the place to store data such as "bed". I think you understand the point, but a summary explanation can't hurt. I don't think there is a need to move the data once the item has been picked up. Just add a field to the Donations table to show the pickup date, or otherwise to indicate the item has in fact been picked up (rather than simply scheduled to be picked up). To get a list of items that have not been picked up yet, use a query based on the table to find all records in which PickUpDate is Null, or PickedUp is Yes, or whatever exactly works for you. To find donations by a person, or donations within a date range, or whatever you need, build a query or devise a filter to select the desired records only. The point for now is not to provide the details of how to go about that, but rather to point out that if a table contains all the necessary information about an entity (such as Items or Donor), you can refine the group of records you wish to see. If you have a field for the Donor and another for the DonationDate, you can find out what that person has donated in any time frame you choose. awsmitty wrote: >John, > >The items are just what we pick up, not deliver; the list is only to give >the drivers and the dispatchers on idea of how big the loads are getting for >that day. These trucks do 6-10 pickups per day. It turns out to be several >loads per day, per truck. I don't think I would ever be searching the items, >maybe, but I'm not planning on it. The only searching is to get the grid >from the address. So, yes, I'm wondering if these other tables tblItems, >etc, all of them really, need to be linked. I mentioned this in my first >post. I really truly don't know. The way I see it, and I could be wrong, >what's needed is some temporary storage place, temporary – from the time the >donation is called in to the time we pick it up, could be a few days, might >be several months (honestly, some churches schedule months in advance to make >sure we are there). Once the ticket is printed, at that point the whole >ticket with all of its information, goes to some table to be stored >indefinitely (I hear 7 years, for IRS purposes, but I really don't know). >Now that table may need to be searchable. John makes a donation, files his >taxes, but two years later the IRS comes along and questions it. In the >meantime John has lost his receipt. John, or for that matter the IRS might >come to us to verify that John donated all this stuff. I can find John >easily enough, just give me his address and I'll use the same routine as when >I found him the first time. I assume what Tom was trying to do, and what I'm >interested in doing just as an educational exersize, is to manage the data >efficiently and conveniently, and learn a little along the way. I assume >Tom's method would be more efficient. Convenient, well, for you guys that >know this forwards and backward, maybe so, but for me, I have to really keep >my eye on the ball or I'll loose track of what's gong on. But, it could >prove to be interesting. > >> First, the overall objective. A few others and I work in a dispatch office >> in a homeless shelter. (I will just be upfront about it.) We are >[quoted text clipped - 58 lines] >> I will try next time not to be so wordy, it's just that I need to remove >> this from my head and put it into yours, as accurately as possible. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201001/1
From: Fred on 18 Jan 2010 10:29 Whether you goal is to want this to work or to lear or both, your most crucial item for both of them is to 1. start by figuring out the "entities" that you want to database 2. Figure out a table structure (including links) to implement #1 Next, to clarify what I think is a communicaiton problems. My suggestion is to start with what Tom gave you, (and which Bruce reinforced) except that I would describe it in a totally dfferent way, and then change it 1% 1. A table of donations (with the "once per donation" type info 2. A table that is a list of the types of items that people donate 3. A table of instances of donations of donations of the above items. This is the table that Tom only briefly mentioned as the "third table". Many people view this (junction table) as merely a linking tool. I view it as a table of entities, in this case, as I described under #3 My 1% change is that #2 could merely be a static table that is used just as a "dropdown list" to populate an "item" field in #3. But either way Tom and I are essentially saying the same thing, and what he gave you should be your starting point. I'm guessing that you will also need tables for the following entities: 4. A semi-static list of trucks (for dropdown lists etc.) 5. A semi-static list of drivers (for dropdown lists eetc.) 6. A table of instances of a truck doing a "route" of pickups (planned or completed) Like all of Truck #1's pickups for January 21st. 7. A table of instances of a truck visiting a house. (planned or completed) #6 is linked to #7 Your table of "instances of donation of an item" should probably actually be "instances of an item being in your system". The linkage to a "donation" table identifies it as being pledged. You'd also link these to table #7, such a linkage would indicate "scheduled for pickup". You'd probably need a field to indicate "picked up" What you are trying to do requires record keeping for individual items. If the items are big (in value or size) such is probably viable. If you have a lot of little items you'll probably find that it wouild require so much entry that it won't actually happen. In that case you might want to invent an "item" which is a "miscellaneous pile/bag/box of little stuff" Or, it sounds like you may have that already. Given your cause, I'm sure that many here are rooting for you to get where you need to go. Don't be bashful about asking the questions to get the help you need succeed on this. Fred
From: Tom van Stiphout on 18 Jan 2010 23:17
On Sun, 17 Jan 2010 13:39:01 -0800, awsmitty <awsmitty(a)discussions.microsoft.com> wrote: I mean that this table will have a field named Quantity, which is of datatype Single, and it is a required field. This will allow you to record how many items of a particular kind there were in one particular donation. I first thought to use Integer for the data type, but what if someone donated $12.50? Maybe your item is "money", and your quantity is 12.5. -Tom. Microsoft Access MVP >Tom. >At the end of the forth paragraph, you mention"quantity single required". I >have to ask, what do you mean? |