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
|
|||
|
|||
Design help, please
Hello. I just designed a new section of my db. We receive
donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal |
#2
|
|||
|
|||
What you would want is five tables (PK = primary key; CPK = composite
primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal |
#3
|
|||
|
|||
Thnaks for the help- I'll give it a try.
I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Also, only tblAuctionDonors has a primary key- why is that? I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . |
#4
|
|||
|
|||
Comments/answers inline...
-- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thnaks for the help- I'll give it a try. I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). Yes, tblAuctionGifts would contain one record for each gift that is donated for an auction. So in your example, you would have two records. tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Yes, this table contains all the "items" (whether individual gifts that were donated or your combined "baskets" that are composed of many individual gifts). So these are the items on which people are actually bidding. And this table allows you to store all auctions' items in one table; each item is associated to a specific auction. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Yes, you are understanding correctly. Also, only tblAuctionDonors has a primary key- why is that? All of the example tables that I posted contain a primary key. CPK means that the primary key consists of more than one field. It's "composed" of more than one field; thus, it's called a composite primary key. Alternatively, you can add a separate PK field, and then you'd create a unique index on the fields that I labeled with the CPK designation. I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. No problem. Good luck. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . |
#5
|
|||
|
|||
Ken, thanks for the clarification- I appreciate it and was
able to build the tables, but of course now I'm struggling with the forms. I created an AuctionDonor form that contains pertinent donor information from the tblAuctionDonors (name, address...). I originally had a subform that showed all of the things that the donor had given including the value, minimum bid, description. But now I'm confused because the tblAuctionGifts that contains the gifts we get for an auction doesn't seem to contain any of that information, rather tblAuctionItems does. I still must track the description of each item donated and link it to a donor (so I can thank them properly for their gifts and what-not). So I'm not sure what the Donor form / items subform would look like. I like the idea of tblAuctionItems to show items available for each auction. Question: tblAuctionItemContents- can I create a Basket1 for the given auction name (so that 5 years from now, I don't have to start at Basket 2,000) and then tell the basket which auction items it holds? I have the AuctionName form done (the easy one you know!). I believe the third form would show both individual auction items and basket that are composed of individual items combined together. What would that look like? Thanks for your patience, Sal -----Original Message----- Comments/answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thnaks for the help- I'll give it a try. I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). Yes, tblAuctionGifts would contain one record for each gift that is donated for an auction. So in your example, you would have two records. tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Yes, this table contains all the "items" (whether individual gifts that were donated or your combined "baskets" that are composed of many individual gifts). So these are the items on which people are actually bidding. And this table allows you to store all auctions' items in one table; each item is associated to a specific auction. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Yes, you are understanding correctly. Also, only tblAuctionDonors has a primary key- why is that? All of the example tables that I posted contain a primary key. CPK means that the primary key consists of more than one field. It's "composed" of more than one field; thus, it's called a composite primary key. Alternatively, you can add a separate PK field, and then you'd create a unique index on the fields that I labeled with the CPK designation. I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. No problem. Good luck. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message .. . Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . . |
#6
|
|||
|
|||
Answers inline...
-- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Ken, thanks for the clarification- I appreciate it and was able to build the tables, but of course now I'm struggling with the forms. I created an AuctionDonor form that contains pertinent donor information from the tblAuctionDonors (name, address...). I originally had a subform that showed all of the things that the donor had given including the value, minimum bid, description. But now I'm confused because the tblAuctionGifts that contains the gifts we get for an auction doesn't seem to contain any of that information, rather tblAuctionItems does. Create a query that joins tblAuctionGifts to tblAuctionItems table. Use that query to display the desired information about the auction items' details; use the AuctionGiftID as the joining field for that. For example: SELECT T.AuctionItemID, T.AuctionItemValue, T.AuctionItemMinBid, G.AuctionGiftID FROM tblAuctionGifts AS G INNER JOIN tblAuctionItems AS T ON G.AuctionGiftID=T.AuctionGiftID; I still must track the description of each item donated and link it to a donor (so I can thank them properly for their gifts and what-not). So I'm not sure what the Donor form / items subform would look like. Depending upon how you want to display it, you could put the donor's information on the main form, and have a subform that shows all auction gifts donated by that person (there would be one or more records in this subform with this setup). Alternatively, you could put the auction gift on the main form, and use a subform to display the donor's information (there'd be just one record in this subform with this setup). I like the idea of tblAuctionItems to show items available for each auction. Question: tblAuctionItemContents- can I create a Basket1 for the given auction name (so that 5 years from now, I don't have to start at Basket 2,000) and then tell the basket which auction items it holds? The AuctionItemID should be a value that is unique within a single auction; remember, it's the combination of the AuctionID and the AuctionItemID that makes the record unique. Thus, you can have a Basket1 value for AuctionItemID in multiple auctions, but there can be only one Basket1 value for any one auction. So an auction held on May 1, 2005 (AuctionID = 1) can have a Basket1 AuctionItemID, and so can an auction held on June 1, 2005 (AuctionID = 2). And so on. I have the AuctionName form done (the easy one you know!). I believe the third form would show both individual auction items and basket that are composed of individual items combined together. What would that look like? This form likely could be of two diffferent types. One, put the auction item on the main form (select the auction ID by a combo box in the main form's header to filter to the items for that auction), and then put the auction gifts in a subform. Alternatively, use a main form to show the auction, a subform to show the auction items, and a subform within the subform to show the auction gifts for each item. Thanks for your patience, Sal -----Original Message----- Comments/answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thnaks for the help- I'll give it a try. I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). Yes, tblAuctionGifts would contain one record for each gift that is donated for an auction. So in your example, you would have two records. tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Yes, this table contains all the "items" (whether individual gifts that were donated or your combined "baskets" that are composed of many individual gifts). So these are the items on which people are actually bidding. And this table allows you to store all auctions' items in one table; each item is associated to a specific auction. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Yes, you are understanding correctly. Also, only tblAuctionDonors has a primary key- why is that? All of the example tables that I posted contain a primary key. CPK means that the primary key consists of more than one field. It's "composed" of more than one field; thus, it's called a composite primary key. Alternatively, you can add a separate PK field, and then you'd create a unique index on the fields that I labeled with the CPK designation. I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. No problem. Good luck. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message . .. Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . . |
#7
|
|||
|
|||
Thanks- hard to believe I'm still having problems.
I have tables: tblAuctionDonors AuctionDonorID (PK) FirstName LastName tblAuctionGifts (I put the "key" symbol next to both the ID fields which I hope gives me a composite primary key) AuctionGiftsID AuctionDonorID tblAuctionItems (CPK with the next 2 listed IDs) AuctionItemID AuctionID DateReceived ItemValue ShortDescription MinimumBid AuctionGiftsID (I added this myself, think I need it; or maybe I should have added AuctionItemID to AuctionGifts?) tblAuctionItemContents (CPK with the next 2 listed IDs) AuctionItemID AuctionGiftsID Auction Donor Entry (form): Donor listing (name, address). Record source is tblAuctionDonor. So the main form is fine. But I'm having problems with the subform. I tried to create a query like the one you suggested: SELECT tblAuctionGifts.AuctionDonorID, tblAuctionItems.AuctionItemID, tblAuctionItems.ItemValue, tblAuctionItems.AuctionItemName, tblAuctionItems.MinimumBid, tblAuctionGifts.AuctionGiftsID, FROM tblAuctionGifts INNER JOIN tblAuctionItems ON tblAuctionGifts.AuctionGiftsID = tblAuctionItems.AuctionGiftsID; which seems like it has everything I need: it links together DonorID, ItemID, and GiftsID. Yet, it opens "grayed out" so that I can't enter anything into it. I see that given the table setup, I need both Gifts and Items together to be able to link to Donors. I expect this subform to allow me to list, in datasheet form, the items received from each donor. I'd like to get the first form working before I move to the other one. Thanks for your help, Sal -----Original Message----- Answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Ken, thanks for the clarification- I appreciate it and was able to build the tables, but of course now I'm struggling with the forms. I created an AuctionDonor form that contains pertinent donor information from the tblAuctionDonors (name, address...). I originally had a subform that showed all of the things that the donor had given including the value, minimum bid, description. But now I'm confused because the tblAuctionGifts that contains the gifts we get for an auction doesn't seem to contain any of that information, rather tblAuctionItems does. Create a query that joins tblAuctionGifts to tblAuctionItems table. Use that query to display the desired information about the auction items' details; use the AuctionGiftID as the joining field for that. For example: SELECT T.AuctionItemID, T.AuctionItemValue, T.AuctionItemMinBid, G.AuctionGiftID FROM tblAuctionGifts AS G INNER JOIN tblAuctionItems AS T ON G.AuctionGiftID=T.AuctionGiftID; I still must track the description of each item donated and link it to a donor (so I can thank them properly for their gifts and what-not). So I'm not sure what the Donor form / items subform would look like. Depending upon how you want to display it, you could put the donor's information on the main form, and have a subform that shows all auction gifts donated by that person (there would be one or more records in this subform with this setup). Alternatively, you could put the auction gift on the main form, and use a subform to display the donor's information (there'd be just one record in this subform with this setup). I like the idea of tblAuctionItems to show items available for each auction. Question: tblAuctionItemContents- can I create a Basket1 for the given auction name (so that 5 years from now, I don't have to start at Basket 2,000) and then tell the basket which auction items it holds? The AuctionItemID should be a value that is unique within a single auction; remember, it's the combination of the AuctionID and the AuctionItemID that makes the record unique. Thus, you can have a Basket1 value for AuctionItemID in multiple auctions, but there can be only one Basket1 value for any one auction. So an auction held on May 1, 2005 (AuctionID = 1) can have a Basket1 AuctionItemID, and so can an auction held on June 1, 2005 (AuctionID = 2). And so on. I have the AuctionName form done (the easy one you know!). I believe the third form would show both individual auction items and basket that are composed of individual items combined together. What would that look like? This form likely could be of two diffferent types. One, put the auction item on the main form (select the auction ID by a combo box in the main form's header to filter to the items for that auction), and then put the auction gifts in a subform. Alternatively, use a main form to show the auction, a subform to show the auction items, and a subform within the subform to show the auction gifts for each item. Thanks for your patience, Sal -----Original Message----- Comments/answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message .. . Thnaks for the help- I'll give it a try. I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). Yes, tblAuctionGifts would contain one record for each gift that is donated for an auction. So in your example, you would have two records. tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Yes, this table contains all the "items" (whether individual gifts that were donated or your combined "baskets" that are composed of many individual gifts). So these are the items on which people are actually bidding. And this table allows you to store all auctions' items in one table; each item is associated to a specific auction. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Yes, you are understanding correctly. Also, only tblAuctionDonors has a primary key- why is that? All of the example tables that I posted contain a primary key. CPK means that the primary key consists of more than one field. It's "composed" of more than one field; thus, it's called a composite primary key. Alternatively, you can add a separate PK field, and then you'd create a unique index on the fields that I labeled with the CPK designation. I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. No problem. Good luck. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message .. . Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . . . |
#8
|
|||
|
|||
Answers inline....
-- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thanks- hard to believe I'm still having problems. I have tables: tblAuctionDonors AuctionDonorID (PK) FirstName LastName OK good. tblAuctionGifts (I put the "key" symbol next to both the ID fields which I hope gives me a composite primary key) AuctionGiftsID AuctionDonorID Although this is what I posted in the original structure, I think you can use just AuctionGiftsID as the primary key. No need to have a composite primary key now that I've thought about this a bit more. (And yes, one way you can create a composite primary key is to highlight both fields and the click the Key icon.) You also could add more fields to this table, such as AuctionGiftDateDonated, AuctionGiftDescription, etc. tblAuctionItems (CPK with the next 2 listed IDs) AuctionItemID AuctionID DateReceived ItemValue ShortDescription MinimumBid AuctionGiftsID (I added this myself, think I need it; or maybe I should have added AuctionItemID to AuctionGifts?) Delete the AuctionGiftsID field from the above table. AuctionGiftsID is used in tblAuctionItemContents to relate an item to the gift(s) that compose that item. tblAuctionItemContents (CPK with the next 2 listed IDs) AuctionItemID AuctionGiftsID OK good. You're missing the tblAuctions table, from which you get the value for AuctionID in tblAuctionItems table. Auction Donor Entry (form): Donor listing (name, address). Record source is tblAuctionDonor. So the main form is fine. But I'm having problems with the subform. I tried to create a query like the one you suggested: SELECT tblAuctionGifts.AuctionDonorID, tblAuctionItems.AuctionItemID, tblAuctionItems.ItemValue, tblAuctionItems.AuctionItemName, tblAuctionItems.MinimumBid, tblAuctionGifts.AuctionGiftsID, FROM tblAuctionGifts INNER JOIN tblAuctionItems ON tblAuctionGifts.AuctionGiftsID = tblAuctionItems.AuctionGiftsID; which seems like it has everything I need: it links together DonorID, ItemID, and GiftsID. Yet, it opens "grayed out" so that I can't enter anything into it. I see that given the table setup, I need both Gifts and Items together to be able to link to Donors. I expect this subform to allow me to list, in datasheet form, the items received from each donor. The query that I'd posted is just an example. It will not work for the donor form and gift subform set up. In this case, the subform should use a query similar to this: SELECT * FROM tblAuctionGifts; Then you set the LinkMasterFields and LinkChildFields properties for the subform control (the control on the main form that holds the gifts subform) to link the subform's records to the main form's records. In this case, set both properties to AuctionDonorID. Then, as you select a donor on the main form, the subform will show the records for that donor, including the ability to enter new records. I'd like to get the first form working before I move to the other one. Thanks for your help, Sal -----Original Message----- Answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Ken, thanks for the clarification- I appreciate it and was able to build the tables, but of course now I'm struggling with the forms. I created an AuctionDonor form that contains pertinent donor information from the tblAuctionDonors (name, address...). I originally had a subform that showed all of the things that the donor had given including the value, minimum bid, description. But now I'm confused because the tblAuctionGifts that contains the gifts we get for an auction doesn't seem to contain any of that information, rather tblAuctionItems does. Create a query that joins tblAuctionGifts to tblAuctionItems table. Use that query to display the desired information about the auction items' details; use the AuctionGiftID as the joining field for that. For example: SELECT T.AuctionItemID, T.AuctionItemValue, T.AuctionItemMinBid, G.AuctionGiftID FROM tblAuctionGifts AS G INNER JOIN tblAuctionItems AS T ON G.AuctionGiftID=T.AuctionGiftID; I still must track the description of each item donated and link it to a donor (so I can thank them properly for their gifts and what-not). So I'm not sure what the Donor form / items subform would look like. Depending upon how you want to display it, you could put the donor's information on the main form, and have a subform that shows all auction gifts donated by that person (there would be one or more records in this subform with this setup). Alternatively, you could put the auction gift on the main form, and use a subform to display the donor's information (there'd be just one record in this subform with this setup). I like the idea of tblAuctionItems to show items available for each auction. Question: tblAuctionItemContents- can I create a Basket1 for the given auction name (so that 5 years from now, I don't have to start at Basket 2,000) and then tell the basket which auction items it holds? The AuctionItemID should be a value that is unique within a single auction; remember, it's the combination of the AuctionID and the AuctionItemID that makes the record unique. Thus, you can have a Basket1 value for AuctionItemID in multiple auctions, but there can be only one Basket1 value for any one auction. So an auction held on May 1, 2005 (AuctionID = 1) can have a Basket1 AuctionItemID, and so can an auction held on June 1, 2005 (AuctionID = 2). And so on. I have the AuctionName form done (the easy one you know!). I believe the third form would show both individual auction items and basket that are composed of individual items combined together. What would that look like? This form likely could be of two diffferent types. One, put the auction item on the main form (select the auction ID by a combo box in the main form's header to filter to the items for that auction), and then put the auction gifts in a subform. Alternatively, use a main form to show the auction, a subform to show the auction items, and a subform within the subform to show the auction gifts for each item. Thanks for your patience, Sal -----Original Message----- Comments/answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message . .. Thnaks for the help- I'll give it a try. I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). Yes, tblAuctionGifts would contain one record for each gift that is donated for an auction. So in your example, you would have two records. tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Yes, this table contains all the "items" (whether individual gifts that were donated or your combined "baskets" that are composed of many individual gifts). So these are the items on which people are actually bidding. And this table allows you to store all auctions' items in one table; each item is associated to a specific auction. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Yes, you are understanding correctly. Also, only tblAuctionDonors has a primary key- why is that? All of the example tables that I posted contain a primary key. CPK means that the primary key consists of more than one field. It's "composed" of more than one field; thus, it's called a composite primary key. Alternatively, you can add a separate PK field, and then you'd create a unique index on the fields that I labeled with the CPK designation. I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. No problem. Good luck. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message . .. Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . . . |
#9
|
|||
|
|||
Thanks for the clarification- before I posted I had the
donor form/gifts subform correct, but then got confused with items and gifts. So now I'm able to enter donors and their multiple donated gifts. tblAuctionGifts now contains date donated, value, description, min. bid... so I removed these type of fields from tblAuctionItems since tblAuctionItemContents has CPK to Items and Gifts. I really do have an Auction table tblAuctions AuctionID AuctionDate AuctionName AuctionLocation In tblAuctionItems, I removed AuctionGiftsID as instructed. Okay, then! So now I want to be able to list Items for an Auction as well as create baskets of items for an auction. Once the item is assigned to a basket, I don't want it to be available as a "lone" item (but of course, I may need to unassign it from a basket to either go to a different basket or stand alone). I not really following what to do with tblAuctionItems and tblAuctionItemContents or how to create a form that shows the items available and then create baskets. Not much of a visionary, sadly. Thanks for the guidance, Sal -----Original Message----- Answers inline.... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thanks- hard to believe I'm still having problems. I have tables: tblAuctionDonors AuctionDonorID (PK) FirstName LastName OK good. tblAuctionGifts (I put the "key" symbol next to both the ID fields which I hope gives me a composite primary key) AuctionGiftsID AuctionDonorID Although this is what I posted in the original structure, I think you can use just AuctionGiftsID as the primary key. No need to have a composite primary key now that I've thought about this a bit more. (And yes, one way you can create a composite primary key is to highlight both fields and the click the Key icon.) You also could add more fields to this table, such as AuctionGiftDateDonated, AuctionGiftDescription, etc. tblAuctionItems (CPK with the next 2 listed IDs) AuctionItemID AuctionID DateReceived ItemValue ShortDescription MinimumBid AuctionGiftsID (I added this myself, think I need it; or maybe I should have added AuctionItemID to AuctionGifts?) Delete the AuctionGiftsID field from the above table. AuctionGiftsID is used in tblAuctionItemContents to relate an item to the gift (s) that compose that item. tblAuctionItemContents (CPK with the next 2 listed IDs) AuctionItemID AuctionGiftsID OK good. You're missing the tblAuctions table, from which you get the value for AuctionID in tblAuctionItems table. Auction Donor Entry (form): Donor listing (name, address). Record source is tblAuctionDonor. So the main form is fine. But I'm having problems with the subform. I tried to create a query like the one you suggested: SELECT tblAuctionGifts.AuctionDonorID, tblAuctionItems.AuctionItemID, tblAuctionItems.ItemValue, tblAuctionItems.AuctionItemName, tblAuctionItems.MinimumBid, tblAuctionGifts.AuctionGiftsID, FROM tblAuctionGifts INNER JOIN tblAuctionItems ON tblAuctionGifts.AuctionGiftsID = tblAuctionItems.AuctionGiftsID; which seems like it has everything I need: it links together DonorID, ItemID, and GiftsID. Yet, it opens "grayed out" so that I can't enter anything into it. I see that given the table setup, I need both Gifts and Items together to be able to link to Donors. I expect this subform to allow me to list, in datasheet form, the items received from each donor. The query that I'd posted is just an example. It will not work for the donor form and gift subform set up. In this case, the subform should use a query similar to this: SELECT * FROM tblAuctionGifts; Then you set the LinkMasterFields and LinkChildFields properties for the subform control (the control on the main form that holds the gifts subform) to link the subform's records to the main form's records. In this case, set both properties to AuctionDonorID. Then, as you select a donor on the main form, the subform will show the records for that donor, including the ability to enter new records. I'd like to get the first form working before I move to the other one. Thanks for your help, Sal -----Original Message----- Answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message .. . Ken, thanks for the clarification- I appreciate it and was able to build the tables, but of course now I'm struggling with the forms. I created an AuctionDonor form that contains pertinent donor information from the tblAuctionDonors (name, address...). I originally had a subform that showed all of the things that the donor had given including the value, minimum bid, description. But now I'm confused because the tblAuctionGifts that contains the gifts we get for an auction doesn't seem to contain any of that information, rather tblAuctionItems does. Create a query that joins tblAuctionGifts to tblAuctionItems table. Use that query to display the desired information about the auction items' details; use the AuctionGiftID as the joining field for that. For example: SELECT T.AuctionItemID, T.AuctionItemValue, T.AuctionItemMinBid, G.AuctionGiftID FROM tblAuctionGifts AS G INNER JOIN tblAuctionItems AS T ON G.AuctionGiftID=T.AuctionGiftID; I still must track the description of each item donated and link it to a donor (so I can thank them properly for their gifts and what-not). So I'm not sure what the Donor form / items subform would look like. Depending upon how you want to display it, you could put the donor's information on the main form, and have a subform that shows all auction gifts donated by that person (there would be one or more records in this subform with this setup). Alternatively, you could put the auction gift on the main form, and use a subform to display the donor's information (there'd be just one record in this subform with this setup). I like the idea of tblAuctionItems to show items available for each auction. Question: tblAuctionItemContents- can I create a Basket1 for the given auction name (so that 5 years from now, I don't have to start at Basket 2,000) and then tell the basket which auction items it holds? The AuctionItemID should be a value that is unique within a single auction; remember, it's the combination of the AuctionID and the AuctionItemID that makes the record unique. Thus, you can have a Basket1 value for AuctionItemID in multiple auctions, but there can be only one Basket1 value for any one auction. So an auction held on May 1, 2005 (AuctionID = 1) can have a Basket1 AuctionItemID, and so can an auction held on June 1, 2005 (AuctionID = 2). And so on. I have the AuctionName form done (the easy one you know!). I believe the third form would show both individual auction items and basket that are composed of individual items combined together. What would that look like? This form likely could be of two diffferent types. One, put the auction item on the main form (select the auction ID by a combo box in the main form's header to filter to the items for that auction), and then put the auction gifts in a subform. Alternatively, use a main form to show the auction, a subform to show the auction items, and a subform within the subform to show the auction gifts for each item. Thanks for your patience, Sal -----Original Message----- Comments/answers inline... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message .. . Thnaks for the help- I'll give it a try. I'm a little confused about tblAuctionGifts, tblAuctionItems and tblAuctionItemContents. Before I had AuctionID as a CPK in tblAuctionGifts. tblAuctionGifts would contain separately an airline ticket and then a hotel room (from 2 different AuctionDonors). Yes, tblAuctionGifts would contain one record for each gift that is donated for an auction. So in your example, you would have two records. tblAuctionItems would essentially link the AuctionGift with those available for the correct auction (?) So, for Gala 2005, both the ticket and the room are available. Yes, this table contains all the "items" (whether individual gifts that were donated or your combined "baskets" that are composed of many individual gifts). So these are the items on which people are actually bidding. And this table allows you to store all auctions' items in one table; each item is associated to a specific auction. Then tblAuctionItemContents links the AuctionGifts to the AuctionItems, so Basket1 could contain GiftA (ticket) and GiftB (room). Am I sort of getting it? Yes, you are understanding correctly. Also, only tblAuctionDonors has a primary key- why is that? All of the example tables that I posted contain a primary key. CPK means that the primary key consists of more than one field. It's "composed" of more than one field; thus, it's called a composite primary key. Alternatively, you can add a separate PK field, and then you'd create a unique index on the fields that I labeled with the CPK designation. I'll build the tables and will no doubt have form design questions if you don't mind. I appreciate your help. No problem. Good luck. Thanks, Sal -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Hello. I just designed a new section of my db. We receive donated auction items and want to track the AuctionGiver and AuctionGift. We also want to create auction sheets (that give the price, minimum bid, room for people to make bids). The auction sheet has the description of the auction item. All went well. Until I realized that we often combine auction items to create gift baskets (things that go well together like a hotel stay and airline tickets, or small items that are grouped together for greater value). We need to keep these items separate in that we need to be able to track which AuctionGiver gave which AuctionGift. But I'd also like to create a "Basket" section where we could track AuctionGifts lumped together. I'm a little mystified how to do the "basket" thing. I'd appreciate any suggestions. Thank you for your consideration, Sal . . . . |
#10
|
|||
|
|||
Sounds as if you're making good progress.
I think I would design your form for items and their contents this way. Create a form (name it sfrmAuctionItemContents) whose recordsource is similar to this: SELECT tblAuctionItemContents.*, tblAuctionGifts.* FROM tblAuctionItemContents INNER JOIN tblAuctionGifts ON tblAuctionItemContents.AuctionGiftID = tblAuctionGifts.AuctionGiftID; Set up this form as a Continuous Forms format (it will be the subform for the next form). Put the desired controls on the form that you'll want to see/edit when adding individual gifts to an item. Save and close this form. Create a form (name it frmAuctionItems) whose recordsource is similar to this: SELECT * FROM tblAuctionItems WHERE [AuctionID]=[Forms]![frmAuctionItems]![cboAuction]; In the form's header, put a combo box (name it cboAuction) whose Row Source is similar to this: SELECT AuctionID, AuctionName, AuctionDate FROM tblAuctions ORDER BY AuctionDate DESC; Use the AfterUpdate event of this combo box to requery the main form's RecordSource: Private Sub cboAuction_AfterUpdate() Me.Requery End Sub (If you don't know how to create this code to run from an event, let me know.) In the detail section of this form (it should be set for Single View format), put controls from the tblAuctionItems table that you will want to see/edit. In the detail section, put a subform control. Use sfrmAuctionItemContents as the Source Object. Set the LinkChildFields and LinkMasterFields to AuctionItemID. Now the subform will be used to enter the gifts that are part of each item. Save and close this form. This setup will not let you create a new auction. You'll need a separate form that will let you create a new record in tblAuctions table for a new auction. Then that auction can be seen in the above form and be available for selection. -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thanks for the clarification- before I posted I had the donor form/gifts subform correct, but then got confused with items and gifts. So now I'm able to enter donors and their multiple donated gifts. tblAuctionGifts now contains date donated, value, description, min. bid... so I removed these type of fields from tblAuctionItems since tblAuctionItemContents has CPK to Items and Gifts. I really do have an Auction table tblAuctions AuctionID AuctionDate AuctionName AuctionLocation In tblAuctionItems, I removed AuctionGiftsID as instructed. Okay, then! So now I want to be able to list Items for an Auction as well as create baskets of items for an auction. Once the item is assigned to a basket, I don't want it to be available as a "lone" item (but of course, I may need to unassign it from a basket to either go to a different basket or stand alone). I not really following what to do with tblAuctionItems and tblAuctionItemContents or how to create a form that shows the items available and then create baskets. Not much of a visionary, sadly. Thanks for the guidance, Sal -----Original Message----- Answers inline.... -- Ken Snell MS ACCESS MVP "SillySally" wrote in message ... Thanks- hard to believe I'm still having problems. I have tables: tblAuctionDonors AuctionDonorID (PK) FirstName LastName OK good. tblAuctionGifts (I put the "key" symbol next to both the ID fields which I hope gives me a composite primary key) AuctionGiftsID AuctionDonorID Although this is what I posted in the original structure, I think you can use just AuctionGiftsID as the primary key. No need to have a composite primary key now that I've thought about this a bit more. (And yes, one way you can create a composite primary key is to highlight both fields and the click the Key icon.) You also could add more fields to this table, such as AuctionGiftDateDonated, AuctionGiftDescription, etc. tblAuctionItems (CPK with the next 2 listed IDs) AuctionItemID AuctionID DateReceived ItemValue ShortDescription MinimumBid AuctionGiftsID (I added this myself, think I need it; or maybe I should have added AuctionItemID to AuctionGifts?) Delete the AuctionGiftsID field from the above table. AuctionGiftsID is used in tblAuctionItemContents to relate an item to the gift (s) that compose that item. tblAuctionItemContents (CPK with the next 2 listed IDs) AuctionItemID AuctionGiftsID OK good. You're missing the tblAuctions table, from which you get the value for AuctionID in tblAuctionItems table. Auction Donor Entry (form): Donor listing (name, address). Record source is tblAuctionDonor. So the main form is fine. But I'm having problems with the subform. I tried to create a query like the one you suggested: SELECT tblAuctionGifts.AuctionDonorID, tblAuctionItems.AuctionItemID, tblAuctionItems.ItemValue, tblAuctionItems.AuctionItemName, tblAuctionItems.MinimumBid, tblAuctionGifts.AuctionGiftsID, FROM tblAuctionGifts INNER JOIN tblAuctionItems ON tblAuctionGifts.AuctionGiftsID = tblAuctionItems.AuctionGiftsID; which seems like it has everything I need: it links together DonorID, ItemID, and GiftsID. Yet, it opens "grayed out" so that I can't enter anything into it. I see that given the table setup, I need both Gifts and Items together to be able to link to Donors. I expect this subform to allow me to list, in datasheet form, the items received from each donor. The query that I'd posted is just an example. It will not work for the donor form and gift subform set up. In this case, the subform should use a query similar to this: SELECT * FROM tblAuctionGifts; Then you set the LinkMasterFields and LinkChildFields properties for the subform control (the control on the main form that holds the gifts subform) to link the subform's records to the main form's records. In this case, set both properties to AuctionDonorID. Then, as you select a donor on the main form, the subform will show the records for that donor, including the ability to enter new records. I'd like to get the first form working before I move to the other one. Thanks for your help, Sal snipped for length -----Original Message----- What you would want is five tables (PK = primary key; CPK = composite primary key) tblAuctionDonors (the people who donate gifts) AuctionDonorID (PK) AuctionDonorName tblAuctionGifts (the gifts you get for an auction) AuctionGiftID (CPK) AuctionDonorID (CPK) tblAuctions (the individual auctions) AuctionID AuctionName AuctionDate tblAuctionItems (the items that are available for each auction) AuctionItemID (CPK) AuctionID (CPK) AuctionItemName tblAuctionItemContents (the gifts that are in each item) AuctionItemID (CPK) AuctionGiftID (CPK) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Who owns the copyright on graphic design layouts prepared in MS Wo | Karen | General Discussion | 4 | February 1st, 2005 07:01 AM |
How can I customise an entire design set | David | Publisher | 2 | November 12th, 2004 09:43 AM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
opening a document so it is NOT in design mode | Brad Pears | New Users | 1 | May 3rd, 2004 09:13 PM |