Tables and relationships?

First, the overall objective. A few others and I work in a dispatch office
in a homeless shelter. (I will just be upfront about it.) We are
responsible for … scheduling the trucks to pickup donations, keeping track of
pending pickup and the expected items, and keeping track of past donations
incase the IRS questions the legitimacy.

I, for the life of me, cannot figure out the necessary tables and their
relationships. Here is what I’m thinking. There are four trucks, so,
tblTrucks, which has only one field (and an index field), trucks, A, B, C,
and D. There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items,
and a memo box to cover the loose ends, all of these would be the fields.
The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or
for beds, fields like twin, full, queen, king, crib, twin with rails,
headboard, etc. Most of these once on the final ticket, would be simple
integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the
thought is to have these ultimately put on a form as combo boxes. (I hope
this is making sense!) There is a table of map grids. The drivers navigate
via maps grids so this table contains things like street, city, zip, hundred
block, and of course, the map grid, which truck(s) covers that map grid, and
this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems
that the truck picks up, these items obviously are common both truck and
donor, and of course the address is also common to the donor. Which brings
us to the next to the last table, one for the donor, not much left, name,
phone(s), that’s about it, but I was also going to include on this table call
date, pickup date, ticket number, special pickup instructions, and this is
called tblPatrons. Finally the last table, which is a composite of all of
the above, tblDonations, the records of which would printed out the day of
the pickups and serve the driver, who hands it to the patron as a receipt,
which is also stored here for tax purposes. If you’re like me, I’m saying to
myself, “What a sha-bang�, but that is what I’m thinking.

I am open to criticism, suggestions, alterations, or dump the whole thing
and start over. The above is bad enough, but now the relationships. I can
see the relationship between the tblDonations and all the rest of the tables.
That’s easy, and I’m hoping that’s enough. So, envision tblDonations in the
center with four spokes coming of, on for each remaining table. Or, should
it be tblDonations related to truck, related to mapgrid, related to patron,
related to items. Or, does it really matter? I don’t want to sound stupid,
but I really don’t know. I am new; this is my very first at Access.

I think you may also need what I’m shooting for as the final “product�, or
form. I’m hoping for one form with eight tabs, tab1, given the street
address, search for and find the corresponding map grid and truck. This is
already done. Tab2, a place were the final “ticket� is prepared, This would
be temporarily stored until the date of the pickup and would be identical to
the records of tblDonations, and this table is were it would be permanently
stored. (As you can probably see, I am hoping this is done “on the fly�, in
other words, as the donor is on the phone this whole thing is filled out.)
Tab3, simple, today’s schedule, for all 4 trucks, on one form. Each record
printed off the morning of the pickup and given to the driver. Tab4, same
thing except for tomorrow. Tab5-8, the schedule for each truck as far into
the future as I can get. These may be datasheet, something I can scroll from
top to bottom.

So, there’s were I’m at. If anyone could comment on the tables, their
relationships, or just the overall structure it would be appreciated.

Thanks

I will try next time not to be so wordy, it's just that I need to remove
this from my head and put it into yours, as accurately as possible.
--
awsmitty

On Sat, 16 Jan 2010 20:03:01 -0800, awsmitty
wrote:

An ambitious project for a newbie.

I will focus on one aspect. You write:
"There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38
items, and a memo box to cover the loose ends, all of these would be
the fields."
So I think you want to create:
tblItems
Bed
Sofa
Lamp
BagOfClothes
....etc...
LooseEnds

That is a REALLY BAD idea, for many reasons. In a relational database
you typically have more rows and fewer columns:
tblItems
ItemID autonumber PK
ItemName text required uniqueindex
This is the stock list of your 38 items. Or if next month we want to
add an item, simply add one more row to this table and EVERYTHING else
stays the same.

tblDonations
DonationID autonumber PK
DonationDate datetime, required
DonorID (link to Donors table)

This sets up a classic M:M (many-to-many) relationship: each item is
in many donations, and each donation has many items. This is resolved
with a third table:
tblItemsInDonations
ItemID long int PK
DonationID long int PK
Quantity single required

Then in the Relationships window you create the two 1:M relationships
between tblItems.ItemID and tblItemsInDonations.ItemID as well as
tblDonations.DonationID and tblItemsInDonations.DonationID, and you
enforce the relationships.

