How should I tackle the following problem

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?

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" wrote in message
news
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?

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

"Jeff Boyce" wrote:

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" wrote in message
news
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?

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

"dplove" wrote in message
...
Yes, it is a table for each Purchase Order because there are around 50 -
100
records for each Purchase Order.

"Jeff Boyce" wrote:

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" wrote in message
news
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?

On Mon, 17 Sep 2007 11:10:05 -0700, dplove
wrote:

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]

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" wrote:

On Mon, 17 Sep 2007 11:10:05 -0700, dplove
wrote:

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]

On Tue, 18 Sep 2007 07:16:03 -0700, dplove
wrote:

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]