Creating a bookstore database in MS Access with the specific requirements you mentioned is quite comprehensive, and would involve multiple layers of functionality. Below are the generalized steps that address the various aspects of creating this database. Remember, each of these steps will require substantial effort and familiarity with Microsoft Access.

Step 1: Design the Database Schema

You'll need to identify all entities and their attributes. For a bookstore, likely entities are Books, Publishers, Customers, Orders, OrderDetails, and possibly Employees.

• Books: BookID, Title, PurchasePrice, SalePrice, QuantityInStock, PublisherID, etc.
• Publishers: PublisherID, Name, DiscountRate, etc.
• Customers: CustomerID, Name, ContactInfo, etc.
• Orders: OrderID, Date, CustomerID, EmployeeID, etc.
• OrderDetails: OrderDetailsID, OrderID, BookID, Quantity, etc.
• Employees:ID, Name, Role, etc.

Step 2: Create Tables in MS Access

Use the Access interface to create these tables, define their data types, and set primary keys. For example, create a table "tblBooks" with fields like "BookID", "Title", etc.

Step 3: Define Relationships

Establish relationships between tables including referential integrity. For instance, a book will be related to the publisher, orders to customers, and order details to both orders and books.

Step 4: Create Queries

Develop queries that can perform calculations based on your business rules. For the tiered discount logic, you'll need to use calculated fields in your queries. Use the Query Design tool to create:

• A query to calculate the purchase price based on the quantity tier.
• Queries to search for books based on different filters like publisher, price range, etc.

Step 5: Create Forms

• Unbound Form (Homepage): Use design view to create a dashboard with buttons to navigate to other forms and reports.
• Trivial Form (Add a New Customer): A simple form linked to the Customers table where you can add new customer details.
• Non-Trivial Forms (New Customer Order, Reorder Books): More complex forms involving subforms, calculations, and validations.

6: Create Reports

Make at least two reports:

• A sales report showing books sold with subtotals and grand totals, including discounts.
• An inventory report showing current stock levels, with books requiring reorder highlighted.

Step 7: Create Charts

Implement a chart:

• Sales chart that can be monthly, showing trends of the books sold or revenue made.

Step 8: Write SQL for a Query

Access SQL can be viewed/edited via the SQL View of a query. You’ll need to write complex SQL statements for your non-trivial queries incorporating joins, subqueries, and calculation.

Step 9: Test the Database

Once you have built the forms queries, reports, and charts, you’ll need to perform thorough testing to ensure that data is correctly inserted, updated, retrieved, and deleted.

Step 10: Refine and Debug

Based on testing feedback, refine any issues with forms' behavior or data consistency. Add any finishing touches for usability and aesthetics.

Step-by-Step for Non-trivial Query with SQL

• SQL Example for Multi-stage Query: To fetch book details, including publisher discounts and sales information.

  SELECT tblBooks.Title, 
         tblBooks.SalePrice, 
         tblPublishers.Name, 
         IIF(tblOrderDetails.Quantity BETWEEN 20 AND 49, tblBooks.SalePrice*0.95, 
             IIF(tblOrderDetails.Quantity BETWEEN 50 AND 99, tblBooks.SalePrice*0.925, 
                 IIF(tblOrderDetails.Quantity >=100, tblBooks.SalePrice*0.875, tblBooks.SalePrice))) AS DiscountedPrice
  FROM (tblBooks INNER JOIN tblPublishers ON tblBooks.PublisherID = tblPublishers.PublisherID)
       INNER JOIN tblOrderDetails ON tblBooks.BookID = tblOrderDetails.BookID
  WHERE tblOrderDetails.Quantity >= 20;

This SQL considers a three-tiered discount system based on quantity from the publisher's side. Remember, this is a simplified version to highlight the approach, and might need more complex logic in actuality.

Final Note:

For actual implementation, you will have to translate each step into actions within the Microsoft Access environment. This involves using the toolbar options for creating new tables, queries, forms, reports, etc. Access wizards can guide you through the basics, while specific design decisions will depend on your project’s unique requirements.

Remember that the complexity and refinement of your database will depend significantly on your familiarization with the software's tools and features.

Based on the information provided and common database design practices, here's a detailed schema for a bookstore database in MS Access, including table names, attributes, data types, and relationships:

Table: Books

