Table design for archive boxes

[Seth]

I am developing a database to record which archive box a particular report is
stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box 1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just 2000
without any compartments.

When the user enters a new archive box into the database I want them to
enter the BoxNumber, NumberOfCompartments, and StorageLocation. My database
will automatically generate 1000A, 1000B, 1000C, and 1000D from BoxNumber and
NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn’t look quite
right to me.

Any suggestions appreciated. After I’ve designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth

 

[tina]

if we presume the following:

the number of compartments in a single archive box does not change over
time. (if Box1000 has 4 compartments today, it will always have 4
compartments, period.)
the compartments are always within the box, so wherever the box is located,
that's where the compartments of that box will be found. (if Box1000 is at
location X, then BoxCompartment 1000B is by definition at location X - there
is no way it can be at location Y.)
you only want to know where a specific archive box is located *right now*,
not a history of where the box may have been located in the past (and then
moved from).
you only want to know where a specific report is stored *right now*, not a
history of where the report may have stored in the past (and then moved
from).

given the preceding assumptions, the following tables should work, as

tblLocations
LocationID (pk)
LocationDescription

tblArchiveBoxes
BoxID (pk)
LocationID (foreign key from tblLocations)

tblBoxCompartments
CompartmentID (pk)
BoxID (foreign key from tblArchiveBoxes)
CompartmentCode (A, B, C, D, whatever)
(a box without compartments would still have one record in this table:
CompartmentCode A, since the whole box can be considered to have a single
compartment.)

tblReports
ReportID (primary key)
ReportName
ReportDescription
CompartmentID (foreign key from tblBoxCompartments)

tblLocations has a one:many relationship with tblArchiveBoxes, linked on
field LocationID. (one location may store many boxes, but each box is stored
at only one location at any single point in time.)
tblArchiveBoxes has a one:many relationship with tblBoxCompartments, linked
on field BoxID. (one box may have many compartments, but each compartment is
in only one box.)
tblBoxCompartments has a one:many relationship with tblReports, linked on
field CompartmentID. (one box compartment may house many reports, but each
report is located in only one compartment.)

so any given report will be found in a specific box compartment, which is
linked to a specific archive box, which is assigned to a specific storage
location. correct?

hth

ps. setting up data entry to automatically create the BoxCompartment
records, is a separate issue. you need to get the tables/relationships right
first, then we'll tackle data entry / user interface issues.

I am developing a database to record which archive box a particular report is
stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box 1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just 2000
without any compartments.

When the user enters a new archive box into the database I want them to
enter the BoxNumber, NumberOfCompartments, and StorageLocation. My database
will automatically generate 1000A, 1000B, 1000C, and 1000D from BoxNumber and
NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn't look quite
right to me.

Any suggestions appreciated. After I've designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth

 

[Seth]

Thanks again Tina! Makes perfect sense. All your assumptions were correct.
I’ve set up tables and relationships as you suggested and it’s looking good.
Now it’s onto the forms. I’ve already done a bit of work on the main form,
just have to add the interface for the archive boxes.

Do you have any tips on automatically creating BoxCompartment records?

Seth

if we presume the following:

the number of compartments in a single archive box does not change over
time. (if Box1000 has 4 compartments today, it will always have 4
compartments, period.)
the compartments are always within the box, so wherever the box is located,
that's where the compartments of that box will be found. (if Box1000 is at
location X, then BoxCompartment 1000B is by definition at location X - there
is no way it can be at location Y.)
you only want to know where a specific archive box is located *right now*,
not a history of where the box may have been located in the past (and then
moved from).
you only want to know where a specific report is stored *right now*, not a
history of where the report may have stored in the past (and then moved
from).

given the preceding assumptions, the following tables should work, as

tblLocations
LocationID (pk)
LocationDescription

tblArchiveBoxes
BoxID (pk)
LocationID (foreign key from tblLocations)

tblBoxCompartments
CompartmentID (pk)
BoxID (foreign key from tblArchiveBoxes)
CompartmentCode (A, B, C, D, whatever)
(a box without compartments would still have one record in this table:
CompartmentCode A, since the whole box can be considered to have a single
compartment.)

tblReports
ReportID (primary key)
ReportName
ReportDescription
CompartmentID (foreign key from tblBoxCompartments)

