Foreign Key/Unique ID Problem

[Raptor_45]

Hi, I'm using Access to connect to SQL Server and I'm having trouble getting a form working correctly. I want the form to have everything from the Sales table and also the Name field from the Customer table. SalesID is an autonumber since it's the primary key of that table. The CustomerID field doesn't automatically populate with it's unique ID. I would like it so that I can enter in the customer's name and then automatically know what the unique identifier is. I don't want it so every time I enter in a new record I have to go look up that customer's CustomerID and key it in manually. The way it's setup now, it won't insert the record if I don't put in the CustomerID. I'm guessing I designed this incorrectly but I'm not sure what do differently. The way I enter in a new sale now is I just call a stored procedure I wrote which finds the customers name based on the unique ID:



CREATE PROCEDURE New_Sale

@CustomerName varchar(30),
@Product varchar(100),
@Quantity int,
@Date smalldatetime
AS

DECLARE @Cid as int

SELECT @Cid = CustomerID
FROM dbo.CUSTOMER
WHERE [Name] = @CustomerName;

BEGIN
INSERT INTO SALES(CustomerID, Product, Quantity, [Date])
VALUES(@Cid, @Product, @Quantity, @Date);

END

UPDATE dbo.[PRODUCT]
SET [in stock] = ([in stock] - @Quantity)
WHERE @Product = Model;
GO

I'd rather enter the sale into a form instead of running this stored procedure and being prompted for the parameters (users are more prone to keying mistakes). Do I need to alter the sales table in order for the CustomerID to be generated automatically after entering in the customers name? Here is a diagram of what my tables/relationships look like:

[rpeare]

You probably want a combo box that has 2 columns, the query driving the combo box would be based on the CUSTOMER table and would have 2 columns, the customerID as the first column and the customer name as the second column. Set the bound column of the combo box to 1 (the customer ID) but set the column widths to 0,2 (zero for the width of the first column so that the customer number is hidden) this will allow you to type in the customer name. Then if you have a secondary field that is just

=[ComboBoxName]

it will reflect the customer number because you've made it the bound column of your combo box.

[Raptor_45]

You probably want a combo box that has 2 columns, the query driving the combo box would be based on the CUSTOMER table and would have 2 columns, the customerID as the first column and the customer name as the second column. Set the bound column of the combo box to 1 (the customer ID) but set the column widths to 0,2 (zero for the width of the first column so that the customer number is hidden) this will allow you to type in the customer name. Then if you have a secondary field that is just

=[ComboBoxName]

it will reflect the customer number because you've made it the bound column of your combo box.

That seems to fix my problem, Thanks! One other question, what would I need to do if I wanted the amount I enter in for quantity to subtract that off of the [in stock] field from the Product table?

[Raptor_45]

Basically I want it to run this SQL statement after I input the quantity in the record on the form:

UPDATE PRODUCT
SET [in stock] = [in stock] - Quantity
WHERE PRODUCT = Model;



If I make a macro I'm not sure how to make quantity and product have the values from the fields I just filled in. Would this way work or is there another way that would be simpler?

[alansidman]

You probably need to identify Quantity as a field in the Sales Table. You don't have the field in the Product Table.

Prefix the field as follows [Sales].[Quantity] and see if that works for you.

[Raptor_45]

You probably need to identify Quantity as a field in the Sales Table. You don't have the field in the Product Table.

Prefix the field as follows [Sales].[Quantity] and see if that works for you.

That didn't seem to work for me. If I wrote a stored procedure and called it from a macro then added the macro to an after update event in the Text Box: Quantity, would that work you think?

[rpeare]

I would not, under any circumstances, have my stock total modified every time data was entered against that item. I would have a table that stored a starting quantity (as of a specific date) and a second table for receipt of that item (additions to stock) and the detail (sales quantity) would the subtractions to stock. Then if I wanted a report for a specific period showing stock level I would sum all the receipts and expendatures for the time period in question. If you update a table you have no historical data and no way to audit what happened to your inventory and why.

[Raptor_45]

I would not, under any circumstances, have my stock total modified every time data was entered against that item. I would have a table that stored a starting quantity (as of a specific date) and a second table for receipt of that item (additions to stock) and the detail (sales quantity) would the subtractions to stock. Then if I wanted a report for a specific period showing stock level I would sum all the receipts and expendatures for the time period in question. If you update a table you have no historical data and no way to audit what happened to your inventory and why.

That makes more sense... So are you saying leave the initial [in stock] in the Product table and at a date field for each of those initial totals? Then create another table called like Stock, give it a PK, a FK (Sales_ID) from Sales table, another FK (Product_ID) from Product table, add some fields like: additions to stock, number sold and date. Would I still have the quantity field in Sales table? Or would it be taken out and I would use the number sold field from table Stock?

[rpeare]

Raptor_45.zip

This is the basic structure I would use.

The adjustment reason table is just so you can handle non purchase order reasons for additions and subtractions from stock.

The Product (tblProducts) table contains only information relating directly to that product
the Purchase Order table (tblPO) table contains only information directly relating to the order, if you have open purchase orders where your customers have an open po for, say, a month at a time, and you add items to it as they order items, then you would have to have a date on the detail table to to accurately reflect what day the items were removed from stock.
the Purchase Order Detail table (tblPODetail) contains information related to the substance of the order
and finally the inventory adjustment table (tblProductAdjustments) would handle all your receipts and disbursements of inventory that occur outside of a purchase order

[Raptor_45]

Thanks for the help!