List Help

[GoodOmens]

I have one table that I want a user to be able to select multiple options from a list to "add" to the table.

Therefore I created a third table that will have two colums: 1 for the unique ID from table 1 and one for the unique ID from table 2. No primary key will be chosen as multiple table2's can be attached to multiple table1's and vise versa.

In the form I want the user to be able to see a list of all the items from table 2 and be able to select multiple rows and click "add" to add the unique id's from the list (The user see's text but I need the id to be inserted instead) into table 3 thus creating a link between table 1 and 2.

So far I have gotten was being able to show the user a subform with a list of items from table 2 to add using the list box. However I can only highlight one record at a time and want to have the user be able to select multiple items and hit a button to add them.

Attached is my DB I am trying to use. The "SARF TABLE" table needs to be able to have multiple "SIAF" items to it through the "SIAF_SARF" table.

If you look at the form SIAF you will see the idea I am getting at. The original idea is to have this as a sub-form of the "assets" form where the user looking at a "SARF TABLE" item will be able to add multiple "SIAF" items by just slecting the name.

The SIAF table is not ideal I know but please help me I was given this project from someone who doesn't understand databases (I do but just not ACCESS) so I need help^^

 

[CEH]

First I would take a look at your relationships..... I see some that should be one to many.... and others.....Well....... "ContactID" to "Contact" ??????????

 

[spasticus]

im no expert but i had a look for you anyway and i got totally confused. which table is supposed to have multiple values related to one record and which table are those values stored?

did you normalise the database? aren't you supposed to? i dont think you can just add tables to do stuff without thinking it through.

i think the table you added with the two IDs should be a composite key otherwise your kind of defeating the concept of a relational database - which is to remove repeating data (am right aren't i? im not just making an idiot out of myself am i?)

 

[GoodOmens]

You are right but without getting to complex this data was not originally designed to be in a database but was setup to use excel. I've been tasked with making it more DB friendly :-(

I want a subform inside the ASSET form that allows you to select multiple SIAF items (from the SIAF table) and tag them to the SARF table (by putting the unique key for the siaf item in a table with the unique key from the SARF item).

Sorry for being confusing but I'm not a DB expert nor can I descibe things well :-(

 

[spasticus]

ok.. in asset (table) youve got assets, right? and in SIAF (table) you've got what? aren't assets usually owned by something not something (in this case a SIAF record) owning an asset (or multiple assets)?

 

[GoodOmens]

ok.. in asset (table) youve got assets, right? and in SIAF (table) you've got what? aren't assets usually owned by something not something (in this case a SIAF record) owning an asset (or multiple assets)?

Uhh to give you some background ... we are tracking documents (in this case what we refer to as a SARF) and need to know who is working on the document at a given time. MS had a built in pre-defined DB for tracking company assets that we modified to use in our case. So in short don't pay attention to names.

A SARF is submitted when a division needs to have their server(s) upgraded (with over 2k servers currently in deployment this happens on a regular basis) and this can refer to either 1 server or multiple (if its per say a cluster). Most of the entry is done through remedy (hence the remedy ID number in the DB) but we are using this internally to track information that remedy won't provide.

We need to have a way through a form to attach multiple lines in the SIAF table to one entry in the SARF.


EDIT:

OK to make things easier I updated my original zip to reflect some changes. In the Asset Form all I want to do is to be able to show in a list the MU's and DNS name in a list for the division selected in the form. Then I want to be able to highlight some and add it to the table so that for each highlighted item a entry in the SARF_SIAF table is created so that the current SIAF ID is entered and the SIAF ID is entered.

If thats not possible (doing multiple at once) then I would like to be able to highlight what I want to add and click a button to add it to a table (then maybe have another list that shows what is actually attached to the current SARF).

Thanks so much for trying to sort out my mess

 

[CEH]

I hate to say it.... But I'd start over. Your tables look fine.. but your relationships are not. First your Contact table should be related to your SIAF table on the correct field. ContactID to ContactID..... Without looking at it too closely it appears all other tables should also be related to this main table on a one to many relationship. I think if you do this you will find things flow a bit more like you want them to.