C# Access Database Question

Question

Say i have 2 tables now, PricePlan and Bill. Both tables have a column called 'Price' and I would like the table 'Bill' to update the value from 'PricePlan's Price. How can i do this or what SQL statement should i be using? Thanks in advance!

can you show the table structure in more depth? how are priceplan and bill related? what version of access are you using? what does this have to do with c#? — nathan gonzalez
– nathan gonzalez, Commented Feb 9, 2011 at 17:48

Abe Miessler · Accepted Answer · 2011-02-09 17:57:01Z

1

You will need to have some sort of way to define a relationship between the two tables.

For instance if your tables have this structure:

PricePlan
---------
ID
Price

Bill
---------
PricePlanID
Price

This will only work for SQL Server. See below for Access solution.

Then a query like this should update Bill :

UPDATE b SET b.Price = pp.Price
FROM Bill as b
INNER JOIN PricePlan as pp
ON b.PricePlanID = pp.id

Also, the schema above is only for example purposes. If yours is like that you should look at changing it.

UPDATE

I just noticed this is for Access, sorry. The strucure of your query will be slightly different. See below:

UPDATE Bill INNER JOIN 
PricePlan ON Bill.PricePlanID = PricePlan.ID
SET Bill.Price= [PricePlan].[Price];

edited Feb 9, 2011 at 17:57

answered Feb 9, 2011 at 17:50

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

pacheco Over a year ago

Hi, really thanks for the help. I have 2 records in PricePlan which are 'Guest' and 'Member'. I have already created a relationship between both tables. Price seems to update and work for 'Guest' but not for 'Member', is there anything that i need to modify?

Abe Miessler Over a year ago

I don't understand what you mean by two records which are 'Guest and 'Member'. Can you provide some sample data in your question as well as what you want the end data to look like?

pacheco Over a year ago

Oh, basically the PricePlan has the following columns, 'PricePlanName' and 'Price'. So i have the PricePlanName of Guest and Member, each having their own 'Price'. What i would like to achieve in 'Price' for table 'Bill' is the extraction of the price given a guest or a member.

Abe Miessler Over a year ago

You would have to join between the PricePlanName column of the PricePlan table and whatever you are calling the PricePlanName column in the Bill table.

nathan gonzalez · Accepted Answer · 2011-02-09 17:52:53Z

0

making some wide assumptions here, but i think this short tutorial on cascading updates in access 2010 should get you on your way.

answered Feb 9, 2011 at 17:52

nathan gonzalez

12k4 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

C# Access Database Question

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related