Run Query based off Table selected from Combo Box

[AmDigPC]

Ok, so here's the situation:
I have a series of tables that have monthly reporting data in them (1 table for each month). In these tables is a 'Total Views' column. What I need to do is calculate the difference between the Total Views of each item from 2 separate months (something like: [9-September]![Total Views]-[8-August]![Total Views]).

I can manually create a query and set the Total Views Field to the aforementioned formula and get a table with the data I want on it.

Here's where it gets tricky: I need to be able to create that query 'on the fly' (form button click) and have the tables it's reaching out to based on what was selected from a combo box on the form. I have the form (frmDataPull) all set up to give the user a list of tables to select from for month 1 (cboMonth1) and month 2 (cboMonth2)

I cannot for the life of me figure out how to setup a query to pull the Total Views data from the cboMonth1 selection/table and subtract it from the cboMonth2 selection/table. What do I need to type into the Field and/or Criteria boxes to get this to work? Is there any coding that needs to take place in the background?

Any help would be greatly appreciated; this issue has been quite vexing...

[jforbes]

Would love to help, but from what is provided it would be all very speculative. If you could provide particulars about your tables it would help a lot.

[zmbd]

You are using the old workbook>Worksh eet mindset here and that will cause you a lot of issues!

I highly recommend that you absolutely stop what you are doing and read the following:[*]> Database Normalization and Table Structures.

I also have to agree with jforbes, you've not provided enough information on the table structures and their relationships; however, I suspect, once you normalize your data the issues you are having will solve themselves. Along with the details of your table structure, the version of Access and your level of Access knowledge will be most helpful.

[AmDigPC]

So here's a bit more information on the tables:
They contain records of knowledgebase article views for each month and have a PK of 'Article Number' that is shared/linked in each month (table). I attached a screenshot of the relationships. What needs to happen is a query needs to take the data from the 'Article Views' column in 1 table and subtract those numbers from the 'Article Views' data in a second table (both tables selected from a form) and report the difference.

I'm not having issues with the data itself, I just don't know how to do what needs to be accomplished. I have a pretty good understanding of Access & Databases (it's just been a while since I worked with them). Also, I am using Access 2013.


[IMGnothumb]http://bytes.com/attachment.php? attachmentid=79 50[/IMGnothumb]

[jforbes]

I agree with ZMBD, you will run into trouble with your data structured the way it currently is.

A place you will have trouble is setting up a one-to-one relationship between two different months. This type of query could easily have orphaned records if there are Articles in one table an not the other. In this case your calculations will either be off or missing altogether. You will probably have this trouble regardless, but it's usually easier to trap when you are referencing a single table.

It would probably be best to create a Main Table with all the fields along with either Month and Year Fields, or a field to represent both Month and Year. Once this is done, you can you can setup a static query and just supply the Month/Year as parameters.

If you have no choice to continue on with your current structure, you will need to create a text string like the following and replace the actual tables with your users selection.

SELECT M1.*, M2.[Total Views] as [M2 Total Views]
FROM [8-August2014] AS M1
INNER JOIN [9-September2014] AS M2
ON M1.[Article Number]=M2.[Article Number]

You never really mention if you are creating a Report off of this or if you are showing it on a Form. Either way, you would then set the Form or Reports RecordSource equal to the string.

[AmDigPC]

Thanks for the response!

So you are saying that I should just have a single table that I import the data into each month with the added field of month/year? Would I still have the individual month tables? If not, how would I write the query to take the user selected month/year from the form and subtract the 2 Article Views numbers?

It sounds like I'm going about this the wrong way & what I was trying to do isn't really possible... In the end, all we need to happen is the following:
1. Import spreadsheet report (for month) from knowledgebase system
2. User opens form and select the 2 months they want to calculate
3. Query calculates the difference in views from Month 1 to Month 2
4. User gets a report showing the total article views for desired month (Month 2)

The whole drive behind this is our KB system only stores the Total Views for the lifetime of the article (does not have the ability to calculate monthly views). As a result, we are doing a data capture at the end of each month so we can have historical article usage data (mainly for the purpose of knowing which articles are not being used & can be archived).

With that being said, is there a better way to approach this?

[jforbes]

Instead of explaining multiple ways it could be done, there are a lot of options available to you right now, I figured I would bang out a sample and post the results so you can see how you can approach this adventure. This is quick and dirty. You will want to make it your own.

Here is a sample Schema:

It's mostly the same as one of your tables with the addition of ExtractNumber and ExtractName. For this example ExtractNumber is a number that you would increment each time you added a monthly extract to the table. This is the column that will link the months together. ExtractName is just a User Friendly Name for the Extract. You could include Month and Year here or to confuse the issue, make it FK lookup to another table with the long name (don't let that muddy the water yet)