Now you have a structure you can build upon. For example you can
create a form in formview with recordsource based on tblDonations, and
a subform (set LinkMasterFields and LinkChildFields both to
DonationID) based on tblItemsInDonations with a hidden DonationID, a
dropdown based on tblItems that is bound to the ItemID, and a
textfield for Quantity.

-Tom.
Microsoft Access MVP


First, the overall objective. A few others and I work in a dispatch office
in a homeless shelter. (I will just be upfront about it.) We are
responsible for … scheduling the trucks to pickup donations, keeping track of
pending pickup and the expected items, and keeping track of past donations
incase the IRS questions the legitimacy.

I, for the life of me, cannot figure out the necessary tables and their
relationships. Here is what I’m thinking. There are four trucks, so,
tblTrucks, which has only one field (and an index field), trucks, A, B, C,
and D. There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items,
and a memo box to cover the loose ends, all of these would be the fields.
The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or
for beds, fields like twin, full, queen, king, crib, twin with rails,
headboard, etc. Most of these once on the final ticket, would be simple
integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the
thought is to have these ultimately put on a form as combo boxes. (I hope
this is making sense!) There is a table of map grids. The drivers navigate
via maps grids so this table contains things like street, city, zip, hundred
block, and of course, the map grid, which truck(s) covers that map grid, and
this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems
that the truck picks up, these items obviously are common both truck and
donor, and of course the address is also common to the donor. Which brings
us to the next to the last table, one for the donor, not much left, name,
phone(s), that’s about it, but I was also going to include on this table call
date, pickup date, ticket number, special pickup instructions, and this is
called tblPatrons. Finally the last table, which is a composite of all of
the above, tblDonations, the records of which would printed out the day of
the pickups and serve the driver, who hands it to the patron as a receipt,
which is also stored here for tax purposes. If you’re like me, I’m saying to
myself, “What a sha-bang”, but that is what I’m thinking.

I am open to criticism, suggestions, alterations, or dump the whole thing
and start over. The above is bad enough, but now the relationships. I can
see the relationship between the tblDonations and all the rest of the tables.
That’s easy, and I’m hoping that’s enough. So, envision tblDonations in the
center with four spokes coming of, on for each remaining table. Or, should
it be tblDonations related to truck, related to mapgrid, related to patron,
related to items. Or, does it really matter? I don’t want to sound stupid,
but I really don’t know. I am new; this is my very first at Access.

I think you may also need what I’m shooting for as the final “product”, or
form. I’m hoping for one form with eight tabs, tab1, given the street
address, search for and find the corresponding map grid and truck. This is
already done. Tab2, a place were the final “ticket” is prepared, This would
be temporarily stored until the date of the pickup and would be identical to
the records of tblDonations, and this table is were it would be permanently
stored. (As you can probably see, I am hoping this is done “on the fly”, in
other words, as the donor is on the phone this whole thing is filled out.)
Tab3, simple, today’s schedule, for all 4 trucks, on one form. Each record
printed off the morning of the pickup and given to the driver. Tab4, same
thing except for tomorrow. Tab5-8, the schedule for each truck as far into
the future as I can get. These may be datasheet, something I can scroll from
top to bottom.

So, there’s were I’m at. If anyone could comment on the tables, their
relationships, or just the overall structure it would be appreciated.

Thanks

I will try next time not to be so wordy, it's just that I need to remove
this from my head and put it into yours, as accurately as possible.

--
awsmitty


"Tom van Stiphout" wrote:

On Sat, 16 Jan 2010 20:03:01 -0800, awsmitty
wrote:

An ambitious project for a newbie.

I will focus on one aspect. You write:
"There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38
items, and a memo box to cover the loose ends, all of these would be
the fields."
So I think you want to create:
tblItems
Bed
Sofa
Lamp
BagOfClothes
....etc...
LooseEnds

That is a REALLY BAD idea, for many reasons. In a relational database
you typically have more rows and fewer columns:
tblItems
ItemID autonumber PK
ItemName text required uniqueindex
This is the stock list of your 38 items. Or if next month we want to
add an item, simply add one more row to this table and EVERYTHING else
stays the same.

tblDonations
DonationID autonumber PK
DonationDate datetime, required
DonorID (link to Donors table)

This sets up a classic M:M (many-to-many) relationship: each item is
in many donations, and each donation has many items. This is resolved
with a third table:
tblItemsInDonations
ItemID long int PK
DonationID long int PK
Quantity single required

