How to store mult. searchable choices in one field of each record

[Guest]

I have a field in an purchase order database that I am trying to create in
which I would like to be able select multiple records from another table into
that field and then be able to find that information by looking up the
purchase order and have it list all of those choices I made or by looking up
an individual choice from that field and be able to find the PO number. This
may be a little over my head but if there is a relatively simple way to do
this then I would really appreciate some help - thanks

 

[Tim Ferguson]

I would like to be able select multiple records from another table into
that field

Welcome to the wonderful world of relational databases... what you need
is some more tables, I'm afraid.

I guess you have a PurchaseOrder table already;

PurchaseOrders(*PONumber, DateSigned, ClientID, FullName, etc)

and a table of Products

Products(*ProductCode, FullDescription, CostPerItem, etc)

now you need a table of OrderLines to tie them together:

OrderLines(*PONumber, *ProductCode, NumberOfItems, AgreedPrice, etc)

The field OrderLines.PONumber is a foreign key referencing the
PurchaseOrders table -- that is to say, each OrderLine must have a valid
PurchaseOrder.PONumber; and the same applies to the
OrderLines.ProductCode field, which must contain the value of a valid
Products.ProductCode.

The stars (*) indicate fields that are Primary Keys; in the OrderLines
table the two fields create one compound Primary Key.

If you need help in creating these relationships in the Access
relationship window please post back again. Note also that this simple
design has some limitations, for example that each PurchaseOrder can only
have a particular product on it once, and so on. Again, if this is not
satisfactory, post back with more details.

Hope that helps


Tim F

 

[Guest]

I'm having a very similar problem here and it doesn't seem that the
additional table solves the problem very well.

I'm creating a problem tracking database. I have a table for each unique
problem report. One of the fields is the project(s) that each problem will
be assigned to. My database contains another table for projects. Each
project contains the people responsible for it.

Now the hard part... I want to assign a single problem report to MORE THAN
ONE project! I thought I could do it by creating a one to many
relationship. But I don't see how this works if a single field in a problem
report record can only contain 1 data value. I need that field to contain
many values... like an array.

How does the intermediate table solve this? I was thinking it would have to
contain multiple rows with duplicate problem report IDs for each project you
want to assign it to. But that makes it messy to enter the project
assignments on a single form when creating an issue. And it's even messier
when updating project assignments after an issue is created. You need a
seperate forms that let you view all projects for each unique ID and you need
to be able to add or delete to the list and then update the intermediate
table.

It would all be so much easier if a list box control on a form that lets you
select multiple values for a field (in my case, projects) would store ALL the
values you select and highlight. But that requires an array data type for a
table field. So what's the best way to handle this? Any suggestions are
welcome!

 

[Tim Ferguson]

I'm creating a problem tracking database. I have a table for each
unique problem report.

This sounds like a mistake. ProblemReports sounds like it should be a
single table, even there are more tables related to it that model
different types of report.

One of the fields is the project(s) that each
problem will be assigned to. My database contains another table for
projects. Each project contains the people responsible for it.

Don't understand this. Projects and People are nearly always in many-to-
many relationships, so you'll need a IsResponsibleFor table to model
that.

Now the hard part... I want to assign a single problem report to MORE
THAN ONE project!

That's easy enough. How many ProblemReports does a single Project have?

But I don't see how this works if a single field in a
problem report record can only contain 1 data value.

Well, no. The way you've phrased it, you'd need a FK field
Projects.ProblemReport that references the ProblemReports table.

How does the intermediate table solve this?

On the other hand, if a Project is assigned to more than one
ProblemReport, you'll need a table of Assignments which would contain
fields like
ProjectID FK references Projects,
ReportNumber FK references ProblemReports,
AssignedBy, SignedOffDate, etc etc

The first two are the important ones. This is straightforward M:M
relatioship modelling.

It would all be so much easier if a list box control on a form that
lets you select multiple values for a field

You can: it's easy and it's normal. BUT that is a user interface
consideration and that comes waaaaaaaaaay down the list of priorities
while you are still at the schema design stage.


HTH

Tim F

 

[Tim Ferguson]

Basically, I have about 200 trucks and I need sometimes to order parts
or service for them.

Okay: I think I misunderstood the scenario. Not that it's much different
though---

Ocassionally, I will order a part on a single PONumber for more
than one truck.

How do you fit one part onto more than one Truck? Or do you mean you
order parts for more than one truck on a single PONumber. This kind of
changes the nature of the relationship above: it's more like this...

PONumbers --< Parts >-- Trucks

or

PONumbers -< Parts -< CouldBeFittedTo >- Trucks


I would like to be able to choose

the truck or trucks for each PO from a drop down list in a form (using
the CTRL button to select multiple trucks) and then be able to go back
in a query and type in a single truck name and return all of the
PONumbers that are assigned to orders for that truck or a query that
will look up PONumbers and I can type in the PONumber and get back the
list of trucks that I input on a form.

Okay... kind of. There are probably three or four forms needed in there.

Maybe this is too complex for
me because I have "sketched" out what I want and I understand the
relationships but I can't seem to make it do this.

Perhaps you could share the diagram with us -- it would help me to
understand what you are trying to model.

I have never seen
an Access database that does this so I don't even know if it is
capable (or if I am).

Definitely yes to the first one; probably yes to the second!

I am looking for a simple solution.

Aren't we all? Seriously though, there is nothing here that is brain
surgery.

What is an NG?

A Newsgroup -- it's what you are interacting with at the moment <g>

I'm afraid I'm going to be away for a week, so I hope someone can pick
this up in the meantime. If you are still stuck when I come back, I'll be
happy to join back in.

Best wishes

Tim F