tblLocations has a one:many relationship with tblArchiveBoxes, linked on
field LocationID. (one location may store many boxes, but each box is stored
at only one location at any single point in time.)
tblArchiveBoxes has a one:many relationship with tblBoxCompartments, linked
on field BoxID. (one box may have many compartments, but each compartment is
in only one box.)
tblBoxCompartments has a one:many relationship with tblReports, linked on
field CompartmentID. (one box compartment may house many reports, but each
report is located in only one compartment.)

so any given report will be found in a specific box compartment, which is
linked to a specific archive box, which is assigned to a specific storage
location. correct?

hth

ps. setting up data entry to automatically create the BoxCompartment
records, is a separate issue. you need to get the tables/relationships right
first, then we'll tackle data entry / user interface issues.

I am developing a database to record which archive box a particular report is
stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box 1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just 2000
without any compartments.

When the user enters a new archive box into the database I want them to
enter the BoxNumber, NumberOfCompartments, and StorageLocation. My database
will automatically generate 1000A, 1000B, 1000C, and 1000D from BoxNumber and
NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn't look quite
right to me.

Any suggestions appreciated. After I've designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth

 

[tina]

Thanks again Tina!
you're welcome

Do you have any tips on automatically creating BoxCompartment records?

well, a simple form interface used strictly to add records to
tblArchiveBoxes, and corresponding "child" records to tblBoxCompartments,
can be set up fairly easily. i can describe one way to set that up, which
should at least give you a starting place to do what suits your needs. but,
first, a couple questions:

will the compartments always have "default", incrementing names? for
example: A, B, C, D, etc. if that's the case, the easiest way to control
what values the system will use in the naming process is to create a table
that simply lists the default names, probably with an additional field for
sort order.

or, instead, will the user need to be able to manually assign the
compartment names at the time the new archive box record is entered?

Thanks again Tina! Makes perfect sense. All your assumptions were correct.
I've set up tables and relationships as you suggested and it's looking good.
Now it's onto the forms. I've already done a bit of work on the main form,
just have to add the interface for the archive boxes.

Do you have any tips on automatically creating BoxCompartment records?

Seth

if we presume the following:

the number of compartments in a single archive box does not change over
time. (if Box1000 has 4 compartments today, it will always have 4
compartments, period.)
the compartments are always within the box, so wherever the box is located,
that's where the compartments of that box will be found. (if Box1000 is at
location X, then BoxCompartment 1000B is by definition at location X - there
is no way it can be at location Y.)
you only want to know where a specific archive box is located *right now*,
not a history of where the box may have been located in the past (and then
moved from).
you only want to know where a specific report is stored *right now*, not a
history of where the report may have stored in the past (and then moved
from).

given the preceding assumptions, the following tables should work, as

tblLocations
LocationID (pk)
LocationDescription

tblArchiveBoxes
BoxID (pk)
LocationID (foreign key from tblLocations)

tblBoxCompartments
CompartmentID (pk)
BoxID (foreign key from tblArchiveBoxes)
CompartmentCode (A, B, C, D, whatever)
(a box without compartments would still have one record in this table:
CompartmentCode A, since the whole box can be considered to have a single
compartment.)

tblReports
ReportID (primary key)
ReportName
ReportDescription
CompartmentID (foreign key from tblBoxCompartments)

tblLocations has a one:many relationship with tblArchiveBoxes, linked on
field LocationID. (one location may store many boxes, but each box is stored
at only one location at any single point in time.)
tblArchiveBoxes has a one:many relationship with tblBoxCompartments, linked
on field BoxID. (one box may have many compartments, but each compartment is
in only one box.)
tblBoxCompartments has a one:many relationship with tblReports, linked on
field CompartmentID. (one box compartment may house many reports, but each
report is located in only one compartment.)

so any given report will be found in a specific box compartment, which is
linked to a specific archive box, which is assigned to a specific storage
location. correct?

hth

ps. setting up data entry to automatically create the BoxCompartment
records, is a separate issue. you need to get the tables/relationships right
first, then we'll tackle data entry / user interface issues.

I am developing a database to record which archive box a particular

report
is

stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box 1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just 2000
without any compartments.

