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

[meghan]

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

 

[pgillis]

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

 

[meghan]

Okay, I have three tables:

1. PurchaseOrders that has all of the information we need to keep on each
Purchase Order generated except the information on our trucks and PONumber is
the primary key
2. TruckInfo which has the Truck Name, VIN, and Truck Type, where VIN is the
primary key
3. TruckOrders which has PONumber and VIN

I have gone into the relationships window and related the matching Record
Names.

Now I want to make a form to input new Purchase Orders and I would like two
things to happen, one I would like to be able to assign more than one VIN to
a PONumber and make that searchable by VIN or PONumber or Truck Name and I
would like to be able to use that VIN again on a different PONumber. Is that
possible? How do I put together the form so it does that? Do I use combo
boxes?

I would also like to select the VIN by selecting the truck name and having
the VIN and truck type fill in automatically. I was thinking I might do this
with an update button. Can you tell me how to do this?

Thanks, I appreciate any help you have time for.

Meghan

 

[Tim Ferguson]

1. PurchaseOrders that has all of the information we need to keep on
each Purchase Order generated except the information on our trucks and
PONumber is the primary key

2. TruckInfo which has the Truck Name, VIN, and Truck Type, where VIN
is the primary key

3. TruckOrders which has PONumber and VIN

Now I want to make a form to input new Purchase Orders and I would
like two things to happen, one I would like to be able to assign more
than one VIN to a PONumber ....
and I would like to be able to use that VIN again on a
different PONumber.

Then it's a many-to-many relationship, but that is okay because it's
already implemented in the TruckOrders table.

How do I put together the formso it does that? Do I use combo boxes?

Go ask your users - what do they do? Do they start off with a truck and
fill it with orders? Or do they process and order and have a list of
trucks with available space? Or do they start by routing the whole lot
using some kind of algorithm? What they do is what you need to provide:
once you understand their process, then the things on screen usually
becomes pretty obvious(*).

I would also like to select the VIN by selecting the truck name and
having the VIN and truck type fill in automatically.

There is no "filling in" to do: once you have put the VIN in the
appropriate TruckingOrders.VanNumber field then the usual queries will
display all the names and things wherever you want them.

(*) Just because knowing _what_ you want to do is easy, it doesn't mean
knowing _how_ to do it is always simple... <g> but that is what the NGs
here are best for.

Hope that helps


Tim F

 

[meghan]

Basically, I have about 200 trucks and I need sometimes to order parts or
service for them. Ocassionally, I will order a part on a single PONumber for
more than one truck. 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. 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. I have
never seen an Access database that does this so I don't even know if it is
capable (or if I am). I am looking for a simple solution.

What is an NG?

Thanks for your continued help.

Meghan

 

[meghan]

I should also add that I have built a form to enter new orders and I have the
source table autogenerating PONumbers. In the form I have put in several
combo boxes to get lists from other tables - which work fine - and I have put
a list box in it for TruckVIN and set it to extended in the MultiSelect
property. I am able to highlight several items as expected but they don't
seem to store in the TruckVIN field in any way even though I have put it in
as the Control Source and bound the TruckVin column in my TruckInfo Table in
the properties of the list box in the form. This is where I am stuck. I am
guessing that I need to change the properties of that TruckVIN record in my
TruckOrders table somehow but I can't find any information on this.

Thanks again,

Meghan

 

[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