multi item combo boxes or list box

[vpettes]

I am new to Access having used Filemaker for many years. My question
has to do with displaying and creating a line item database. What I
have is a two tables. One has Name, Location, time, disposition and
Offense. The second has Offenses and Description of Offenses. I want to
display, on a form, all the fields in table one with the Offense field,
a drop down, combo box or list box, able to display more than one item.
For example, if the user chooses in the drop down box 59FC, then 59FA,
then 59NS. These would show on the form all the choices, preferably
under each other. I am thinking a third table, a line item table, may
need to be created to store the data for report purposes. But I will
figure that out. Anyway, any suggestions?
vincent

Sample of what I am looking for:

Name Location Time Dispositon Offense
john 123 anywhere 10:00 Weeds 59FB
59FC
59FA

[MGFoster]

vp*****@las-cruces.org wrote:

I am new to Access having used Filemaker for many years. My question
has to do with displaying and creating a line item database. What I
have is a two tables. One has Name, Location, time, disposition and
Offense. The second has Offenses and Description of Offenses. I want to
display, on a form, all the fields in table one with the Offense field,
a drop down, combo box or list box, able to display more than one item.
For example, if the user chooses in the drop down box 59FC, then 59FA,
then 59NS. These would show on the form all the choices, preferably
under each other. I am thinking a third table, a line item table, may
need to be created to store the data for report purposes. But I will
figure that out. Anyway, any suggestions?
vincent

Sample of what I am looking for:

Name Location Time Dispositon Offense
john 123 anywhere 10:00 Weeds 59FB
59FC
59FA

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe your DB design needs to be changed (Normalized) to something
like this:

Defendants:
DefendantID -- An AutoNumber column
Name
... other columns that ID the defendant ...

I'm not sure about the following table, since you used such vague column
names.

Crimes: (or some other more specific/legal name)
DefendantID -- a link to Defendants table
Location -- Where the crime was committed ?
CrimeTime -- DateTime when the crime was committed ?
Disposition -- Where the defendant is placed ?
What do you mean by Disposition?

OffenseTypes (your second table):
OffenseCode -- the 59FA, 59FC, etc. codes
OffenseDescript ion -- the verbiage for each code

Charges:
DefendantID - a link to the DefendantID in Defendants
OffenseID - a link to the table OffenseTypes

Then you'd have a main form for the Defendant; a subform for the Crimes;
a subform in the Crimes subform for the Charges. The OffenseID in the
Charges subform would be a ComboBox. All the subform's would link back
up the subform chain to the main form by the DefendantID.

Read the Access Help articles on subforms for more info.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnfHpYechKq OuFEgEQI0SACgyP puyUIbXNKNGWmFU pamBPcIGp0AoL5W
2i6lIRu1s4iY+xB Bkh2L1w46
=iU4A
-----END PGP SIGNATURE-----

[vpettes]

Thanks for your insight. However, what we use is much simpler. What we
need is a simple flat file to log incidents. Everything else is done
except for the combo box that allows for multiple offense codes to be
displayed for one incident. How would I do that? Would I use a list
box? What would the relationship look like? BTW, is there something
like a portal that can be put on a form to display date from another
related file? Thanks again.
vincent

[Rick Brandt]

vp*****@las-cruces.org wrote:

Thanks for your insight. However, what we use is much simpler. What we
need is a simple flat file to log incidents. Everything else is done
except for the combo box that allows for multiple offense codes to be
displayed for one incident. How would I do that? Would I use a list
box? What would the relationship look like? BTW, is there something
like a portal that can be put on a form to display date from another
related file? Thanks again.
vincent

So you need "Many" offense codes for each "One" incident? Sounds like a
One-To-Many relationship doesn't it? That requires two tables if you want
to do it right. Otherwise just use Excel.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[vpettes]

Yes, yes! I have created the two tables, Log and Offenses, with a
relationship between two fields, i'll call them "callnumber ", this is a
number field, to link them together. I have imported the data needed
into the second table, which has three fields, offenses, offense
description and callnumber. How do I show in the form the fields
"offenses" and "offense description". This needs to be a drop down for
"offenses" to ease data entry. Thanks.

[Tim Marshall]

vp*****@las-cruces.org wrote:

Thanks for your insight. However, what we use is much simpler. What we
need is a simple flat file to log incidents.