• BookID (Primary Key): AutoNumber -Title**: Text
• GenreID (Foreign Key): Number (Long Integer)
• AuthorID (Foreign Key): Number (Long Integer)
• PublisherID (Foreign Key): Number (Long Integer)
• PurchasePrice: Currency
• SalePrice: Currency
• **ISBN Text
• QuantityInStock: Number (Long Integer

Table: Authors

• AuthorID (Primary Key): AutoNumber
• FirstName: Text
• LastName: Text- Biography: Memo

Table: Publishers

• ID (Primary Key): AutoNumber
• Name: Text
• ContactInfo: Text

Table: Genres

• GenreID (Primary Key): AutoNumber- GenreName: Text

Table: Customers

• CustomerID (Primary Key): AutoNumber
• FirstName:
• LastName: Text
• ContactInfo: Text
• Email: Text

Table: Orders

OrderID (Primary Key): AutoNumber

• CustomerID (Foreign Key): Number (Long Integer- Date: Date/Time
• TotalAmount: Currency

Table: OrderDetails

• OrderDetailID (Primary Key): AutoNumber
• OrderID (Foreign Key) Number (Long Integer)
• BookID (Foreign Key): Number ( Integer)
• Quantity: Number (Long Integer)
• DiscountRate: Number (Double)

Table: Employees (if applicable)

• EmployeeIDPrimary Key): AutoNumber
• FirstName: Text- LastName: Text
• Role: Text
• ContactInfo: Text

Relationships:

1. Books-Author: One Author can write many Books, but a Book has only one Author. -Books.AuthorID** -> Authors.AuthorID

2. Books-Genre: One Genre can many Books, but a Book is categorized in one Genre.

   • .GenreID -> Genres.GenreID

3. Books-Publisher: One Publisher can publish Books; a Book has one Publisher.

   • Books.PublisherID ->Publishers.PublisherID**

4. Books-OrderDetails: One Book can be part of many OrderDetails; one OrderDetail refers to Book.

   • OrderDetails.BookID -> Books.BookID

5. Orders-OrderDetails: One Order can contain many Order; an OrderDetail is part of one Order. -OrderDetails.OrderID** -> Orders.OrderID

6. Customers-Orders: A Customer can place many Orders; an Order is associated with one Customer.

   • Orders.CustomerID -> Customers.CustomerID

7. Employees-Orders: If you include Employees, an can manage many Orders, but an Order is managed by one Employee (optional).

   • Orders.EmployeeID -> Employees.EmployeeID

Make sure to set appropriate Indexed property to "Yes (No Duplicates)" for fields like ISBN in the Books table to ensure uniqueness where needed.

For Foreign Key relationships, enforce referential integrity to ensure database does not get corrupted with orphan records.

Remember, in MS Access, lookup fields can also be used, but for a more robust and scalable, using foreign keys as presented above is the recommended approach. This schema serve as a strong foundation for a bookstore database, upon which you can build forms, queries, and reports according to your requirements.

In the given database schema, the "Genres" table represents the different categories or genres of books that are available for sale. Each genre is defined by a unique "GenreID" and a descriptive "GenreName".

To calculate the purchase price of a book based on the tiered discount logic, we need to use a query that can perform calculations based on the business rules. The query design view in MS Access provides an easy-to-use interface for creating complex queries.

For the tiered discount logic, we can create a query that calculates the purchase price based on the quantity tier. The query would look something like this:

SELECT [BookID], [PurchasePrice], [SalePrice], [QuantityInStock], [GenreID] FROM [tn001_Books] WHERE [QuantityInStock] >= 20;

In this query, we are selecting the columns "BookID", "PurchasePrice", "SalePrice", "QuantityInStock", and "GenreID" from the "tn001_Books" table where the quantity in stock is greater than or equal to 20.

TheGenID" column is included in the query to calculate the purchase price based on the genre. We can do this by using a calculated field in the query. To create a calculated field, we need to use the "Query Design" view in MS Access.

In the "Query Design" view, we can add a new column to the query by clicking on the "Add Column" button. We can then selecting the data type of the new column and give it a name. For example, we can name the new column "TieredDiscount".

Once we have added the new column, we can use the SQL formula to calculate the tiered discount. For example, we can use the following formula:

=IIf([QuantityInStock] >= 20, [SalePrice]*0.95, IIf([QuantityInStock] >= 50, [SalePrice]*0.925, [SalePrice]*0.875))

This formula checks whether the quantity in stock is greater than or equal to 20, 50 or 100, and then applies the corresponding discount to the sale price.

Finally, we can run the query and view the results. The purchase price for each book will be calculated based on the tiered discount logic and displayed in the "TieredDiscount" column.

Note that this is a simplified example to illustrate the concept of tiered discounts, and the actual logic may need to be more complex to accommodate more tiers or other business rules.