Table Design & Relationship problem...

[Niko]

Hi all, I'll try to explain as much as I can what I want to do.
I want an incoming notice paper to be in an access file along with
some data...

So we got one table with fields ( like sender,date,subject, etc.) that
can take one-single value and another one (the Departement it belongs
to) which can take multiple values (like IT,financial..blah).

I want to make a form where we can enter all those information.So, I
went and made that form with a subform that I made it to seek the
values from the Deps table and made it multi-select.The thing is all
wrong...

I think I must have two tables with Department information.One which
will have all the Deps and a form to edit that records and another one
which will have the related notice papers and deps...But I don't know
how to make that

Hope someone can find the time to point me to the right direction..!

 

[Tim Ferguson]

(e-mail address removed) (Niko) wrote in @posting.google.com:

So we got one table with fields ( like sender,date,subject, etc.) that
can take one-single value and another one (the Departement it belongs
to) which can take multiple values (like IT,financial..blah).

I am not quite sure what you mean here:- does each Department have many
Papers, or does each Paper belong to many Departments? Or, indeed do some
Papers belong to many Departments, and some Departments have more than one
Paper?

"Multiple values" is something of a dirty word in database circles, but
what you want is probably not very hard to do, once you clarify what that
is.

HTH


Tim F

 

[Maria G]

Have you tried using the Wizard to make your subform for you? It is essential
that the two tables each have a field that is identical in both e.g. ID
number. This will mean that the id number (if set up as Auto Number in your
Main form) will be assigned to every entry you make in the subform.

What is it that does not 'work' on your subform?

 

[Niko]

First of all I would like to thank you both Tim and Maria for your
time...

I will try to make it more clear.
We have two tables, one Paper which has related info(fields) like
Sender,Date,Subject etc. and the second one Departments that has
listed all the Departments of the company.
Now, the employee must have a form to enter all the info for the Paper
including the task to "assign" it to one or more Departments.That
means that one Paper can belong to one or more Departments and one
Department can have assigned many Papers.That is a many to many
relationship, right? ( just read it! )

A detail I want to do is that a Paper cannot have in it's Department
value the same Department twice.

The problem I'm facing is that up to now I havent figured out a way to
make the subform have a drop-down list of fixed values ( the
Departments of the company )
and to update another table with the "assigned" Departments of one
Paper.

Waiting for your answers and...for the ideas to come into my head

 

[Tim Ferguson]

(e-mail address removed) (Niko) wrote in @posting.google.com:

That
means that one Paper can belong to one or more Departments and one
Department can have assigned many Papers.That is a many to many
relationship, right? ( just read it! )

Correct: that also means you need a third table called something like
IsAssignedTo to hold the linking data. And there is _no_ Department field
in the Papers table, and no Paper field in the Departments table. The new
table looks a bit like

IsAssignedTo
------------
DeptID PaperNumber AssignDate
===== =========== -----------
ENG 1023 12/12/2003
ENG 1996 10/10/2003
FRA 1023 11/11/2003
BIO 1996 09/09/2003



The last field is optional, but you may well want to store stuff about
the actual assignments, like who assigned it or when it was delivered, or
whatever. The key (pun intended) is to make the combination {DeptID,
PaperNumber} into the Primary Key of the table -- this ensures that each
paper can be assigned to a particular department at most once, and vice
versa.

As far as the UI is concerned, there are a number of ways you can display
it. You can use a subform, based on a query that joins the IsAssignedTo
and Departments, still using a combo box to select the
IsAssingedTo.DeptID but showing the Departments.FullName and so on...
it's easier to do than to describe! The really posh solution involves
drag-and-drop and stuff, but let's walk before we run OK?

Hope that helps


Tim F

 

[Niko]

Well Tim,

just a few minutes after I posted my question I took a look at the
Northwind db and found my solution just by seeing how the Orders,
Order Details and Products tables interact...

That's what I wanted to do! The problem is that I havent read a book
about Access and I went straight on building a db!

Tim thanks for your time I appreciate it..

 

[Tim Ferguson]

(e-mail address removed) (Niko) wrote in

That's what I wanted to do! The problem is that I havent read a book
about Access and I went straight on building a db!

Don't feel bad -- I hope I didn't sound as if I was putting you down. One
of my big beefs with Access is the way that it's marketted as an end-user
app. With Word, you can just fire it up and start typing, and more-or-
less the same applies with Excel and Powerpoint (although it's easy to
tell presentations that have been planned before hand!).

Database design is very different, however, and you really need to have
put in at least half[1] your design work before firing up Access for the
first time. There is a significant knowledge base to absorb before you
can do anything useful. It's nothing magic, but without it Access
degenerates to merely a complex and frustrating cross between Cardfile
and Sympbony. With some understanding of R theory, however, Access
becomes a platform on which to create some really impressive and robust
solutions. You'll find out how much fun that can be

All the best

Tim F

[1] Some sources say two-thirds or even up to 90%...

 

[Brett Collings [429338]]

Great post Tim!

Brett

(e-mail address removed) (Niko) wrote in

That's what I wanted to do! The problem is that I havent read a book
about Access and I went straight on building a db!

Don't feel bad -- I hope I didn't sound as if I was putting you down. One
of my big beefs with Access is the way that it's marketted as an end-user
app. With Word, you can just fire it up and start typing, and more-or-
less the same applies with Excel and Powerpoint (although it's easy to
tell presentations that have been planned before hand!).

Database design is very different, however, and you really need to have
put in at least half[1] your design work before firing up Access for the
first time. There is a significant knowledge base to absorb before you
can do anything useful. It's nothing magic, but without it Access
degenerates to merely a complex and frustrating cross between Cardfile
and Sympbony. With some understanding of R theory, however, Access
becomes a platform on which to create some really impressive and robust
solutions. You'll find out how much fun that can be

All the best

Tim F

[1] Some sources say two-thirds or even up to 90%...

Cheers,
Brett