Hi Vincent,

No offense meant, but you mentioned you have used Filemaker for years,
which, I assume, allows relational database principles to be followed.
As MGFoster or Rick mentions, the description of your requirements
dictates a multi-table normalized approach. If you don't want to do
this (and multi-table design of a database is kind of difficult if
you're not used to it) and would prefer a flat file, your job would be
made much, much easier by simply using Excel, I think. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[vpettes]

Thanks. I wasn't offended. I am trying to adjust my thinking to Access
from Filemaker and my description was a little blurred. I am used to
the multi-table approach. What I could do all day in Filemaker is
becoming a chore in Access. But that's another Google Group. Anyway, is
there a book or website that I can look at samples in order to get a
clear understanding as how Access works. Thanks

[Larry Linson]

<vp*****@las-cruces.org> wrote

is there a book or website that I can
look at samples in order to get a
clear understanding as how Access
works. Thanks

A very good example, Northwind.MDB, comes packaged with Access and will have
been installed in the folder hierarchy where you installed Office, unless
you opted for a custom install and omitted it. There is a very useful Orders
and Solutions sample database, temporarily housed at
http://www.mvps.org/access/downloads/bapp2000.exe. Many of the samples in
that database refer to Northwind.

If you are using Access 2003, follow the link in Help to Office Online |
Training, and follow the links there to specific topics. You'll find a
number of useful, thankfully brief online training courses. If you are not
using Access 2003, most of the material will still apply, and you can go
directly to http://office.microsoft.com/en-us/training/default.aspx.

Larry Linson
Microsoft Access MVP

P.S. Here's the body of a post from late last year in the
microsoft.publi c.access.gettin gstarted newsgroup, by Jeff Conrad, a
self-confessed Access Junkie, who did us all a great favor by collecting and
posting these links:

Here's one or two links, grouped by category, to help in your Access qwest.
Please watch out for any
possible line wrapping on these links. You should definitely find one or two
things in here to help
you. <g>

Number One on your start list:

http://www.mvps.org/access/
(A definite must!)

Microsoft Knowledge Base for searching:
http://support.microsoft.com/?scid=fh;[ln];kbhowto
Getting Started:

http://www.mvps.org/access/tencommandments.htm

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Glossary of database terms:
http://www.dhdursoassociates.com/dat...lossary-3.html

Where to find information about designing a database in Microsoft Access:
http://support.microsoft.com/?id=289533

"Understand ing Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understand ing Relational Database Design"
http://support.microsoft.com/?id=234208

Naming Conventions:
http://www.xoc.net/standards/rvbanc.asp

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understand ing Normalization")

http://www.oreilly.com/catalog/acces...pter/ch04.html

http://www.datatexcg.com/Downloads/D...gnTips1997.pdf

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genacc...abasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default...lurb060600.asp

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html
Access Data "Clean-up" Tips:
http://pubs.logicalexpressions.com/P...cle.asp?ID=182
Interface Design topics:
http://www.isii.com/ui_design.html
Tutorial Style:

http://www.microsoft.com/Accessdev/a...bapp97/toc.htm

Microsoft Access 2000: Building Applications with Forms and Reports
http://tinyurl.com/2szmm

http://www.dur.ac.uk/its/training/se...&campus=durham
(Sample practice file for download as well)

http://www.geekgirls.com/databasics_01.htm

http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html

http://www.microsoft.com/office/prev...s/column06.asp

http://www.functionx.com/access/

http://www.bcschools.net/staff/AccessHelp.htm#Top

http://www.fontstuff.com/access/index.htm

http://www.fgcu.edu/support/office2000/access/

http://www.oit.duke.edu/ats/training/docs/access1/

http://cisnet.baruch.cuny.edu/holowc...html#sec_intro

http://ddmara.tripod.com/access/index.html

https://secure.vtc.com/cd/access2003.htm

http://www.simply-access.com/Start_m...tutorials.html

http://www.rogersaccesslibrary.com/Tutorials.html

Tutorial: Analyzing Data Using Access
http://www.microsoft.com/education/AccessTutorial.aspx

Sample Database Models/Table Designs:

http://www.databaseanswers.com/data_models/index.htm
Download Samples:

http://rogersaccesslibrary.com/TableOfContents3.asp

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

http://www.candace-tripp.com/access_downloads.htm

http://www.datastrat.com/DataStrat2.html

http://members.shaw.ca/glenk/index.html

Fred Boer's fully functioning library application.
Lots of different topics for study included:
http://www3.sympatico.ca/lornarourke/fred.zip
The mighty Sensei Leban's site for all things magical:
(Please bow in reverence on your way in.)

http://www.lebans.com/
Advanced Topics:

http://www.trigeminal.com/utility.asp

http://www.mentalis.org/index2.shtml
Misc: Downloads/Samples/Helpful Articles

http://www.granite.ab.ca/accsmstr.htm

http://allenbrowne.com/tips.html

http://www.viescas.com

http://www.daiglenet.com/msaccess.htm

http://www.users.bigpond.com/papwalker/

http://www.applecore99.com/index.asp

http://www.helenfeddema.com/access.htm

http://www.dbases.net/knowledge_base/

http://ourworld.compuserve.com/homepages/attac-cg/

http://www.aadconsulting.com/index.html

http://accdevel.tripod.com/

http://ffdba.com/downloads.htm

http://www.attcanada.net/~kallal.msn.../msaccess.html

http://www.mvps.org/btmtz/

http://www.jstreettech.com/Downloads.asp

http://www.liquorman.net/Tips.html
Helpful Articles/Code Samples:

http://www.pacificdb.com.au/MVP/MVPCode.htm

http://members.rogers.com/douglas.j....cessIndex.html

http://www.winsoft.sk/daofaq.htm

http://www.calvinsmithsoftware.com/HardToFindTips.htm

http://www.mvps.org/vbnet/

http://www.master-office.com/developers.htm

http://www.trevor.easynet.co.uk/AccFAQ/

http://www.theofficeexperts.com/access.htm

http://www.access.qbuilt.com/

http://www.microsoft-accesssolutions.co.uk/

http://www.blueclaw-db.com/ms_access_examples/index.htm

http://www.jamiessoftware.tk/

http://www.pdtech.co.uk/prod01.htm

http://easyweb.easynet.co.uk/~trevor/AccFAQ/
(A little dated, but still some helpful things)

http://www.vbexplorer.com/VBExplorer/VBExplorer.asp
Run Command Constants:

http://www.tkwickenden.clara.net/
A massive list of other Access links:

http://www.geocities.com/waddly/accory.html

http://dmoz.org/Computers/Software/Databases/MS_Access/

http://www.directory-base.com/access/
Add-Ins/Utilities:

My personal favorite on Doug Steele's site:
http://members.rogers.com/douglas.j....ocumentor.html
;-)

