Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

My question is #1 below. #1. All new books in the Excel file should be added to the Book table. If there are books that

My question is #1 below.

#1. All new books in the Excel file should be added to the Book table. If there are books that were already in the table, only the quantity should be updated. All purchased books are new.

I am thinking I need to write a Merge Query to answer this problem but the columns are named different in each table(Type is the same as Category, Stock is the same as St, and Price(EUR) needs to be converted to Price ($), so I'm not sure how I get them to merge correctly. Should I use a JOIN first? I started the below code but I'm stuck... am I even on the right track?

SELECT Book.Title, Book.ISBN

MERGE Book AS T USING Inventory AS S ON T.Title = S.Title WHEN MATCHED THEN UPDATE SET

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I've imported the Excel files into the Access database as additional tables. Below is the assignment, followed by a screenshot of the tables in the database.

The first one is the same Microsoft Access database you were given in Assignment 5, last week. The second one is an Excel file that that contains information about a new batch of books that was recently purchased by the bookstore that handles the first database. It also contains a client list from the old bookstore that was also acquired by the new bookstore. Your job is to integrate the information provided in the Excel file onto the current database file. To do that you must do the following:

  1. All new books in the Excel file should be added to the Book table. If there are books that were already in the table, only the quantity should be updated. All purchased books are new.

image text in transcribed

image text in transcribed

BookInventory All Search Tables Fie Criminal Justice Finance Painting 9 40 10 60 4 30 11 75 6 25 15 20 2 5 50 4 80 3 10 8 30 3 25 1 Criminal law 39089 Thomas J. Gardner &Terry M. Andersorn 2 Financial Markets and Institutic 213683 Frederick S. Mishkin & Stanley G. Eakins 3 Pablo Picasso 4 Discrete Mathematics 5 Ethical Obligations and Decisior 802528 Steven M. Mintz and Roselyn E. Morris 6 Gardner's Art through the Ages 507086 Kleiner, Mamiya & Tansey 7 Compilers 8 Degas The Life of the Artist 8107 Gordon, Forge 9 Artificial Intelligence. A Moden 790395 Stuart J. Russell & Peter Norvig 10 Finite Mathematics 11 C+ How to Program 12 The Definitive Guide to HYML 3960 13 Design Basics 14 Modern Operating Systems 600663 Andrew S, Tanenbaunm 15 Basic Statistics for Business and 340178 Lind, Marchal & Wathen 16 Fourier Analysis 17 Systems Analysis and Design -224085- Kenneth E. Kendall & Julie E. Kendall 18 Constitutional Law and Crimina 81126 Scott Harr, Karen Hess and Christine Orhtmann 19 Cost Accounting. A Managerial 210917 CharlesT. Horngren, Srikant M. Datar and Madhav Rajan 20 Cobol for the 21st Century 1252 Warncke, Carsten 30515 John Dossey, Albert Otto, Lawrence Spence, Charles Vanden Eynde Math Accounting Art in General Computers Painting Computers Math Computers Computers Design Computers Math Math Computers Criminal Justice Accounting Computers Inventory Order Orderu.. VIP Clie Wrote 48681 Alfred V. Aho, Monica S. Lam, Ravi Sethi, Jeffrey D. Ullman Lipschutz, Schiller Deitel Paul & Deitel Harvey Adam Freeman 10 216541 12 13 14 15 16 17 508377 Lauer, Pentak 2 80 3 60 4 10 12 55 4 50 5 75 25 20 Spiegel 19 20 nancy stern, Robert A. Stern and James P. Ley Ne

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Professional Microsoft SQL Server 2014 Integration Services

Authors: Brian Knight, Devin Knight

1st Edition

1118850904, 9781118850909

More Books

Students also viewed these Databases questions