order history database design in access 2003

[shadowsong]

i'm trying to set up a database tracking order history and order
receipts for inventory items, but i'm not sure what should be in each
table, what should be in the link table, and what kind of key should be
used in each table.


each item is identified by either IJ number or IA number. not all items
have both numbers, but all have at least one. some parts are ordered
under IA number and received under IJ number, or vice versa. some items
are also referred to by multiple IJ or IA numbers, but i think i will
ignore that for now as i'm confused enough already. i would also like
to keep track of obsolete item numbers so they can be cross referenced
to a valid current item number

each item has multiple orders, each order contains multiple items.
unique order lines can be identified by an IA/IJ number + an order
number. date and quantity also needs to be recorded.

each unique order line can be received on more than one invoice, and
each invoice can contain the same order or item number (but not
order+item number) multiple times. quantity received and invoice date
is also recorded on this table.


i think i need to use compound keys as identifiers, but i haven't been
able to find much information on how they work in previous versions of
access, and none about using them in access 2003. i have a lot of
repeated information, but i can't figure out how to remove any of it
without losing identifying information.

here's my first stab at setting up tables, with possible compound keys
in brackets. all matching column names would be linked across tables:

[IANumber IJNumber] - link table. contains only part numbers.
[IANumber IJNumber Order] OrderDate OrderQty - order table.
[IANumber IJNumber Order Invoice] InvDate InvQty - receipts table.


this is my first time setting up a database, so i'm not sure i'm going
about it the most logical way. do i need more (or fewer) tables? is
there an easier way to set up the tables or the keys? have compound
keys been replaced by some other kind of key?

any websites or books i should take a look at?

 

[Josh]

In a post on 8/8/06, Subject: Problem designing tables: Too many fields
I saw where John Vinson posted this link:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Looks like some good ones.

Josh

 

[Allen Browne]

Presumably the IJ number alone uniquely identifies the product (if it has an
IJ), and so would an IA (if the product had one.) Therefore IJ and IA are
merely aliases for the product. In your database, consistently use the one
thing that *you* want to use as a unique identifier for the product - an
Autonumber if you wish. Then plug in whatever names someone else needs in
the context where you have to use it (such as on a report for ordering the
product.)

ProdCodeType table (one record for each alias, e.g. "IA", "IJ"):
ProdCodeTypeID Text primary key

Product table:
ProductID AutoNumber primary key
ProductName Text

ProductCode table:
ProdCode Text The actual value of the IJ/IA code for this
product.
ProdCodeTypeID Text Relates to ProdCodeType.ProdCodeTypeID
ProductID Number Relates to Product.ProductID
For primary key, use ProdCode + ProdCodeTypeID, so the same combination
cannot occur twice.

Example data:
ProductID ProdCodeTypeID ProdCode
1 IA 999xy
1 IJ abcde
2 IA 987zz

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i'm trying to set up a database tracking order history and order
receipts for inventory items, but i'm not sure what should be in each
table, what should be in the link table, and what kind of key should be
used in each table.


each item is identified by either IJ number or IA number. not all items
have both numbers, but all have at least one. some parts are ordered
under IA number and received under IJ number, or vice versa. some items
are also referred to by multiple IJ or IA numbers, but i think i will
ignore that for now as i'm confused enough already. i would also like
to keep track of obsolete item numbers so they can be cross referenced
to a valid current item number

each item has multiple orders, each order contains multiple items.
unique order lines can be identified by an IA/IJ number + an order
number. date and quantity also needs to be recorded.

each unique order line can be received on more than one invoice, and
each invoice can contain the same order or item number (but not
order+item number) multiple times. quantity received and invoice date
is also recorded on this table.


i think i need to use compound keys as identifiers, but i haven't been
able to find much information on how they work in previous versions of
access, and none about using them in access 2003. i have a lot of
repeated information, but i can't figure out how to remove any of it
without losing identifying information.

here's my first stab at setting up tables, with possible compound keys
in brackets. all matching column names would be linked across tables:

[IANumber IJNumber] - link table. contains only part numbers.
[IANumber IJNumber Order] OrderDate OrderQty - order table.
[IANumber IJNumber Order Invoice] InvDate InvQty - receipts table.


this is my first time setting up a database, so i'm not sure i'm going
about it the most logical way. do i need more (or fewer) tables? is
there an easier way to set up the tables or the keys? have compound
keys been replaced by some other kind of key?

any websites or books i should take a look at?

 

[Guest]

You might want to use the template that comes with Access for creating an
Order Entry database. That will set up tables/queries/forms/reports that you
can start using immediately, look at for ideas, or use after customizing it.
When you are first starting to use Access, it can seem like an overwhelming
task to get everything right from the beginning. There are several templates
available right on your computer, with a short wizard to help you set them up.

There are also templates that you can download from the Microsoft site.
This on is for managing orders.

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

 

[shadowsong]

If I wanted to run a query that would give me all open orders for a
particular item number, but put in a number other than the one set as
the item ID, would it still give me results? For example, say that for
one item, I have these numbers:

98412773 - IA number, item ID
98411025 - IA number, obsolete
0004000704 - IJ number

Our system uses the IA number, but when we receive orders, they have
the IJ number, so when putting a new entry in the order receipt table,
we would only have the IJ number available. New orders will be entered
under the current IA number, but old orders may already be in the
database under the obsolete number. How do I link all of these numbers
together so that I can search for one number and return all records
containing any of the three?

Note that item numbers (and thus our unique identifiers) get obsoleted
and replaced on a fairly regular basis - i would like to change the
numbers on the link table without having to change all of the entries
in the other tables.

 

[Guest]

Note that item numbers (and thus our unique identifiers) get obsoleted

and replaced on a fairly regular basis - i would like to change the
numbers on the link table without having to change all of the entries
in the other tables.

So, you have the same item, but it is identified differently over time?