Multiple values in Access 2002?

Question

I am creating a small database, and am having a slight problem. It is a database which holds details of tasks undertaken by a company (a small computer repair firm), along with details of the customers and the stock held.

I need to be able to add the stock used to complete each task, onto the task form (and table). I have created a look-up in the 'Stock' column on the tasks table, and am able to select one item of stock. However, I need to be able to select more than one item for each task.

Is there a relatively simple way to do this in 2002? I know it can be done in 2007, but I don't have that version on my PC.

Thanks in advance!

Answer 1

You need a many-to-many table. By having a stock lookup in the task table, you can link a task to only one stock item. the many-to-many lets you like a task row to many stock item rows and vice versa.



Your current arrangement:



Task_Table has field stock_item which referes to Stock_Table primary_key.



New arrangement:



(damnit! YA destroys my nice layout)



In your task table, delete the stock reference

Make a new stable Stock_For_Task

add 3 fields

task - reference to Task table primary key

stock - reference to Stock table primary key

count - number of stock items used.



In the Stock_For_Task table, make the combination of the two foreign key columns unique.



Now where you have the stock lookup in the task form, you can create a multi-row table. You can add many stock items.



It's the same amount of difficulty in any version of Access.

Answer 2

To do this, you need to use an intermediary table. You need a table table with the follow schema:



1. LineID (auto-number)

2. StockID

3. TaskID



Create automatic dropdowns for values StockID and TaskID (use Access' wizard).



Finally, create a sub-form in your Task form. This will show multiple values of Stock for the Task you have selected. In your subform, you can auto-populate the StockID value by setting the Default Value to something like [Forms]![MyForm].[MyValue]. The subform wizard in Access will create an auto-filter automatically so that when you browser through your Tasks, the subform will be refreshed.



This can easily be done in both 2002 and 2007. Good luck!

Answer 3

Are you writing sq. or doing this interior the clothier? clothier occasion: Max([FieldA]) & Max([container B]) & Max([container C]) sq. occasion: go with max(field1), max(field2), max(field3) from YourTable yet another component to bear in techniques: it sounds as though out of your question which you're attempting to concatonate (append) all then values right into a single column. some could evaluate this undesirable undesirable form to concatonate the fields interior the question. a greater helpful physique of techniques migh be to return the values as their guy or woman columns and them string them jointly on your record, form or regardless of that's you're construction. additionally: in case you will possibly choose for to get the columns to have greater significant names than "Expr1" you could create an alias for the column on your resultset. interior the clothier you do some thing like this... MaxEndDate : Max(EndDate) In sq. you will possibly try this... go with max(EndDate) as EndDate from MyTable