Multivalued Field Functionality in Access 2003

Hello All,

I am in a bit of a dilemma. I am trying to design an Access Database
that quite frankly needs multivalued fields so that I can use a
checked listbox, but I am limited to Access 2003 and cannot use Access
2007, in which I already have a fully working model.

I am building a database with a central table, called GENERAL. There
are 5 additional forms, DRUGNAME, ADDITIONALMEDICATIONS,
INJURYINFORMATION, etc. Each one contains one field which has a list.
For example the DrugName table would contain a list of all the
prescription drugs that the person possibly could have been on.

In 2007 it was great to be able to go on and have the general form
with name fields, address fields, etc. Then to be able to have all the
checked list boxes on the general field, one for the DrugName, one for
InjuryInformation, etc. The person could create a record, select the
prescription drugs the person was on, select multiple options for
injuries he might have had, etc. All this information was recorded to
his record in comma-delineated format which was easily accessible from
a query.

However, we have to use 2003. I want to make this as simple as
possible as it will be a bunch of non-computer literate individuals
using it, who may need to make changes to it, etc. So I don't want to
have to write custom SQL statements for each query, custom VBA code
for each query, etc. I want to make this as much point-and-click as
possible, even at the sacrifice of perfect database design technique.
This has to be usable by somebody who has never touched Access before.

Suggestions? How do I port multivalued field techniques to Access
2003?

Thanks,
Joseph

0
Joseph
6/13/2007 6:13:35 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
2014 Views

Similar Articles

[PageSpeed] 26

There's no way to port multi-valued fields to Access 2003 or any other prior 
version of Access.

