How should I tackle the following problem

[dplove]

I have two tables (Asset & PO (which there is a table for each PO). Within
the Asset table there is the serial number & PO number. Within the PO tables
there is the serial number with the rental rate of the computer. What I
would like to do is use the Asset table (serial number & PO number) and pull
the rental rate from the PO table. NOt sure what the simple way of doing
this is (reports or queries).

What suggestions do you have?

 

[Jeff Boyce]

Perhaps I've misunderstood...

Your description ("... a table for each PO") seems to be saying that every
new PO (?does this mean purchase order?- we aren't there) gets a table
created for it. If so, this is much more like a spreadsheet than a
relational database.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dplove]

Yes, it is a table for each Purchase Order because there are around 50 - 100
records for each Purchase Order.

 

[Jeff Boyce]

Before you go any further, step back!

Let's try an analogy ... to an Order Fulfillment system.

Each person could have many Orders (a one-to-many relationship).

Each Order could have multiple Order Detail "records" (again, one-to-many).

So, if we take your approach, we'd need to create a new table for every
Order (because it can have multiple "records"). And we'd need to create a
new table for every Person, because s/he could have multiple Orders.

Clearly, this approach will cause an immense maintenance burden for the
person keeping it going. Coincidentally, it sounds very much like how you'd
have to approach a solution ... if you were using a spreadsheet.

You will not get the best use of Access' relationally-oriented features and
functions if you insist on feeding it 'sheet data. And you'll create a lot
more work for yourself (and Access).

I recommended stepping back so you can have some time to look into the topic
of normalization. A well-normalized relational database will make your work
(and Access') a lot simpler.

For example, you could set up your tables something like:

tblPerson
PersonID
LastName
FirstName
DOB
... (other person-specific info)

tblPO
POID
PersonID (which Person does this PO relate to)
... (other PO-specific info, such as PO_Date, ...)

trelPODetail
PODetailID
POID (this "points" back to the PO that "owns" it)
... (PO Detail-specific info -- you mentioned 50-100 per PO, each
would get one row HERE)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have two tables (Asset & PO (which there is a table for each PO). Within
the Asset table there is the serial number & PO number. Within the PO tables
there is the serial number with the rental rate of the computer. What I
would like to do is use the Asset table (serial number & PO number) and pull
the rental rate from the PO table. NOt sure what the simple way of doing
this is (reports or queries).

What suggestions do you have?

You need *three* tables, not 1 + (number of PO):

PurchaseOrders
PONumber <Primary Key>
<information pertaining to the PO as a whole, e.g. date issued...)

Assets
SerialNumber <Primary Key>
Description
<other info about the asset>

PODetails
PONumber <link to PurchaseOrders>
SerialNumber <link to Assets>
<any information about this asset with respect to this PO>


John W. Vinson [MVP]

 

[dplove]

I like your idea, but one question:
If my Purchase orders have about 80 assets to each PO will it still be a
good idea to combine them into 1 table?

 

[John W. Vinson]

I like your idea, but one question:
If my Purchase orders have about 80 assets to each PO will it still be a
good idea to combine them into 1 table?

8, 80, 8000... yes.

"Fields are expensive, records are cheap".


John W. Vinson [MVP]