When the user enters a new archive box into the database I want them to
enter the BoxNumber, NumberOfCompartments, and StorageLocation. My database
will automatically generate 1000A, 1000B, 1000C, and 1000D from

BoxNumber
and

NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn't look quite
right to me.

Any suggestions appreciated. After I've designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth

 

[Seth]

Yes, I would like the the compartments always have default incrementing names
A, B, C, D etc

Cheers,
Seth

Thanks again Tina!

you're welcome

Do you have any tips on automatically creating BoxCompartment records?

well, a simple form interface used strictly to add records to
tblArchiveBoxes, and corresponding "child" records to tblBoxCompartments,
can be set up fairly easily. i can describe one way to set that up, which
should at least give you a starting place to do what suits your needs. but,
first, a couple questions:

will the compartments always have "default", incrementing names? for
example: A, B, C, D, etc. if that's the case, the easiest way to control
what values the system will use in the naming process is to create a table
that simply lists the default names, probably with an additional field for
sort order.

or, instead, will the user need to be able to manually assign the
compartment names at the time the new archive box record is entered?

Thanks again Tina! Makes perfect sense. All your assumptions were correct.
I've set up tables and relationships as you suggested and it's looking good.
Now it's onto the forms. I've already done a bit of work on the main form,
just have to add the interface for the archive boxes.

Do you have any tips on automatically creating BoxCompartment records?

Seth

if we presume the following:

the number of compartments in a single archive box does not change over
time. (if Box1000 has 4 compartments today, it will always have 4
compartments, period.)
the compartments are always within the box, so wherever the box is located,
that's where the compartments of that box will be found. (if Box1000 is at
location X, then BoxCompartment 1000B is by definition at location X - there
is no way it can be at location Y.)
you only want to know where a specific archive box is located *right now*,
not a history of where the box may have been located in the past (and then
moved from).
you only want to know where a specific report is stored *right now*, not a
history of where the report may have stored in the past (and then moved
from).

given the preceding assumptions, the following tables should work, as

tblLocations
LocationID (pk)
LocationDescription

tblArchiveBoxes
BoxID (pk)
LocationID (foreign key from tblLocations)

tblBoxCompartments
CompartmentID (pk)
BoxID (foreign key from tblArchiveBoxes)
CompartmentCode (A, B, C, D, whatever)
(a box without compartments would still have one record in this table:
CompartmentCode A, since the whole box can be considered to have a single
compartment.)

tblReports
ReportID (primary key)
ReportName
ReportDescription
CompartmentID (foreign key from tblBoxCompartments)

tblLocations has a one:many relationship with tblArchiveBoxes, linked on
field LocationID. (one location may store many boxes, but each box is stored
at only one location at any single point in time.)
tblArchiveBoxes has a one:many relationship with tblBoxCompartments, linked
on field BoxID. (one box may have many compartments, but each compartment is
in only one box.)
tblBoxCompartments has a one:many relationship with tblReports, linked on
field CompartmentID. (one box compartment may house many reports, but each
report is located in only one compartment.)

so any given report will be found in a specific box compartment, which is
linked to a specific archive box, which is assigned to a specific storage
location. correct?

hth

ps. setting up data entry to automatically create the BoxCompartment
records, is a separate issue. you need to get the tables/relationships right
first, then we'll tackle data entry / user interface issues.


I am developing a database to record which archive box a particular report
is
stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box
1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just 2000
without any compartments.

When the user enters a new archive box into the database I want them to
enter the BoxNumber, NumberOfCompartments, and StorageLocation. My
database
will automatically generate 1000A, 1000B, 1000C, and 1000D from BoxNumber
and
NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn't look
quite
right to me.

Any suggestions appreciated. After I've designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth

 

[tina]

okay, here's one solution that i worked out, using the tables/fields that i
posted earlier:

create an additional table, as

tblCompartmentCodes
CodeName (primary key)
SortBy (Number data type)

in this table, enter whatever default codes you want to use in naming your
box compartments. enter the SortBy numbers so the codes will always be
sorted in the same ascending order - 1, 2, 3, 4, 5, etc. (it seems obvious
that A, B, C, D values don't need a sort order. BUT, using a SortBy field
allows you to use any codes you want/need to - without being limited to an
sort order inherent in the codes themselves.)

