Porting MS Access database to SQL Server

40 pts.
Tags:
Access Database
Microsoft Access
SQL Server database
SQL Server stored procedures
I've got a problem with porting my MS Access database to SQL Server. In my inventory database, I have 4 tables: (1)Purchase, (2)Sales, (3)Quantity Adjustment, (4)Amount Adjustment. I want to calculate the stock position from those 4 tables. In access database, I do with this kind of queries: a. parameterized query to filter each table and union it all and I name it 'qryStockPos2':
SELECT Product, Quantity FROM tbPurchase WHERE PurchaseDate<=[selDate]
UNION ALL SELECT Product, Quantity FROM tbSales WHERE SalesDate<=[selDate]
UNION ALL SELECT Product, Quantity FROM tbQtyAdj WHERE QtyAdjDate<=[selDate]
UNION ALL SELECT Product, Quantity FROM tbAmountAdj WHERE AmountAdjDate<=[selDate]
b. select query based on the first query:
SELECT Product, Sum(Quantity) FROM qryStockPos2 GROUP BY Product;
Please help me how to port those queries into sql server stored procedures. Thanks in advance. Regards, ipong

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’ve got a couple of options.

1. Setup a function that accepts the date as a parameter and returns the data you need.
2. Setup a Common Table Expression within your code and query that.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,365 pointsBadges:
    report
  • Squashjunkie
    If you are using SQL 2005 you could also use SSIS as this will give you more flexibilty instead of writing the hard code and creating stored procedures.
    325 pointsBadges:
    report
  • Ipong
    Thanks...finally I got the answer. I create a stored procedure that contains: a. create a temporary table b. create a cte query (union several tables) c. put cte result into temp table d. create a cte query (union several tables and temp table) e. get the result from last cte query I've compared with my access database and it produces the same result....
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following