Here is some Sample Data that I put in there to test with:


Here is a quick and dirty Query linking the tables together:

SELECT 
  [KnowledgebaseHistory].[Total Views]-[Previous].[Total Views] AS ViewsThisGoRound
, Previous.ExtractNumber
, KnowledgebaseHistory.[Total Views]
, KnowledgebaseHistory.[Article Number]
, KnowledgebaseHistory.ExtractNumber
, KnowledgebaseHistory.ExtractName
, KnowledgebaseHistory.Title
, KnowledgebaseHistory.[Version Number]
, KnowledgebaseHistory.[Created Date]
, KnowledgebaseHistory.[Last Modified Date]
, KnowledgebaseHistory.[Created By Full Name]
, KnowledgebaseHistory.[Last Modified By Full Name]
FROM KnowledgebaseHistory 
LEFT JOIN KnowledgebaseHistory AS Previous 
ON (KnowledgebaseHistory.ExtractNumber-1 = Previous.ExtractNumber) AND (KnowledgebaseHistory.[Article Number] = Previous.[Article Number]);

An here is some Sample Output of that Query:


I'm hurrying to get this post completed as I need to go, so if everything looks good, you should that the first two rows have Nulls for [ViewsThisGoRoun d] as there is no previous history, where the 3rd and 4th rows have the delta.

Hope it helps.

[zmbd]

Thank You J, I was thinking of doing the same thing; however, just didn't get a chance!!

AmDigPC; we just had another member go thru this same growing pain with design. I think that you would really benefit by reading thru the following thread (and maybe the back-story too (^_^) ) http://bytes.com/topic/access/answer...-layout-tables
One of the great things about this site is that we can benefit from the efforts put forth by others!

I should also send you my boilerplate for new access application developers... check you bytes.com inbox (^_^)


--- A little house keeping here ---
WE do ask that threads be kept to one question.
IMHO, J gave what is most likely one of the more workable answers in post#5
If you need help with the database design once you have worked thru J's last post, the thread I've linked you to, and my boiler plate, please start a new thread.

Thnx
Z

[AmDigPC]

jforbes,
Thank you so much for the detailed answer!

I was able to follow/adapt your instructions and get a report showing what we needed. Funny thing is, I was already playing around with having the data on a single table (thinking it might be easier) :-)

I do have one final question though: how would I tweak that SQL query to pull the Article Views for the last 3 months?

zmdb,
Thank you for the additional information; I will be sure to go through those links you sent! Sorry if we went a little off-topic...

[AmDigPC]

After looking into my last question/problem further, it seems like I need to have the first query (the one calculating the monthly views) dump the data onto a new table then run a second query that adds the 3 months of views together. Here's what I've accomplished:
- I have the working SQL that calculates the monthly views (tweaked version of what jforbes posted)
- Wrote a query to create the new table
- Wrote a query to put the monthly view data on the new table

Where I'm stuck is, I don't know how I would write the query that would total the last 3 months. Can anyone help on this?

Should I post this as a new topic/question?

[jforbes]

Yeah, One Question per Thread. Also, if you can, provide what you have accomplished so far.