Open from Master and Rename

[Guest]

I'm a novice user here. So please keep that in mind. I've developed a
database in Access 2000. It's not a running program like one would be used
for inventory, sales, etc. We use the program to enter address/site
information, then enter data related to items collected or retrieved. I am
trying to get the program to, upon opening, prompt the user to copy and
rename that database for that specific case. Right now I tell them to copy
the "Master", paste it, then rename the "Master". It would be much more user
friendly if, when opened, it would prompt the user to give his/her data a new
name, then all the tables, queries, forms, and reports would still work in
that database. Any help?
Thanks

 

[gls858]

I'm a novice user here. So please keep that in mind. I've developed a
database in Access 2000. It's not a running program like one would be used
for inventory, sales, etc. We use the program to enter address/site
information, then enter data related to items collected or retrieved. I am
trying to get the program to, upon opening, prompt the user to copy and
rename that database for that specific case. Right now I tell them to copy
the "Master", paste it, then rename the "Master". It would be much more user
friendly if, when opened, it would prompt the user to give his/her data a new
name, then all the tables, queries, forms, and reports would still work in
that database. Any help?
Thanks

I could be wrong here but it's sounds like you have a badly designed
database. You shouldn't need to have a database for each case.
More info about your database would be helpful. Tables, data stored, etc.

gls858

 

[Guest]

You could be right. ha. Like I said, I'm self-taught. I don't think we
would need a seperate dateabase for each case. If I could tell it to save
the data from each case, seperately, but so that we could generate reports
for a specific case, that would work great. That's my goal. Thanks

 

[John W. Vinson]

I'm a novice user here. So please keep that in mind. I've developed a
database in Access 2000. It's not a running program like one would be used
for inventory, sales, etc. We use the program to enter address/site
information, then enter data related to items collected or retrieved. I am
trying to get the program to, upon opening, prompt the user to copy and
rename that database for that specific case. Right now I tell them to copy
the "Master", paste it, then rename the "Master". It would be much more user
friendly if, when opened, it would prompt the user to give his/her data a new
name, then all the tables, queries, forms, and reports would still work in
that database. Any help?
Thanks

As gls858 correctly says, you certainly should NOT have a separate database
for each "case". Could you describe the nature of the data that you're
storing? I suspect you should have one record in a Cases table for each case;
an Items table; and probably a third table to indicate which items belong to
each case. This will let you generate case-specific reports.

For some tutorials on how to design databases in Access, see:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]

 

[Guest]

The "case" would be each specific search warrant we serve. There is specific
inforamtion per case such as date/time/locaton, etc. Then I have table(s)
for evidence seized, search participants, etc. Right now it is one case per
database (the way I am copying and renaming. If I could make it so one case
# is relational to each specific peice of evidence, that's the goal. In the
evidence data form, I do have a case # field as well. Would this make it
specific to that case?

 

[John W. Vinson]

The "case" would be each specific search warrant we serve. There is specific
inforamtion per case such as date/time/locaton, etc. Then I have table(s)
for evidence seized, search participants, etc. Right now it is one case per
database (the way I am copying and renaming. If I could make it so one case
# is relational to each specific peice of evidence, that's the goal. In the
evidence data form, I do have a case # field as well. Would this make it
specific to that case?

Absolutely. You don't need or want additional databases!!! <shudder>

If you're using table datasheets to interact with the data... don't. Instead,
use a Form based on the cases table, with a Subform based on the evidence
table. You'll also need tables for such things as a people table in which to
enter officers (search participants), etc.

DON'T make the common mistake of starting your design with the Form. Forms are
*just windows*. The foundation of your database is a properly normalized set
of Tables; the forms are built on them - not vice versa. You might have table
structures resembling (I obviously don't know your data model, so this is just
a partial suggestion):

Cases
CaseNo <Text, Primary Key, unique case number>
CaseDateTime <don't use Date as a fieldname, do store date and time in the
same field>
Location

Evidence
EvidenceID <Primary Key, unique number for a piece of evidence; probably
Number, not Autonumber since autonumbers will have gaps and should not be
human-visible>
CaseNo <link to Cases>
EvidenceType <e.g. "Firearm", "Hair sample", "Vehicle", ...>
Description
<other information pertinant to the physical evidence object>

SearchParticipants
CaseNo <one field of the two-field primary key>
OfficerID <second field of the key>
<any information about this officer's involvement in this case>

Officers
OfficerID <primary key; badge number say>
LastName
FirstName
<other biographical info>


You would use Forms, Subforms, combo boxes and other such tools to manage the
data.

I've done a largish dispatching database for a state police agency so I have
at least some experience with the subject; but this could be a fairly large
and complex application!

John W. Vinson [MVP]

 

[Guest]

Could I send you my database so you could look at it?

 

[John W. Vinson]

Could I send you my database so you could look at it?

Depends; could I send you an invoice at my current consulting fees?

John W. Vinson [MVP]

 

[Guest]

Ha, always a catch eh? Well, I'm trying what you suggested, and will see if
I can get er to work. Trouble is, I already have tables (started from there
and worked up, and have queries, forms and reports. So just gotta figure out
the best way to link all the info to a specific case.

Thanks

 

[John W. Vinson]

Ha, always a catch eh?

<g> Sorry, but I'm a self-employed consultant making my living at this (and
helping my wife with her farmers' market and other businesses, and this and
that); my time on the newsgroups is on a volunteer basis. Downloading and
fixing a database goes beyond the free support threshold.

Well, I'm trying what you suggested, and will see if
I can get er to work. Trouble is, I already have tables (started from there
and worked up, and have queries, forms and reports. So just gotta figure out
the best way to link all the info to a specific case.

You need a "foreign key" - the case number - in each related table. Typically
you would (again) use a Form based on the cases table, with one or more
subforms based on the related tables, using the case number as the
master/child linking field.

John W. Vinson [MVP]

 

[gls858]

Ha, always a catch eh? Well, I'm trying what you suggested, and will see if
I can get er to work. Trouble is, I already have tables (started from there
and worked up, and have queries, forms and reports. So just gotta figure out
the best way to link all the info to a specific case.

Thanks

Sometimes it's just easier to start from scratch than try to fix a badly
designed database. You might try searching on Database design and see
if you can find a tutorial. A little studying on the front end can save
you tons of time in the end. Following is just one of many links I found
and I only took a brief look at it but it seems to cover many of the
things I've seen discussed here on a frequent basis. Watch for line wrap
on the URL.
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000008.htm

gls858

 

[Guest]

I understand. I appreciate your help. I'll keep plugging away. Thanks for
the advice.

 

[Guest]

Thanks, I'll take a look and see what I can do. I guess even if I do start
over, I can import the reports and such I have already made into the new
database.

thanks again.

 

[John W. Vinson]

Thanks, I'll take a look and see what I can do. I guess even if I do start
over, I can import the reports and such I have already made into the new
database.

well... maybe. If your tables are incorrectly normalized, then the Report's
recordsource will be improperly normalized, and may be difficult to
reconstruct. It's certainly worth a try though - you should be able to salvage
the layout of controls on the report, but may need to change most or all of
their control sources.

John W. Vinson [MVP]