skulmat said:
How can I modify a northwind database so that instead of thier just being a
plain automatically generated order number key for a new order,(when iclick
"new") Rather it will be a "two part key"
"OrderNumber*InvoiceNumber" so i
can create "subs" of an order, so when i input the order, I can choose to
create a new "sub of the order" or a completely new order. Thankx
The unfortunate part is, we didn't make the Order table ourselves, and
so we have no idea what the name of the Primary Key is. It was
undoubtedly randomly generated by Access itself when the original
designers used the UI to create the sample database in the first
place.
We could play around with writing DDL to ADD and DROP columns and
CONSTRAINTS, and shift the data around, but that would be a pain in
the neck (because of the above).
First:
1) Go to the Tables list.
2) Right-click on the Orders table.
3) Chose Save As.
4) Type in TestOrders, and click OK.
Go into Design View for the Orders Table.
1) Right-click on the grey-square immediately to the left of
CustomerID.
2) From the pop-up menu: chose Insert Rows.
3) Type in InvoiceNbr.
4) Change Data Type to Number.
5) Change Decimal Places to 0.
6) cntl-s to save the table.
7) Close the table.
Create an UPDATE query to load the Invoice Numbers that you want in
that column.
Execute that query.
Now:
1) Open TestOrders in Design View.
2) Select the InvoiceNbr row.
3) Change Required to Yes.
4) Change Indexed to "Yes (No Duplicates)" (I'm assuming there will be
no duplicates . . .)
Now:
1) Look up at the OrderID row.
2) Right-click on grey-square (with the key-symbol in it) to the left
of OrderID.
3) On the pop-up menu, de-select the Primary Key.
4) Change Data Type to Number.
5) Change Decimal Places to 0.
6) Change Required to Yes.
7) Change Indexed to "Yes (No Duplicates)" (I'm assuming there will be
no duplicates . . .)
Now:
1) Using the left-click, select/highlight both OrderID and InvoiceNbr
rows.
2) Hover the pointer directly over the thin line separating the two
grey-squares and the pointer has changed to the row-height draging
symbol.
3) Right-click at this time to get the pop-up menu.
4) Select Primary Key.
Now:
1) cntl-s to save.
2) Click "No" when the dialog box appears.
3) Click "Ok" to all further dialog boxes, ignore their messages.
Order ID and InvoiceNbr are now your Primary Key for the table.