You have no choice but to implement the proper multi-table approach, which 
is what Access 2007 is doing under the covers (although they're not exposing 
the additional tables, as we've repeatedly told them they should)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Joseph Irvine" <Jkeagle13@aol.com> wrote in message 
news:1181758415.490614.282360@q19g2000prn.googlegroups.com...
> Hello All,
>
> I am in a bit of a dilemma. I am trying to design an Access Database
> that quite frankly needs multivalued fields so that I can use a
> checked listbox, but I am limited to Access 2003 and cannot use Access
> 2007, in which I already have a fully working model.
>
> I am building a database with a central table, called GENERAL. There
> are 5 additional forms, DRUGNAME, ADDITIONALMEDICATIONS,
> INJURYINFORMATION, etc. Each one contains one field which has a list.
> For example the DrugName table would contain a list of all the
> prescription drugs that the person possibly could have been on.
>
> In 2007 it was great to be able to go on and have the general form
> with name fields, address fields, etc. Then to be able to have all the
> checked list boxes on the general field, one for the DrugName, one for
> InjuryInformation, etc. The person could create a record, select the
> prescription drugs the person was on, select multiple options for
> injuries he might have had, etc. All this information was recorded to
> his record in comma-delineated format which was easily accessible from
> a query.
>
> However, we have to use 2003. I want to make this as simple as
> possible as it will be a bunch of non-computer literate individuals
> using it, who may need to make changes to it, etc. So I don't want to
> have to write custom SQL statements for each query, custom VBA code
> for each query, etc. I want to make this as much point-and-click as
> possible, even at the sacrifice of perfect database design technique.
> This has to be usable by somebody who has never touched Access before.
>
> Suggestions? How do I port multivalued field techniques to Access
> 2003?
>
> Thanks,
> Joseph
> 


0
Douglas
6/13/2007 6:24:27 PM
Hello,

Still not sure that makes sense to me. So I go and have all the
multiple tables, great. How do I mimic the functionality of the
checked list box though in Access 2003 though? They need to be able to
dynamically update the checked list box which could contain 100+
entries that they can then choose from on the main GENERAL form. They
need to be able to select multiple options for each record.

Thanks,
Joseph

*************************************************
On Jun 13, 11:24 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> There's no way to port multi-valued fields to Access 2003 or any other prior
> version of Access.
>
> You have no choice but to implement the proper multi-table approach, which
> is what Access 2007 is doing under the covers (although they're not exposing
> the additional tables, as we've repeatedly told them they should)
>
> --
> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> (no e-mails, please!)
>
> "Joseph Irvine" <Jkeagl...@aol.com> wrote in message
>
> news:1181758415.490614.282360@q19g2000prn.googlegroups.com...
>
>
>
> > Hello All,
>
> > I am in a bit of a dilemma. I am trying to design an Access Database
> > that quite frankly needs multivalued fields so that I can use a
> > checked listbox, but I am limited to Access 2003 and cannot use Access
> > 2007, in which I already have a fully working model.
>
> > I am building a database with a central table, called GENERAL. There
> > are 5 additional forms, DRUGNAME, ADDITIONALMEDICATIONS,
> > INJURYINFORMATION, etc. Each one contains one field which has a list.
> > For example the DrugName table would contain a list of all the
> > prescription drugs that the person possibly could have been on.
>
> > In 2007 it was great to be able to go on and have the general form
> > with name fields, address fields, etc. Then to be able to have all the
> > checked list boxes on the general field, one for the DrugName, one for
> > InjuryInformation, etc. The person could create a record, select the
> > prescription drugs the person was on, select multiple options for
> > injuries he might have had, etc. All this information was recorded to
> > his record in comma-delineated format which was easily accessible from
> > a query.
>
> > However, we have to use 2003. I want to make this as simple as
> > possible as it will be a bunch of non-computer literate individuals
> > using it, who may need to make changes to it, etc. So I don't want to
> > have to write custom SQL statements for each query, custom VBA code
> > for each query, etc. I want to make this as much point-and-click as
> > possible, even at the sacrifice of perfect database design technique.
> > This has to be usable by somebody who has never touched Access before.
>
> > Suggestions? How do I port multivalued field techniques to Access
> > 2003?
>
> > Thanks,
> > Joseph- Hide quoted text -
>
> - Show quoted text -


0
Joseph
6/13/2007 6:44:19 PM
Add a boolean field to the table and use a continuous form (showing the 
boolean field) as a subform, rather than listbox.

And you're not storing multiple values for each record: you're storing 
multiple rows in the junction table.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Joseph Irvine" <Jkeagle13@aol.com> wrote in message 
news:1181760259.876528.138130@e26g2000pro.googlegroups.com...
> Hello,
>
> Still not sure that makes sense to me. So I go and have all the
> multiple tables, great. How do I mimic the functionality of the
> checked list box though in Access 2003 though? They need to be able to
> dynamically update the checked list box which could contain 100+
> entries that they can then choose from on the main GENERAL form. They
> need to be able to select multiple options for each record.
>
> Thanks,
> Joseph
>
> *************************************************
> On Jun 13, 11:24 am, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>> There's no way to port multi-valued fields to Access 2003 or any other 
>> prior
>> version of Access.
>>
>> You have no choice but to implement the proper multi-table approach, 
>> which
>> is what Access 2007 is doing under the covers (although they're not 
>> exposing
>> the additional tables, as we've repeatedly told them they should)
>>
>> --
>> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
>> (no e-mails, please!)
>>
>> "Joseph Irvine" <Jkeagl...@aol.com> wrote in message
>>
>> news:1181758415.490614.282360@q19g2000prn.googlegroups.com...
>>
>>
>>
>> > Hello All,
>>
>> > I am in a bit of a dilemma. I am trying to design an Access Database
>> > that quite frankly needs multivalued fields so that I can use a
>> > checked listbox, but I am limited to Access 2003 and cannot use Access
>> > 2007, in which I already have a fully working model.
>>
>> > I am building a database with a central table, called GENERAL. There
>> > are 5 additional forms, DRUGNAME, ADDITIONALMEDICATIONS,
>> > INJURYINFORMATION, etc. Each one contains one field which has a list.
>> > For example the DrugName table would contain a list of all the
>> > prescription drugs that the person possibly could have been on.
>>
>> > In 2007 it was great to be able to go on and have the general form
>> > with name fields, address fields, etc. Then to be able to have all the
>> > checked list boxes on the general field, one for the DrugName, one for
>> > InjuryInformation, etc. The person could create a record, select the
>> > prescription drugs the person was on, select multiple options for
>> > injuries he might have had, etc. All this information was recorded to
>> > his record in comma-delineated format which was easily accessible from
>> > a query.
>>
>> > However, we have to use 2003. I want to make this as simple as
>> > possible as it will be a bunch of non-computer literate individuals
>> > using it, who may need to make changes to it, etc. So I don't want to
>> > have to write custom SQL statements for each query, custom VBA code
>> > for each query, etc. I want to make this as much point-and-click as
>> > possible, even at the sacrifice of perfect database design technique.
>> > This has to be usable by somebody who has never touched Access before.
>>
>> > Suggestions? How do I port multivalued field techniques to Access
>> > 2003?
>>
>> > Thanks,
>> > Joseph- Hide quoted text -
>>
>> - Show quoted text -
>
> 


0
Douglas
6/13/2007 7:56:28 PM
Reply:

Similar Artilces:

turn off automatic hyperlink Publisher 2003
How do I avoid automatic formatting of e-mail addresses to stupid blue type with stupid underlining? High-light the email, Insert, Hyperlink, Remove Link. -- Mary Sauer MVP http://msauer.mvps.org/ "Mark" <Mark@discussions.microsoft.com> wrote in message news:2E198309-1F6A-46F2-94A5-C12D64C7452E@microsoft.com... > How do I avoid automatic formatting of e-mail addresses to stupid blue type > with stupid underlining? Or you could change your color scheme to a custom color scheme, and change the hyperlink (and followed hyperlink) color to match your ...

sendkeys action requires utility add in access 2003
I Know that this has been asked before. My question is why it will not pop up on one computer but will on another. The same actions being performed. Any input will be helpful. Thanks Craig "Craig" <Craig@discussions.microsoft.com> wrote in message news:5A3A76CB-9556-4B9D-B85E-39F13771D0C8@microsoft.com... >I Know that this has been asked before. My question is why it will not >pop > up on one computer but will on another. The same actions being performed. > Any input will be helpful. > > Thanks Craig You'll have to give a few more details a...

how can i access CRM from ouside LAN
Hi All, if i have a implementation of MS CRM 3 in my office. All users of CRM can access it from within the LAN through windows authentication. How can they access it from their home or some remote position. i dont want to use offline CRM/outlook functionality. Can i access MS CRM 3 online from anywhere on internet, something like assignin it some IP. Please explain. You can serve it out just as you would any other website in IIS. Make sure you use an SSL certificate so your CRM data is protected behind https. If your CRM server has a fixed external IP address or is reachable via a URL, y...

Outlook 2003
Is there a way to lock Outlook down, so that only the email features are available to users? ------------------------------------------------ ~~ Message posted from http://www.OutlookForum.com/ ~~ View and post usenet messages directly from http://www.OutlookForum.com/ ...

EXCHANGE 2003 and VISA Ultimate
I am having an issue running Exchange connected to a VISTA machine and wondering if anyone can guide me on this. I have three servers in my network, one running the server files and SQL, one running Exchange and one running backups. All are 2003 Enterprise. My desktop is running VISTA Ultimate, and to date have had no issues that could to be handled. Today, however, while I am still receiving email I cannot send anything through OUTLOOK 2007 with getting a message saying that I am not authorized to send mail. This has been working fine to date, so a little confused as to what is happen...

2003 wont send new mail from default account
I am running Office Pro 2003 SP1 I have read several postings and accept that Outlook will reply to mails via the account the was received from. However, i have added a new account and set it as default, but mail still gets sent from my other account. This even happens when i select the account from within the new mail... the message says 'This message will be sent via mail.mydefaultdomain.com'.. but its actually gets sent via the non default account.. How do i sort this as it is really annoying.. On 19 Jan 2005 14:44:35 -0800, Winshent wrote: > I am running Office Pro 2003 SP1...

Can't delete additional "Personel Folders Outlook 2003
I followed a backup procedure form MS Office site that said to check the resulting PST file by opening in Outlook. I did it twice. Well, it created two other "Personal Folder" at the bottom of the Mail/All Mail Folders pane that I cannot delete. I moved or deleted the two backed up PST files so there is no data for the folders to relate to. How can I get rid of these erroneous folders? Gary Create a new mail profile - yours is now corrupt. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here)...

Business Contact Manager 2003 Database Update
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C3962C.16F7ECA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear All, I have what I believe a serious problem here. I have been using Outlook = 2003 with Business Contact Manager when it was in Beta 2 and even after = the Technical Refresh. Now, after both are released, I need to use the = BCM database that I have populated. The problem is that the database = cannot be backed up and then restored to the RTM BCM. It keeps telling = me that the version is differen...

moving Exchange 2003 mailboxes from multiple domains to one new do
My compnay has Exchange servers in three different domains. We have built a new domain and want to move all mailboxes into one exchange server in the new domain. We have let mailbox size get out of hand and some users have 2GB mailbox size. exporting old mailbox data to PST and importing into new exchange server is possible but would take forever (hundreds of users). any ideas how to streamline this process? or an alternate method of attack? On Wed, 28 Mar 2007 08:24:06 -0700, Kemper <Kemper@discussions.microsoft.com> wrote: >My compnay has Exchange servers in three different d...

Can I convert an Outlook 2000 PST to Outlook 2003
We have upgraded from Outlook 2000 to Outlook 2003. Is there a fast way to convert my old PST files to Outlook 2003 so that they are not limited to 2 GB size? I don't want to have to go through all of the folders on 30 computers and drag one folder at a time from the old file to the new file. Thanks, Joe I have had pretty good luck importing. Of course, when you import, you = will no longer have access to any custom forms, views, or Rules Wizard = rules.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.h...

User not accessed the system since last 2 months
Is there any script available to determine accounts that have not logged in for the past x months. Please provide us the link and and guide line how to use that ? Thanks ...

Outlook Epxress 2003 (SBE)
Hi, This program keeps hanging on loading . Can anyone help me out ? Rohit rohit_chandni wrote: > This program keeps hanging on loading . Can anyone help me out ? > Rohit, Outlook or Outlook Express? -- f.h. Hi F.H, It is Outlook Express 2003 (Small Business Edition). Can you help me out on this ? Rgds, Rohit "F.H. Muffman" wrote: > rohit_chandni wrote: > > This program keeps hanging on loading . Can anyone help me out ? > > > > Rohit, > > Outlook or Outlook Express? > > -- > f.h. > > > Outlook Express is pa...

Accessing Custom fields (propery)
Hi everyone, Any one who can tell me how to access custom fields using the CRM web service? Thanlks, ...

General MS Access Programming
I have spent almost 46 years learning, using and teaching different programming languages and quite frankly, I have never found anything more confusing than trying to program functions in MS Access. I have given up trying to learn how by studying other people's coding examples. What is available to help learn the subject in the form of self-study courses? Thanks, George I'm puzzled by this, given that Access uses the same VBA as the rest of the Office products, and that it's virtually indistinguishable from "Classic" VB. Jeff Conrad has a fabulous list of Acces...

Copy one field in two or more fields
Hi i have a tableA with one field and another tableB with two fields. TableA Name a b c d e f .. ... I want copy all values alternatively, in an another tableB with two fields. The results should be: TableB NameA NameB a b c d e e .. Anyone can be help me ? Thank you in advance Best regards Diego -- Message posted via http://www.accessmonster.com Diego via AccessMonster.com wrote: >i have a tableA with one field and another tableB with two fields. > >TableA >Name > a > b > c ...

Exchange 2000/2003 Routing Problems
I recently added a new Exchange 2003 server to our 1 server Exchange 2000 org. I am able to send mail from the new Exchange 2003 server to the 2000 server. But when I send to the 2003 server the mail does not go delivered. I am assuming this is a routing issue. How can I troubleshoot this? Should each server be in a seperate routing group and use a routing group connector? They are currently i n the same routing group. Thanks, Forest In case anyone has this problem my STMP connector was setup to use a smart relay. I had to check the box that said attempt local delivery before using...

Field names?
Can anyone tell me what type field and the field names are for the Vote Tracking Recipient and Response? I do see one called "Voting Response" under "All Mail Fields", however that is only showing me my vote responses to someone else, i.e. not thost that I've sent out for a vote myself. Please help, Steve The Voting Response field works for me in my Inbox when I recieve votes in response to an email I send out with Voting Buttons on it. Judy Gleeson MVP Outlook Trainer and Consultant read my articles here: www.judygleeson.com Canberra, Australia how to post qu...

How to copy a field of A excel file to B Excel file automatically?
I need to copy some fields of A.xls to B.xls every night at 12:00. how can I do that? I'd try one of two methods. First, use the MS Windows "scheduler" to fire off you spreadsheet at midnight (make sure to leave the computer turned on) and have a VBA macro triggered when the workbook is opened to perform the data movement. A second, albeit more difficult method, would be to write VBA code to structure an internal timer that would watch for midnight. In this case, you'd have to open the workbook before going home for the day, and activate the macro that sets ...

web access autentication
hi every body i have installed an exchange 2003 in my domain and enable web access on it. every useres who want to access with web have to enter servers FQDN/exchange to browse it . after that a user name and password box apeared to them . im looking for a way if users login to domain , web access dosenot looking for autentication and after browsing server ip address that box dose not shown to them ! please lead me if it possible! In fact, the two last sentences are hard to understand. If my understanding is correct, you may try, http://exchangename/exchange or if you have only one e...

about function keys
This is a multi-part message in MIME format. ------=_NextPart_000_0149_01CA656F.25EEE970 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I went out a got a new keyboard. guess what? the function keys don't work on the new one. could this be anything with windows 7 by any chance????? bonnie ------=_NextPart_000_0149_01CA656F.25EEE970 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HT...

Pivot Table 2007 display field only in Grand Total
Dear Excel experts, I have a Pivot Table (Excel 2007). As an example, the table consists of two Colum Labels (Expense 1 and Expense 2). Values under the Column Labels are Actual Expense, Budget and Full Year budget. All Values (Actual Expense, Budget and Full Year budget) are totaled in the Grand Total. However, Management want to see Full Year Budget only in Grand Total (not under Expenses 1 and Expense 2). I was manually hiding those values. Is there a way to automate this? Thanks ...

Exch/Outlook 2003 Delegates
I have an Exchange 2003 Standard Server with Outlook 2003 clients. My users have noticed that they cannot properly assign delegates. They can choose delegates and assign the permissions normally, but when they quit and restart Outlook, the delegates are there but with "None" as their permissions. What might cause this? Hi Tim, Thank you for posting here. According to the information in your post, I suggest you use the following message to trouble shoot this issue: 1. New Outlook profile ============= Please create a new profile to isolate the issue. For the steps on how to cr...

Exchange 2003 gagging on mass mailing
There have been a couple of occasions when clients have not used distribution lists or bcc and stuffed hundreds of addresses in the To: field. When they send, I've noticed my XP desktop locking and my Outlook 2003 client "not responding" for a few seconds. Exchange is relaying all external mail through a local campus server. One instance resulted in the relay server rejecting based on a header size limit, but the latest instance was a text email with no attachments send to ~ 400 recipients. I have set a 500 recipient limit on the Exchange server and contacted the users a...

Speech Function
Can the speech function be added to Excel version 9.0.2719? Or is it already there? Speech was added in xl2002. You may want to look for a third part voice recognition program. Gayle wrote: > > Can the speech function be added to Excel version 9.0.2719? Or is it already > there? -- Dave Peterson ec35720@msn.com ...

Incorrect printing of Visio 2003 drawings in Word 2003
I got the error described in KB 827865 http://support.microsoft.com/default.aspx?scid=kb;en-us;827865&Product=visio2003 with Visio 2003 and Word 2003, in Embedded Visio Objects as well as in placed .emf files or copy/pasted images using Enhanced Metafile format and all this while using just normal latin characters. I do already use current drivers and the problem appears when creating PDFs as well as when printing to a PostScript printer. Any help would be very much appreciated! Matthias Have you tried inserting them as Word Drawing objects? That works best for me. Randall Arnold ...