How should I tackle the following problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Yes, it is a table for each Purchase Order because there are around 50 - 100
records for each Purchase Order.
 
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
 
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?
Click to expand...

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?
 
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?
Click to expand...

8, 80, 8000... yes.

"Fields are expensive, records are cheap".


John W. Vinson [MVP]
 
Back
Top