Then in the Relationships window you create the two 1:M relationships
between tblItems.ItemID and tblItemsInDonations.ItemID as well as
tblDonations.DonationID and tblItemsInDonations.DonationID, and you
enforce the relationships.

Now you have a structure you can build upon. For example you can
create a form in formview with recordsource based on tblDonations, and
a subform (set LinkMasterFields and LinkChildFields both to
DonationID) based on tblItemsInDonations with a hidden DonationID, a
dropdown based on tblItems that is bound to the ItemID, and a
textfield for Quantity.

-Tom.
Microsoft Access MVP


First, the overall objective. A few others and I work in a dispatch office
in a homeless shelter. (I will just be upfront about it.) We are
responsible for … scheduling the trucks to pickup donations, keeping track of
pending pickup and the expected items, and keeping track of past donations
incase the IRS questions the legitimacy.

I, for the life of me, cannot figure out the necessary tables and their
relationships. Here is what I’m thinking. There are four trucks, so,
tblTrucks, which has only one field (and an index field), trucks, A, B, C,
and D. There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items,
and a memo box to cover the loose ends, all of these would be the fields.
The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or
for beds, fields like twin, full, queen, king, crib, twin with rails,
headboard, etc. Most of these once on the final ticket, would be simple
integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the
thought is to have these ultimately put on a form as combo boxes. (I hope
this is making sense!) There is a table of map grids. The drivers navigate
via maps grids so this table contains things like street, city, zip, hundred
block, and of course, the map grid, which truck(s) covers that map grid, and
this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems
that the truck picks up, these items obviously are common both truck and
donor, and of course the address is also common to the donor. Which brings
us to the next to the last table, one for the donor, not much left, name,
phone(s), that’s about it, but I was also going to include on this table call
date, pickup date, ticket number, special pickup instructions, and this is
called tblPatrons. Finally the last table, which is a composite of all of
the above, tblDonations, the records of which would printed out the day of
the pickups and serve the driver, who hands it to the patron as a receipt,
which is also stored here for tax purposes. If you’re like me, I’m saying to
myself, “What a sha-bang�, but that is what I’m thinking.

I am open to criticism, suggestions, alterations, or dump the whole thing
and start over. The above is bad enough, but now the relationships. I can
see the relationship between the tblDonations and all the rest of the tables.
That’s easy, and I’m hoping that’s enough. So, envision tblDonations in the
center with four spokes coming of, on for each remaining table. Or, should
it be tblDonations related to truck, related to mapgrid, related to patron,
related to items. Or, does it really matter? I don’t want to sound stupid,
but I really don’t know. I am new; this is my very first at Access.

I think you may also need what I’m shooting for as the final “product�, or
form. I’m hoping for one form with eight tabs, tab1, given the street
address, search for and find the corresponding map grid and truck. This is
already done. Tab2, a place were the final “ticket� is prepared, This would
be temporarily stored until the date of the pickup and would be identical to
the records of tblDonations, and this table is were it would be permanently
stored. (As you can probably see, I am hoping this is done “on the fly�, in
other words, as the donor is on the phone this whole thing is filled out.)
Tab3, simple, today’s schedule, for all 4 trucks, on one form. Each record
printed off the morning of the pickup and given to the driver. Tab4, same
thing except for tomorrow. Tab5-8, the schedule for each truck as far into
the future as I can get. These may be datasheet, something I can scroll from
top to bottom.

So, there’s were I’m at. If anyone could comment on the tables, their
relationships, or just the overall structure it would be appreciated.

Thanks

I will try next time not to be so wordy, it's just that I need to remove
this from my head and put it into yours, as accurately as possible.
.

Tom,

Many thanks. Yes, I would agree, this is a bit ambitious for a newbie. I
am up to my neck, but I really think it has to be done. If I leave here and
it doesn’t get done, it will be a long time before someone else approaches
the issue. The current method is slow, awkward, very error prone, and just
plain sloppy. For example, we have a closet full of carbon copies of past
receipts that we must keep. When’s that last time you’ve even seen a carbon
copy, much less used one? I could go on and on.

I see what you’re doing with the items table. I figured that 38+ fields
with a dozen rows at most was a bad idea, but I couldn’t figure out any other
way. What drew me towards this were things like the beds. Using your
example, I guess I could also have a table linked to beds, which contains all
the possibilities, twin, full, qn, etc, along with mattress, box spring,
rails, headboard, etc. Sometimes we get it all, sometimes we only get one
piece.

