Autonumber by PO # ID

[Guest]

Is it possible that if in a table, there's 5+ different customers, and each
customer has a PO #, and each PO # has a number of lines for the different
products ordered, if I decide that I want to add a new product to a PO, that
it would increment the line number to the next preceding number.

Now here's the catch. All of the PO data is generated through a MRP system
that I have, all the Orders are imported once a day to the mdb to analyze the
orders. So the orders already come with a line number, so these line numbers
will be the ones I need to consider, because if I add any new items, I need
to feed the new item numbers back into the MRP system with the next preceding
line on it's corresponding order. Otherwise the MRP system won't recognize
it as an update per say.

 

[Joseph Meehan]

Is it possible that if in a table, there's 5+ different customers,
and each customer has a PO #, and each PO # has a number of lines for
the different products ordered,

That sounds like a violation of normalization.

It sounds like you should have a table for customers (five records).
Normally I would expect customers to have one or more PO# so you would have
a second table for PO#'s. Even if you only have one PO# for any customer
now, unless it is not possible to have a second, you want to have a separate
table from PO#s. Next you would have a third table for products ordered.

This may sound like a lot of work and is certainly not the way you would
handle this in a spreadsheet, but you are not using a database. It will
save you a lot of grief to get this right now and not later.

Each table has a relationship to one or more other tables.

if I decide that I want to add a new
product to a PO, that it would increment the line number to the next
preceding number.

Now here's the catch. All of the PO data is generated through a MRP
system that I have, all the Orders are imported once a day to the mdb
to analyze the orders. So the orders already come with a line
number,

Tables don't have line numbers. You can generate numbers but they are
not line numbers. Data in a table does not have an order. You create the
order based on a filed. You sort the data in the order that you like base
on the field or fields you need for that specific use. This all begins to
make sense when you start thinking about Access as a relational database
rather than a spreadsheet.

so these line numbers will be the ones I need to consider,
because if I add any new items, I need to feed the new item numbers
back into the MRP system with the next preceding line on it's
corresponding order. Otherwise the MRP system won't recognize it as
an update per say.

From the limited information you have presented, I might suggest that
Excel Spreadsheet may be a better tool for this function than Access
database.

I know the above may sound .. Well harsh or less than kind. However I
do not mean it to. However it is very important to start with an
understanding of how a database should be structured from the start. Those
of us who have been there and done that just want to keep you from making
the same errors we made.

 

[Guest]

Thanks Joseph, I'm going to work on setting it all up as you mentioned. And
this is a databse, I'm working on Access 2003