In my archive database I have to have the physical location of storage boxes.
These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and Unit
a, b, c. When I start this database should I have Section, Row and Unit as
separate fields, or should I have one field for example A2b. The ultimate
result is to know what is in each space but also to identify which spaces are
empty for future filling. Don't want to start out on the wrong track.
Thanks.

Linda

If you stuff all those (Section, Row, Unit) together in one field, you will
be violating the design principal of "one fact, one field". From a
practical standpoint, so what?!

If you will NEVER need to be able to sort or select by, say, Row or Unit,
you probably don't need separate fields.

Were it mine, I WOULD use separate fields, probably even if I couldn't
imagine a need to do the sort/select.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"LindaC" <(E-Mail Removed)> wrote in message
newsC5547E3-0F1A-4100-8A7C-(E-Mail Removed)...
> In my archive database I have to have the physical location of storage
> boxes.
> These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and
> Unit
> a, b, c. When I start this database should I have Section, Row and Unit
> as
> separate fields, or should I have one field for example A2b. The ultimate
> result is to know what is in each space but also to identify which spaces
> are
> empty for future filling. Don't want to start out on the wrong track.
> Thanks.

Separate fields, for sure. But think carefully about whether this
3-level scheme will survive for the life of the database: might it in a
few years time need to deal with Stores as well as Sections, and
sub-units as well as Units?

On Wed, 21 Feb 2007 09:05:10 -0800, LindaC
<(E-Mail Removed)> wrote:

>In my archive database I have to have the physical location of storage boxes.
> These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and Unit
>a, b, c. When I start this database should I have Section, Row and Unit as
>separate fields, or should I have one field for example A2b. The ultimate
>result is to know what is in each space but also to identify which spaces are
>empty for future filling. Don't want to start out on the wrong track.
>Thanks.

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Thank you. Regarding the second part of the question, when I use the 3
fields, how will I be able to identify space that is left in the warehouse.
Thanks.
"John Nurick" wrote:

> Separate fields, for sure. But think carefully about whether this
> 3-level scheme will survive for the life of the database: might it in a
> few years time need to deal with Stores as well as Sections, and
> sub-units as well as Units?
>
> On Wed, 21 Feb 2007 09:05:10 -0800, LindaC
> <(E-Mail Removed)> wrote:
>
> >In my archive database I have to have the physical location of storage boxes.
> > These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and Unit
> >a, b, c. When I start this database should I have Section, Row and Unit as
> >separate fields, or should I have one field for example A2b. The ultimate
> >result is to know what is in each space but also to identify which spaces are
> >empty for future filling. Don't want to start out on the wrong track.
> >Thanks.
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newsgroup and not by email.
>

This is a bit more complicated than it might first appear, unless I'm
missing a step. It would seem to me that a section (A, for example) can only
be empty if all rows and units in that section are also empty.

And by the same token, a row can only be empty if all units in it are also
empty. Is that not correct?

So, when you want to identify "space" left in the warehouse, you'll need to
be more precise about what "empty space" means.


--
George Hepworth
2007 Access MVP

"LindaC" <(E-Mail Removed)> wrote in message
news:B641171C-EB33-42B7-A308-(E-Mail Removed)...
> Thank you. Regarding the second part of the question, when I use the 3
> fields, how will I be able to identify space that is left in the
> warehouse.
> Thanks.
> "John Nurick" wrote:
>
>> Separate fields, for sure. But think carefully about whether this
>> 3-level scheme will survive for the life of the database: might it in a
>> few years time need to deal with Stores as well as Sections, and
>> sub-units as well as Units?
>>
>> On Wed, 21 Feb 2007 09:05:10 -0800, LindaC
>> <(E-Mail Removed)> wrote:
>>
>> >In my archive database I have to have the physical location of storage
>> >boxes.
>> > These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and
>> > Unit
>> >a, b, c. When I start this database should I have Section, Row and Unit
>> >as
>> >separate fields, or should I have one field for example A2b. The
>> >ultimate
>> >result is to know what is in each space but also to identify which
>> >spaces are
>> >empty for future filling. Don't want to start out on the wrong track.
>> >Thanks.
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newsgroup and not by email.
>>

You'll need to store information on all the spaces that exist; with that
and the three fields in each box's record you can use a query to
identify empty locations (i.e. locations that don't appear in any box's
record).

How best to do this depends on how your storage is set up now and how it
might change during the life of the database.

For example, if all your Sections will always have the same number of
Rows, and all your Rows will always have the same number of Units, you
could use three small tables, each with just one field (which, of
course, is the primary key):

tblSections
SectionID* (text): if you have five Sections A .. E then
tblSections will have five records 'A' .. 'E'.

tblRows
RowID* (Number - Long): if every section has 8 rows, then
tblRows will have 8 records 1 .. 8.

And so on for tblUnits.

With those three tables, a query like this will produce a list of all
possible locations:
SELECT SectionID, RowID, UnitID
FROM tblSections, tblRows, tblUnits

This can be used in a "find unmatched" query (or a "frustrated outer
join") to get a list of empty locations. (

It's also possible - as George suggested - to use variants of these
queries to identify entire rows or sections that are empty.

....

If your storage is not uniformly configured - e.g. if some sections may
have 8 rows and others 10 - then the three very simple tables above
won't wash. There's a choice between using a more complex structure in
which tblSections includes a NumberOfRows field, or creating a single
table that contains one record for every location. Either way, the idea
is to store (or generate from stored data) a list of all existing
locations and compare it with the locations in use.


On Thu, 22 Feb 2007 04:54:00 -0800, LindaC
<(E-Mail Removed)> wrote:

>Thank you. Regarding the second part of the question, when I use the 3
>fields, how will I be able to identify space that is left in the warehouse.
>Thanks.
>"John Nurick" wrote:
>
>> Separate fields, for sure. But think carefully about whether this
>> 3-level scheme will survive for the life of the database: might it in a
>> few years time need to deal with Stores as well as Sections, and
>> sub-units as well as Units?
>>
>> On Wed, 21 Feb 2007 09:05:10 -0800, LindaC
>> <(E-Mail Removed)> wrote:
>>
>> >In my archive database I have to have the physical location of storage boxes.
>> > These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and Unit
>> >a, b, c. When I start this database should I have Section, Row and Unit as
>> >separate fields, or should I have one field for example A2b. The ultimate
>> >result is to know what is in each space but also to identify which spaces are
>> >empty for future filling. Don't want to start out on the wrong track.
>> >Thanks.
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newsgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

I have a very simular issue. I appreciate all the information in the thread
so far - but I am not sure how I can do what I need to do. As an example, We
store items (books, forms, paper, etc.) for other offices of our company. We
might receive 80.000 items in 264 boxes (sometimes 60 per skid). As we put
these items into our shelves - it might take up several spots (and sometimes
there are more than one thing in a spot if there were few enough items).
Then throughout the month (or years), they will ask for how many are left,
and we need to count them (as well as know all the locations where they are
located in the warehouse). If it is possible, I would appreciate a copy of
any template anyone might have to help me or a copy of your database if you
are willing (you can blank out the forms if you'd like of your actual data).

"LindaC" wrote:

> In my archive database I have to have the physical location of storage boxes.
> These will be identified as Section A, B, C, etc. Row 1, 2, 3, etc and Unit
> a, b, c. When I start this database should I have Section, Row and Unit as
> separate fields, or should I have one field for example A2b. The ultimate
> result is to know what is in each space but also to identify which spaces are
> empty for future filling. Don't want to start out on the wrong track.
> Thanks.