Continuing with your example, I will try to set this up, massage it as best
I can, and let you know the progress. I already know part of what’s going to
happen. I am going to make one form, throw it out, make another, throw it
out, and continue this way until the end result is a db that is very simple
to use. There is still much for me to learn. I got the search routine to
work. But filling out the rest of the tabs as described in the posting
above, in such a manner that it is as user friendly as possible, is still
somewhat unresolved. That’s another reason why all the items, etc. When the
smoke clears, this really does need to be very simple to use.

Thanks again



--
awsmitty


"Tom van Stiphout" wrote:

On Sat, 16 Jan 2010 20:03:01 -0800, awsmitty
wrote:

An ambitious project for a newbie.

I will focus on one aspect. You write:
"There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38
items, and a memo box to cover the loose ends, all of these would be
the fields."
So I think you want to create:
tblItems
Bed
Sofa
Lamp
BagOfClothes
....etc...
LooseEnds

That is a REALLY BAD idea, for many reasons. In a relational database
you typically have more rows and fewer columns:
tblItems
ItemID autonumber PK
ItemName text required uniqueindex
This is the stock list of your 38 items. Or if next month we want to
add an item, simply add one more row to this table and EVERYTHING else
stays the same.

tblDonations
DonationID autonumber PK
DonationDate datetime, required
DonorID (link to Donors table)

This sets up a classic M:M (many-to-many) relationship: each item is
in many donations, and each donation has many items. This is resolved
with a third table:
tblItemsInDonations
ItemID long int PK
DonationID long int PK
Quantity single required

Then in the Relationships window you create the two 1:M relationships
between tblItems.ItemID and tblItemsInDonations.ItemID as well as
tblDonations.DonationID and tblItemsInDonations.DonationID, and you
enforce the relationships.

Now you have a structure you can build upon. For example you can
create a form in formview with recordsource based on tblDonations, and
a subform (set LinkMasterFields and LinkChildFields both to
DonationID) based on tblItemsInDonations with a hidden DonationID, a
dropdown based on tblItems that is bound to the ItemID, and a
textfield for Quantity.

-Tom.
Microsoft Access MVP


First, the overall objective. A few others and I work in a dispatch office
in a homeless shelter. (I will just be upfront about it.) We are
responsible for … scheduling the trucks to pickup donations, keeping track of
pending pickup and the expected items, and keeping track of past donations
incase the IRS questions the legitimacy.

I, for the life of me, cannot figure out the necessary tables and their
relationships. Here is what I’m thinking. There are four trucks, so,
tblTrucks, which has only one field (and an index field), trucks, A, B, C,
and D. There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items,
and a memo box to cover the loose ends, all of these would be the fields.
The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or
for beds, fields like twin, full, queen, king, crib, twin with rails,
headboard, etc. Most of these once on the final ticket, would be simple
integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the
thought is to have these ultimately put on a form as combo boxes. (I hope
this is making sense!) There is a table of map grids. The drivers navigate
via maps grids so this table contains things like street, city, zip, hundred
block, and of course, the map grid, which truck(s) covers that map grid, and
this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems
that the truck picks up, these items obviously are common both truck and
donor, and of course the address is also common to the donor. Which brings
us to the next to the last table, one for the donor, not much left, name,
phone(s), that’s about it, but I was also going to include on this table call
date, pickup date, ticket number, special pickup instructions, and this is
called tblPatrons. Finally the last table, which is a composite of all of
the above, tblDonations, the records of which would printed out the day of
the pickups and serve the driver, who hands it to the patron as a receipt,
which is also stored here for tax purposes. If you’re like me, I’m saying to
myself, “What a sha-bang�, but that is what I’m thinking.

I am open to criticism, suggestions, alterations, or dump the whole thing
and start over. The above is bad enough, but now the relationships. I can
see the relationship between the tblDonations and all the rest of the tables.
That’s easy, and I’m hoping that’s enough. So, envision tblDonations in the
center with four spokes coming of, on for each remaining table. Or, should
it be tblDonations related to truck, related to mapgrid, related to patron,
related to items. Or, does it really matter? I don’t want to sound stupid,
but I really don’t know. I am new; this is my very first at Access.