i used the AutoForm wizard to create a simple form bound to tblArchiveBoxes.
(select the table in the database window, then click the AutoForm button on
the toolbar.) i named the form frmAddArchiveBoxes.
i set the form's DataEntry property to Yes, so the form will only be used to
add *new* archive box records to the table.
i added an unbound textbox to the form, naming it txtCompartments, and set
the DefaultValue to 1.
i added a command button (Command6) to the form, with the caption "Add
Compartments).

i created a Select query (named qryCompartmentCodes) to pull the correct
number of compartment codes from tblCompartmentCodes, based on the number
entered in the form's unbound textbox, as

SELECT tblCompartmentCodes.SortBy, tblCompartmentCodes.CodeName
FROM tblCompartmentCodes
WHERE
(((tblCompartmentCodes.SortBy)<=[Forms]![frmAddArchiveBoxes]![txtCompartment
s]))
ORDER BY tblCompartmentCodes.SortBy;

i created an Append query to add the correct number of records to
tblBoxCompartments, based on the above Select query, and the ArchiveBoxID
entered in the form, as

INSERT INTO tblBoxCompartments ( CompartmentCode, BoxID )
SELECT qryCompartmentCodes.CodeName, [Forms]![frmAddArchiveBoxes]![BoxID] AS
Expr1
FROM qryCompartmentCodes;

i added code to the form's command button to run the query, which adds the
correct number of box compartment records for the new archive box record, as

Private Sub Command6_Click()

If IsNull(Me!BoxID) Then
MsgBox "Enter the ID for the new archive box, please."
Me!BoxID.SetFocus
ElseIf IsNull(Me!txtCompartments) Then
MsgBox "Enter the number of compartments in the " _
& "new archive box, please."
Me!txtCompartments.SetFocus
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendCompartments"
DoCmd.SetWarnings True
MsgBox "Compartments added."
DoCmd.RunCommand acCmdRecordsGoToNew
Me!txtCompartments = 1
End If

End Sub

keep in mind that the above is only one solution, not the *only* solution.
but it gives you a starting point, at least, to work out a solution that
fits your needs perfectly.

hth

Yes, I would like the the compartments always have default incrementing names
A, B, C, D etc

Cheers,
Seth

Thanks again Tina!

you're welcome

Do you have any tips on automatically creating BoxCompartment records?

well, a simple form interface used strictly to add records to
tblArchiveBoxes, and corresponding "child" records to tblBoxCompartments,
can be set up fairly easily. i can describe one way to set that up, which
should at least give you a starting place to do what suits your needs. but,
first, a couple questions:

will the compartments always have "default", incrementing names? for
example: A, B, C, D, etc. if that's the case, the easiest way to control
what values the system will use in the naming process is to create a table
that simply lists the default names, probably with an additional field for
sort order.

or, instead, will the user need to be able to manually assign the
compartment names at the time the new archive box record is entered?

Thanks again Tina! Makes perfect sense. All your assumptions were correct.
I've set up tables and relationships as you suggested and it's looking good.
Now it's onto the forms. I've already done a bit of work on the main form,
just have to add the interface for the archive boxes.

Do you have any tips on automatically creating BoxCompartment records?

Seth


:

if we presume the following:

the number of compartments in a single archive box does not change over
time. (if Box1000 has 4 compartments today, it will always have 4
compartments, period.)
the compartments are always within the box, so wherever the box is located,
that's where the compartments of that box will be found. (if Box1000

is
at

location X, then BoxCompartment 1000B is by definition at location

X -
there

is no way it can be at location Y.)
you only want to know where a specific archive box is located *right now*,
not a history of where the box may have been located in the past

(and
then

moved from).
you only want to know where a specific report is stored *right now*,

not
a

history of where the report may have stored in the past (and then moved
from).

given the preceding assumptions, the following tables should work, as

tblLocations
LocationID (pk)
LocationDescription

tblArchiveBoxes
BoxID (pk)
LocationID (foreign key from tblLocations)

tblBoxCompartments
CompartmentID (pk)
BoxID (foreign key from tblArchiveBoxes)
CompartmentCode (A, B, C, D, whatever)
(a box without compartments would still have one record in this table:
CompartmentCode A, since the whole box can be considered to have a single
compartment.)

tblReports
ReportID (primary key)
ReportName
ReportDescription
CompartmentID (foreign key from tblBoxCompartments)

