Table Automatically Populating with Data from another table

Question

Table Automatically Populating with Data from another table

ITHELP85

Valued Member

Points: 59
More actions
November 9, 2008 at 1:19 am

#195996

So I am working on this project, where I have an ITEM_PURCHASE table and an WAREHOUSE table
I want users to be able to input data into the ITEM_PURCHASE table thru a form and the WAREHOUSE table will automatically populate from certain fields in ITEM_PURCHASE. In this scenario should I use a trigger or stored procedure? Can someone provide the SQL code to get me started? Can someone help me out here, it will be greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'd say stored proc. Have the form call the proc when it's saved and have the proc do the inserts into the two tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ITHELP85 Valued Member Points: 59 More actions · Answer 2

Thanks I am new to this so I dont know where to start..could you be able to help me with that?

ITHELP85 Valued Member Points: 59 More actions · Answer 3

Thanks, but I am still lost.

My app is the only app toching ITEM_PURCHASE. I am new to this so... I am wondering could you guide me a little bit more. After users input data into an Access form Populating ITEM_PURCHASE, I want to take the Values for ItemNumberSK(surrogate key), ItemName, and Quantity and place them in WAREHOUSE.ItemNumberSK, WAREHOUSE.ItemName, and WAREHOUSE.ItemQty

Thanks again

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Access or SQL Server?

Can you post the table definitions, an example of the data that would be inserted and the end results that you want?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ITHELP85 Valued Member Points: 59 More actions · Answer 5

GilaMonster (11/9/2008)
Access or SQL Server?
Can you post the table definitions, an example of the data that would be inserted and the end results that you want?

I am using an Access front-end with SQL Server 2005 back-end

I want once an ITEM_PURCHASE record is entered the

Values ITEM_PURCHASE(ItemNumberSK, ItemName, Quantity) are INSERTED

INTO WAREHOUSE (ItemNumberSK, ItemName, ItemQty).

I hope this helps

/* Creates ITEM_PURCHASE table */

CREATE TABLE ITEM_PURCHASE(

ItemNumberSKintNOT NULL IDENTITY(10000, 1),

StoreNumberSKintNOT NULL,

ItemNamechar(50)NOT NULL,

DatesmalldatetimeNOT NULL,

LocalCurrencyAmtdecimal (12, 6)NOT NULL,

ExchangeRatedecimal (12, 6)NOT NULL,

Quantitynumeric(7, 0)NOT NULL,

CONSTRAINT Item_PurchasePK PRIMARY KEY (ItemNumberSK),

CONSTRAINT Item_PurchaseFK FOREIGN KEY (StoreNumberSK) REFERENCES STORE(StoreNumberSK)

);

/* Creates WAREHOUSE table */

CREATE TABLE WAREHOUSE(

ItemNumberSKintNOT NULL,

ItemNamechar(50) NOT NULL,

ItemUnitPricemoneyNOT NULL,

ItemQtynumeric(7, 0)NOT NULL,

CONSTRAINT WarehousePK PRIMARY KEY (ItemNumberSK),

CONSTRAINT WarehouseFK FOREIGN KEY (ItemNumberSK) REFERENCES ITEM_PURCHASE (ItemNumberSK)

);

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

Probably your best bet is to execute a stored procedure in SQL Server in your form's After_Update event. The stored procedure would insert a record into the Warehouse table if it didn't exist or update any existing record.

Todd Fifield