HELP! with database design

[Amelia]

I am trying to create a efficient purchase order, have multiple reasons for
this. Anyway I created a bunch of tables as directed by the Microsoft site
and tried to follow what they suggest. I then created relationships as our
old one does not have any and I want it to correlate with different tables. I
then created a query and form from the query. But maybe I have the query
wrong. I did one query with all my table data. I don't know alot about
subforms, but I am thinking this would be more efficient.

If you go to this url:
https://share.acrobat.com/adc/document.do?docid=7a079458-15f7-4352-acfe-d68f6628b8c9 it will show you the tables and relationships I have.

I then want to create a form that pulls the data out of the tables: i.e. I
have the departments table, i input the the departments in this table and now
when I create a PO for that department I want all PO's to be listed under the
department.

I hope this makes sense. Thank you to advance to anyone that responds and is
willing to walk me through this, so to speak.

 

[Duane Hookom]

Learn about forms and subforms. I generally keep a form's record source to 1
or 2 tables.

 

[Jeff Boyce]

Amelia

You use the term "efficient" but don't describe what that means to you.

Since you posted in the .tablesdbdesign newsgroup, consider posting your
current table structure.

Regards

Jeff Boyce
Microsoft Access MVP

 

[Amelia]

I thought that is my table design? (The url I posted) This is basically all I
have done so far. I have tried to create a form, but it did not work as I
wanted it so I am starting over again.

By efficent I just mean so if I go to a table I can see all orders
associated with it. I will do most of this by queries too, so maybe I don't
need the relationships? But I don't want to double entry. (this is also what
I mean by effiencent. I want a form that I can add to a table from the form
without having to go to the table form and entering.) Anyway if you don't
have any advice or think I should post somewhere else I will! Just let me
know! I have always had good advice from this site and want to continue
utilizing it as much as I can.

 

[Jeff Boyce]

Amelia

Access tables store data, Access form display it. As a general practice,
you'll want to get away from "going to your table to see all your orders".
That's what your forms are for.

"double entry" ...? That implies putting the same fact in more than once.
I agree, avoiding 'double entry' is desireable! (and in a well-normalized
relational database, probably not even necessary).

I don't understand what you mean by "... add ... from the form without
having to go to the table form and entering". Are you saying that you add
the record in a form, then DON'T find it in the table, so add it again
there? If so, then something's broken.

I'll rarely follow links for the same reason I rarely open attached files
.... I have no way to guarantee what I'm getting into.

Please consider posting a simplified description of your table structure
here. For example, a simplified database structure for an "enrollment"
database might look something like:

tblStudent
StudentID
FName
LName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Access MVP