tblLocations has a one:many relationship with tblArchiveBoxes, linked on
field LocationID. (one location may store many boxes, but each box

is
stored

at only one location at any single point in time.)
tblArchiveBoxes has a one:many relationship with tblBoxCompartments, linked
on field BoxID. (one box may have many compartments, but each compartment is
in only one box.)
tblBoxCompartments has a one:many relationship with tblReports,

linked
on

field CompartmentID. (one box compartment may house many reports,

but
each

report is located in only one compartment.)

so any given report will be found in a specific box compartment,

which
is

linked to a specific archive box, which is assigned to a specific storage
location. correct?

hth

ps. setting up data entry to automatically create the BoxCompartment
records, is a separate issue. you need to get the

tables/relationships
right

first, then we'll tackle data entry / user interface issues.


I am developing a database to record which archive box a

particular
report

is
stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box
1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just 2000
without any compartments.

When the user enters a new archive box into the database I want

them
to

enter the BoxNumber, NumberOfCompartments, and StorageLocation. My
database
will automatically generate 1000A, 1000B, 1000C, and 1000D from BoxNumber
and
NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn't look
quite
right to me.

Any suggestions appreciated. After I've designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth

 

[Seth]

Thanks. That vives me a lot to work with.
Cheers,
Seth

okay, here's one solution that i worked out, using the tables/fields that i
posted earlier:

create an additional table, as

tblCompartmentCodes
CodeName (primary key)
SortBy (Number data type)

in this table, enter whatever default codes you want to use in naming your
box compartments. enter the SortBy numbers so the codes will always be
sorted in the same ascending order - 1, 2, 3, 4, 5, etc. (it seems obvious
that A, B, C, D values don't need a sort order. BUT, using a SortBy field
allows you to use any codes you want/need to - without being limited to an
sort order inherent in the codes themselves.)

i used the AutoForm wizard to create a simple form bound to tblArchiveBoxes.
(select the table in the database window, then click the AutoForm button on
the toolbar.) i named the form frmAddArchiveBoxes.
i set the form's DataEntry property to Yes, so the form will only be used to
add *new* archive box records to the table.
i added an unbound textbox to the form, naming it txtCompartments, and set
the DefaultValue to 1.
i added a command button (Command6) to the form, with the caption "Add
Compartments).

i created a Select query (named qryCompartmentCodes) to pull the correct
number of compartment codes from tblCompartmentCodes, based on the number
entered in the form's unbound textbox, as

SELECT tblCompartmentCodes.SortBy, tblCompartmentCodes.CodeName
FROM tblCompartmentCodes
WHERE
(((tblCompartmentCodes.SortBy)<=[Forms]![frmAddArchiveBoxes]![txtCompartment
s]))
ORDER BY tblCompartmentCodes.SortBy;

i created an Append query to add the correct number of records to
tblBoxCompartments, based on the above Select query, and the ArchiveBoxID
entered in the form, as

INSERT INTO tblBoxCompartments ( CompartmentCode, BoxID )
SELECT qryCompartmentCodes.CodeName, [Forms]![frmAddArchiveBoxes]![BoxID] AS
Expr1
FROM qryCompartmentCodes;

i added code to the form's command button to run the query, which adds the
correct number of box compartment records for the new archive box record, as

Private Sub Command6_Click()

If IsNull(Me!BoxID) Then
MsgBox "Enter the ID for the new archive box, please."
Me!BoxID.SetFocus
ElseIf IsNull(Me!txtCompartments) Then
MsgBox "Enter the number of compartments in the " _
& "new archive box, please."
Me!txtCompartments.SetFocus
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendCompartments"
DoCmd.SetWarnings True
MsgBox "Compartments added."
DoCmd.RunCommand acCmdRecordsGoToNew
Me!txtCompartments = 1
End If

End Sub

keep in mind that the above is only one solution, not the *only* solution.
but it gives you a starting point, at least, to work out a solution that
fits your needs perfectly.

hth

Yes, I would like the the compartments always have default incrementing names
A, B, C, D etc

Cheers,
Seth

Thanks again Tina!
you're welcome

Do you have any tips on automatically creating BoxCompartment records?

