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

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

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


.

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


.

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


.



.

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


.



.

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


.



.



.

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


.



.



.

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


.



.



.



.

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)