I think you may also need what I’m shooting for as the final “product�, or
form. I’m hoping for one form with eight tabs, tab1, given the street
address, search for and find the corresponding map grid and truck. This is
already done. Tab2, a place were the final “ticket� is prepared, This would
be temporarily stored until the date of the pickup and would be identical to
the records of tblDonations, and this table is were it would be permanently
stored. (As you can probably see, I am hoping this is done “on the fly�, in
other words, as the donor is on the phone this whole thing is filled out.)
Tab3, simple, today’s schedule, for all 4 trucks, on one form. Each record
printed off the morning of the pickup and given to the driver. Tab4, same
thing except for tomorrow. Tab5-8, the schedule for each truck as far into
the future as I can get. These may be datasheet, something I can scroll from
top to bottom.

So, there’s were I’m at. If anyone could comment on the tables, their
relationships, or just the overall structure it would be appreciated.

Thanks

I will try next time not to be so wordy, it's just that I need to remove
this from my head and put it into yours, as accurately as possible.
.

On Sun, 17 Jan 2010 13:15:01 -0800, awsmitty
wrote:

I see what you’re doing with the items table. I figured that 38+ fields
with a dozen rows at most was a bad idea, but I couldn’t figure out any other
way. What drew me towards this were things like the beds. Using your
example, I guess I could also have a table linked to beds, which contains all
the possibilities, twin, full, qn, etc, along with mattress, box spring,
rails, headboard, etc. Sometimes we get it all, sometimes we only get one
piece.

Well, I'd suggest that you just store a record of what you actually got.
You're not going to take apart a single bed and send the box spring to one
place and the headboard to another, are you??? If you could just search for
headboards (as items), and for beds (as items), would that meet your needs?

I suppose if you were given just a headboard and frame you could enter that as
two items; but I'd be inclined to enter a complete bed just as a bed. If
circumstances required you could extract the components later!
--

John W. Vinson [MVP]

Tom.
At the end of the forth paragraph, you mention"quantity single required". I
have to ask, what do you mean?
--
awsmitty


"Tom van Stiphout" wrote:

On Sat, 16 Jan 2010 20:03:01 -0800, awsmitty
wrote:

An ambitious project for a newbie.

I will focus on one aspect. You write:
"There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38
items, and a memo box to cover the loose ends, all of these would be
the fields."
So I think you want to create:
tblItems
Bed
Sofa
Lamp
BagOfClothes
....etc...
LooseEnds

That is a REALLY BAD idea, for many reasons. In a relational database
you typically have more rows and fewer columns:
tblItems
ItemID autonumber PK
ItemName text required uniqueindex
This is the stock list of your 38 items. Or if next month we want to
add an item, simply add one more row to this table and EVERYTHING else
stays the same.

tblDonations
DonationID autonumber PK
DonationDate datetime, required
DonorID (link to Donors table)

This sets up a classic M:M (many-to-many) relationship: each item is
in many donations, and each donation has many items. This is resolved
with a third table:
tblItemsInDonations
ItemID long int PK
DonationID long int PK
Quantity single required

Then in the Relationships window you create the two 1:M relationships
between tblItems.ItemID and tblItemsInDonations.ItemID as well as
tblDonations.DonationID and tblItemsInDonations.DonationID, and you
enforce the relationships.

Now you have a structure you can build upon. For example you can
create a form in formview with recordsource based on tblDonations, and
a subform (set LinkMasterFields and LinkChildFields both to
DonationID) based on tblItemsInDonations with a hidden DonationID, a
dropdown based on tblItems that is bound to the ItemID, and a
textfield for Quantity.

-Tom.
Microsoft Access MVP


First, the overall objective. A few others and I work in a dispatch office
in a homeless shelter. (I will just be upfront about it.) We are
responsible for … scheduling the trucks to pickup donations, keeping track of
pending pickup and the expected items, and keeping track of past donations
incase the IRS questions the legitimacy.

I, for the life of me, cannot figure out the necessary tables and their
relationships. Here is what I’m thinking. There are four trucks, so,
tblTrucks, which has only one field (and an index field), trucks, A, B, C,
and D. There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items,
and a memo box to cover the loose ends, all of these would be the fields.
The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or
for beds, fields like twin, full, queen, king, crib, twin with rails,
headboard, etc. Most of these once on the final ticket, would be simple
integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the
thought is to have these ultimately put on a form as combo boxes. (I hope
this is making sense!) There is a table of map grids. The drivers navigate
via maps grids so this table contains things like street, city, zip, hundred
block, and of course, the map grid, which truck(s) covers that map grid, and
this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems
that the truck picks up, these items obviously are common both truck and
donor, and of course the address is also common to the donor. Which brings
us to the next to the last table, one for the donor, not much left, name,
phone(s), that’s about it, but I was also going to include on this table call
date, pickup date, ticket number, special pickup instructions, and this is
called tblPatrons. Finally the last table, which is a composite of all of
the above, tblDonations, the records of which would printed out the day of
the pickups and serve the driver, who hands it to the patron as a receipt,
which is also stored here for tax purposes. If you’re like me, I’m saying to
myself, “What a sha-bang�, but that is what I’m thinking.

