Ms-access – How Do I Query this Data in Access Without Creating a Circular Reference

ms accessms-access-2016query

I have two tables called Events and EventSeasons. Events define the main type of events that there can be, and EventSeasons stores the number of times that the events have occurred yearly. I'm trying to query them so that I can figure out which ones are ongoing (between a range of dates), but my problem is that I also need to retroactively mark past seasons of the same event type as active. The next diagram shows what the output should be:You can see in EventSeason that IsActive column for item 2003 is true. Therefore in Events the Subactive column for the item 1001 (Summer) is True. Finally back in EventSeasons the Retroactive column for all 1001 items is set to true.

I have actually achieved this in Excel, but now that I'm trying to do it in access I feel like I'm going to create a circular reference. What I did in excel was to assign true to IsActive in EventSeasons if today's date is between range, then I assign true to Subactive in Events if there are instances of the current EventID marked as active in EventSeason. I then finally assign RetroActive to true if Subactive is true Or IsActive is true.

Best Answer

It would probably be easiest to do what you are trying to do by using views, instead of trying to add calculated columns to the table.

EventTheme_IsActive

SELECT ThemeID, 
       EventID, 
       StartDate, 
       FinishDate, 
       IIf(Date()>=StartDate And Date()<=FinishDate,True,False) AS IsActive
FROM EventTheme;

Events_Subactive

SELECT e.EventID, e.Name, et.Subactive
FROM Events AS e
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) > 0, True, False) AS Subactive 
    FROM EventTheme_IsActive 
    WHERE IsActive = TRUE 
    GROUP BY EventID)  AS et ON e.EventID = et.EventID;

Events_RetroActive

SELECT et.ThemeID, et.EventID, et.StartDate, et.FinishDate, et.IsActive, a.RetroActive
FROM EventTheme_IsActive AS et 
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) > 0, TRUE, FALSE) AS RetroActive 
    FROM EventTheme_IsActive 
    WHERE IsActive = TRUE 
   GROUP BY EventID)  AS a ON et.EventID = a.EventID;

These views may not be updateable but if you just need read access then this should suit your needs.

Related Solutions

How to go about creating this SQL query

You can self join to tables for this sort of criteria(at least, I can do this in SQL Server):

SELECT
     r.name,
     m.title
FROM
    rating rt
    JOIN movie m ON (rt.mid = m.mid)
    JOIN reviewer r on (rt.rid = r.rid)
    JOIN rating rt2 on (rt.mid = rt2.mid and rt.rid = rt2.rid)
WHERE
    rt.ratingDate > rt2.ratingDate
    AND rt.stars >  rt2.stars;

Note that we're joing rating back to itself. This way we can use the WHERE clause to find rows in rating that are greater in both date and stars to other rows in rating.

You can review the full example I built in SQL Fiddle here.

If you want to add the condition that the reviewer made exactly these 2 reviews of the movie and no more, you can add:

    AND NOT EXISTS                                  --- not exists
        ( SELECT *
          FROM rating rt3                           --- another rating
          WHERE rt3.mid = m.mid                     --- for same movie
            AND rt3.rid = r.rid                     --- by same reviewer
            AND rt3.ratingDate <> rt2.ratingDate    --- on different date
            AND rt3.ratingDate <> rt1.ratingDate    --- than the first 2 reviews
        )

This assumes that the Rating table has (rID, mID, ratingDate) as primary key.

Ms-access – Normalizing a database without access to source data

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it. The two fundamental benefits to normalization are:

Data Integrity
Query Flexibility

The simple approach to normalization is to create a table for each person, place, thing, concept, or event. By doing this, you have each characteristic of each person, place, thing, concept, or event of interest to you in one and only one place in the database. The benefit of data integrity is achieved for when you insert and update data. Because you have normalized you don't have redundant copies of the same characteristic in many places across the database, each with a different value, that you have to remember to update and keep in sync. Second, you have only one place in the database to program checks when inserting or updating data to make sure the data is valid. Since your data is coming in from many excel spreadsheets, normalizing your data gives you the opportunity to implement data integrity checks on the load to make sure you analysis is based on accurate data. The benefit of query flexibility is achieved for when you want to read and analyze the data. Because you have normalized the data you can connect up the tables in a flexible way based upon the question about the data you want answered, including only what you need to answer the specific question. Second, this enables the database to return the answers to your questions much faster than if it had to scan through all the data, including the data not relevant to your question, in your un-normalized tables .

Access is a simplified DBMS and does include a basic SQL processor that allows you to write queries and thus take advantage of the benefits of normalized data. If you are eventually going to move to SQL Server, which is a full featured DBMS, then normalizing your data now will ease the transition and let you take advantage of the full capabilities of SQL Server and its very rich implementation of SQL.

As I mentioned in the beginning, to achieve these benefits you must be willing to do the up front programming to translate the data coming in from your various excel dumps and map the rows and columns in those spreadsheets to your normalized tables. This is not a trivial exercise but doable using Access programming. One approach would be to create tables that replicate the data as in the source and load the data into them. These are known as stage tables. Once you have the un-normalized data in Access tables you can then more easily write access code using SQL to extract the data from those stage tables, normalize it, identify data quality issues (say the same characteristic in two different excel dumps that should have the same value but do not), and load it into your normalized tables. This is the common method for normalizing data coming from an un-normalized source very common in subject area based data warehouses.

You will find this additional level of effort to be well worth it though once you have high quality, normalized data in your access database. You report consumers will see that you are a real data professional when you show them examples where data quality was poor and you discovered that fact so it can be corrected in the sources. Likewise, when they ask for a new report that analyzes the data in a very different way, you can rapidly create the new report using SQL to combine the data in the normalized tables in this very different way that wasn't originally anticipated. They will be very impressed that you are able to do this quickly and easily!

I hope this helps explain why normalization would be of benefit to you.

Best Answer

Related Solutions

How to go about creating this SQL query

Ms-access – Normalizing a database without access to source data

Related Question