http://mphillipson.users.btopenworld.com/

http://www.fmsinc.com/

http://www.moshannon.com/

http://www.rickworld.com

http://www3.bc.sympatico.ca/starthere/findandreplace

http://www.peterssoftware.com/index.htm

http://www.dbi-tech.com/default.asp

http://www.zada.com.au/accessaddins.htm

http://www.mztools.com

http://www.logico-solutions.com/

http://accesstools.narod.ru/

http://www.directory-base.com/access...itle=Add%20Ins

http://www.skrol29.com/index.php?lang=us
Live Access chats every Tuesday and Thursday:
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Access User-Level Security:

Security FAQ (the Security Bible):
http://support.microsoft.com/?kbid=207793

Jack Macdonald's Security Document:
http://www.geocities.com/jacksonmacd...ssSecurity.pdf

Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm

Joan Wild's Tips:
http://www.jmwild.com/security02.htm
Creating menu bars and tool bars:

http://www.microsoft.com/Accessdev/a...ers/ba01_6.htm
Additional Access Forums:

http://www.accessvba.com/

http://www.utteraccess.com

http://www.access-programmers.co.uk/forums/
Book Recommendations :

http://www.developershandbook.com/
(A VERY good book to have)

http://www.viescas.com/Info/books.htm

http://www.fmsinc.com/toplevel/books.htm
Some of the MVP web sites:

http://www.mvps.org/links.html
Database Recovery:

http://www.pksolutions.com/

http://www.atroplan.com/
Search Google archives on Access newsgroup posts:

http://tinyurl.com/ctj2
That should keep you busy for a while! ;-)

[vincent]

Thanks a bunch. Didn't know there was so much help out there. Thanks
again!