I am open to criticism, suggestions, alterations, or dump the whole thing
and start over. The above is bad enough, but now the relationships. I can
see the relationship between the tblDonations and all the rest of the tables.
That’s easy, and I’m hoping that’s enough. So, envision tblDonations in the
center with four spokes coming of, on for each remaining table. Or, should
it be tblDonations related to truck, related to mapgrid, related to patron,
related to items. Or, does it really matter? I don’t want to sound stupid,
but I really don’t know. I am new; this is my very first at Access.

I think you may also need what I’m shooting for as the final “product�, or
form. I’m hoping for one form with eight tabs, tab1, given the street
address, search for and find the corresponding map grid and truck. This is
already done. Tab2, a place were the final “ticket� is prepared, This would
be temporarily stored until the date of the pickup and would be identical to
the records of tblDonations, and this table is were it would be permanently
stored. (As you can probably see, I am hoping this is done “on the fly�, in
other words, as the donor is on the phone this whole thing is filled out.)
Tab3, simple, today’s schedule, for all 4 trucks, on one form. Each record
printed off the morning of the pickup and given to the driver. Tab4, same
thing except for tomorrow. Tab5-8, the schedule for each truck as far into
the future as I can get. These may be datasheet, something I can scroll from
top to bottom.

So, there’s were I’m at. If anyone could comment on the tables, their
relationships, or just the overall structure it would be appreciated.

Thanks

I will try next time not to be so wordy, it's just that I need to remove
this from my head and put it into yours, as accurately as possible.
.

On Sun, 17 Jan 2010 13:39:01 -0800, awsmitty
wrote:

I mean that this table will have a field named Quantity, which is of
datatype Single, and it is a required field.
This will allow you to record how many items of a particular kind
there were in one particular donation. I first thought to use Integer
for the data type, but what if someone donated $12.50? Maybe your item
is "money", and your quantity is 12.5.

-Tom.
Microsoft Access MVP



Tom.
At the end of the forth paragraph, you mention"quantity single required". I
have to ask, what do you mean?

Tom

As an alternative to Single, do you find it useful to use the "Currency"
data type?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Tom van Stiphout" wrote in message
...
On Sun, 17 Jan 2010 13:39:01 -0800, awsmitty
wrote:

I mean that this table will have a field named Quantity, which is of
datatype Single, and it is a required field.
This will allow you to record how many items of a particular kind
there were in one particular donation. I first thought to use Integer
for the data type, but what if someone donated $12.50? Maybe your item
is "money", and your quantity is 12.5.

-Tom.
Microsoft Access MVP



Tom.
At the end of the forth paragraph, you mention"quantity single required".
I
have to ask, what do you mean?

On Tue, 19 Jan 2010 09:10:35 -0800, "Jeff Boyce"
wrote:

Usually yes, I am a big proponent of using the smallest and most
accurate data type. But I think this is a justifyable exception: we
want only a single Quantity field, and it can hold the number of beds
donated, or gallons of drinking water, or yards of linnen, etc.

-Tom.
Microsoft Access MVP


Tom

As an alternative to Single, do you find it useful to use the "Currency"
data type?

Regards

Jeff Boyce
Microsoft Access MVP

John,