well, a simple form interface used strictly to add records to
tblArchiveBoxes, and corresponding "child" records to tblBoxCompartments,
can be set up fairly easily. i can describe one way to set that up, which
should at least give you a starting place to do what suits your needs. but,
first, a couple questions:

will the compartments always have "default", incrementing names? for
example: A, B, C, D, etc. if that's the case, the easiest way to control
what values the system will use in the naming process is to create a table
that simply lists the default names, probably with an additional field for
sort order.

or, instead, will the user need to be able to manually assign the
compartment names at the time the new archive box record is entered?


Thanks again Tina! Makes perfect sense. All your assumptions were
correct.
I've set up tables and relationships as you suggested and it's looking
good.
Now it's onto the forms. I've already done a bit of work on the main form,
just have to add the interface for the archive boxes.

Do you have any tips on automatically creating BoxCompartment records?

Seth


:

if we presume the following:

the number of compartments in a single archive box does not change over
time. (if Box1000 has 4 compartments today, it will always have 4
compartments, period.)
the compartments are always within the box, so wherever the box is
located,
that's where the compartments of that box will be found. (if Box1000 is
at
location X, then BoxCompartment 1000B is by definition at location X -
there
is no way it can be at location Y.)
you only want to know where a specific archive box is located *right
now*,
not a history of where the box may have been located in the past (and
then
moved from).
you only want to know where a specific report is stored *right now*, not
a
history of where the report may have stored in the past (and then moved
from).

given the preceding assumptions, the following tables should work, as

tblLocations
LocationID (pk)
LocationDescription

tblArchiveBoxes
BoxID (pk)
LocationID (foreign key from tblLocations)

tblBoxCompartments
CompartmentID (pk)
BoxID (foreign key from tblArchiveBoxes)
CompartmentCode (A, B, C, D, whatever)
(a box without compartments would still have one record in this table:
CompartmentCode A, since the whole box can be considered to have a
single
compartment.)

tblReports
ReportID (primary key)
ReportName
ReportDescription
CompartmentID (foreign key from tblBoxCompartments)

tblLocations has a one:many relationship with tblArchiveBoxes, linked on
field LocationID. (one location may store many boxes, but each box is
stored
at only one location at any single point in time.)
tblArchiveBoxes has a one:many relationship with tblBoxCompartments,
linked
on field BoxID. (one box may have many compartments, but each
compartment is
in only one box.)
tblBoxCompartments has a one:many relationship with tblReports, linked
on
field CompartmentID. (one box compartment may house many reports, but
each
report is located in only one compartment.)

so any given report will be found in a specific box compartment, which
is
linked to a specific archive box, which is assigned to a specific
storage
location. correct?

hth

ps. setting up data entry to automatically create the BoxCompartment
records, is a separate issue. you need to get the tables/relationships
right
first, then we'll tackle data entry / user interface issues.


I am developing a database to record which archive box a particular
report
is
stored in.

Archive boxes can be moved to different storage locations.

Each archive box can contain a number of compartments. eg. Archive box
1000
contains 1000A, 1000B, 1000C and 1000D. But an archive box dose not
necessarily contain any compartments. eg. Archive box 2000 is just
2000
without any compartments.

When the user enters a new archive box into the database I want them
to
enter the BoxNumber, NumberOfCompartments, and StorageLocation. My
database
will automatically generate 1000A, 1000B, 1000C, and 1000D from
BoxNumber
and
NumberOfCompartments.

At the moment I will try to do this with 3 tables. But it doesn't look
quite
right to me.

Any suggestions appreciated. After I've designed the tables and
relationships I need to work out how to automatically generate the
compartment numbers. from NumberOfCompartments.

tblReports
fldReportID (x)MANY
[+ other fields]

tblArchiveBoxes (x)ONE
fldArchiveBoxNum
fldArchiveBoxCompartment
fldArchiveBoxStorageLctn (y)MANY


tblStorageLocations
StorageLctnID (y)ONE
StorageLctn
StorageLctnSite


tblArchiveBoxes
fldArchiveBoxNum,fldArchiveBoxCompartment,fldArchiveBoxStorageLctn
1000,A,Office
1000,B,Office
1000,C,Office
1000,D,Office
2000,,Factory
3000,A,Factory
3000,B,Factory
4000,Office

Regards,
Seth