The items are just what we pick up, not deliver; the list is only to give
the drivers and the dispatchers on idea of how big the loads are getting for
that day. These trucks do 6-10 pickups per day. It turns out to be several
loads per day, per truck. I don't think I would ever be searching the items,
maybe, but I'm not planning on it. The only searching is to get the grid
from the address. So, yes, I'm wondering if these other tables tblItems,
etc, all of them really, need to be linked. I mentioned this in my first
post. I really truly don't know. The way I see it, and I could be wrong,
what’s needed is some temporary storage place, temporary – from the time the
donation is called in to the time we pick it up, could be a few days, might
be several months (honestly, some churches schedule months in advance to make
sure we are there). Once the ticket is printed, at that point the whole
ticket with all of its information, goes to some table to be stored
indefinitely (I hear 7 years, for IRS purposes, but I really don’t know).
Now that table may need to be searchable. John makes a donation, files his
taxes, but two years later the IRS comes along and questions it. In the
meantime John has lost his receipt. John, or for that matter the IRS might
come to us to verify that John donated all this stuff. I can find John
easily enough, just give me his address and I’ll use the same routine as when
I found him the first time. I assume what Tom was trying to do, and what I’m
interested in doing just as an educational exersize, is to manage the data
efficiently and conveniently, and learn a little along the way. I assume
Tom’s method would be more efficient. Convenient, well, for you guys that
know this forwards and backward, maybe so, but for me, I have to really keep
my eye on the ball or I’ll loose track of what’s gong on. But, it could
prove to be interesting.


--
awsmitty


"awsmitty" wrote:

First, the overall objective. A few others and I work in a dispatch office
in a homeless shelter. (I will just be upfront about it.) We are
responsible for … scheduling the trucks to pickup donations, keeping track of
pending pickup and the expected items, and keeping track of past donations
incase the IRS questions the legitimacy.

I, for the life of me, cannot figure out the necessary tables and their
relationships. Here is what I’m thinking. There are four trucks, so,
tblTrucks, which has only one field (and an index field), trucks, A, B, C,
and D. There needs to be a table which lists the items, and there are many,
beds, sofas, lamps, bags of clothes, lawn mowers, bikes, … I have 38 items,
and a memo box to cover the loose ends, all of these would be the fields.
The records would be, for example, 1-3 bags of clothes or 6-9, 9-12, 12+, or
for beds, fields like twin, full, queen, king, crib, twin with rails,
headboard, etc. Most of these once on the final ticket, would be simple
integers, 1 sofa, 3 lamps, so most of the fields would be 1,2,3, … and the
thought is to have these ultimately put on a form as combo boxes. (I hope
this is making sense!) There is a table of map grids. The drivers navigate
via maps grids so this table contains things like street, city, zip, hundred
block, and of course, the map grid, which truck(s) covers that map grid, and
this is called tblMapGrid. So, thus far, a tblMapGrid, tblTrucks, tblItems
that the truck picks up, these items obviously are common both truck and
donor, and of course the address is also common to the donor. Which brings
us to the next to the last table, one for the donor, not much left, name,
phone(s), that’s about it, but I was also going to include on this table call
date, pickup date, ticket number, special pickup instructions, and this is
called tblPatrons. Finally the last table, which is a composite of all of
the above, tblDonations, the records of which would printed out the day of
the pickups and serve the driver, who hands it to the patron as a receipt,
which is also stored here for tax purposes. If you’re like me, I’m saying to
myself, “What a sha-bang�, but that is what I’m thinking.

I am open to criticism, suggestions, alterations, or dump the whole thing
and start over. The above is bad enough, but now the relationships. I can
see the relationship between the tblDonations and all the rest of the tables.
That’s easy, and I’m hoping that’s enough. So, envision tblDonations in the
center with four spokes coming of, on for each remaining table. Or, should
it be tblDonations related to truck, related to mapgrid, related to patron,
related to items. Or, does it really matter? I don’t want to sound stupid,
but I really don’t know. I am new; this is my very first at Access.

I think you may also need what I’m shooting for as the final “product�, or
form. I’m hoping for one form with eight tabs, tab1, given the street
address, search for and find the corresponding map grid and truck. This is
already done. Tab2, a place were the final “ticket� is prepared, This would
be temporarily stored until the date of the pickup and would be identical to
the records of tblDonations, and this table is were it would be permanently
stored. (As you can probably see, I am hoping this is done “on the fly�, in
other words, as the donor is on the phone this whole thing is filled out.)
Tab3, simple, today’s schedule, for all 4 trucks, on one form. Each record
printed off the morning of the pickup and given to the driver. Tab4, same
thing except for tomorrow. Tab5-8, the schedule for each truck as far into
the future as I can get. These may be datasheet, something I can scroll from
top to bottom.

So, there’s were I’m at. If anyone could comment on the tables, their
relationships, or just the overall structure it would be appreciated.

Thanks

I will try next time not to be so wordy, it's just that I need to remove
this from my head and put it into yours, as accurately as possible.
--
awsmitty