You are here:

Using MS Access/Comparing the data from 2 monthly tables

Advertisement


Question
QUESTION: Hi Scottgem - I have 2 tables named as CurMon and PrvMon respectively. Both the tables have same fields but the data belongs the that particular month. Some of fields are 1. Loss ID 2. Loss in local currency 3. Loss in USD 4. TOTAL Loss, etc. Now what I need is - I would like to compare both the data based on the loss IDs. Please note that there may be some loss IDs added or deleted in new table and I would want all the Loss IDs to be shown though they are newly added or removed when compared to previous month. Please help.

ANSWER: First, you shouldn't have tables for Current and previous months. This should be handled by queries.

Second, I'm not getting how they are related, why would the lossID be the same?



---------- FOLLOW-UP ----------

QUESTION: Mostly loss IDs are common, for example loss I'd 3201 will have some loss in current month (I.e. 56000 USD loss) and in previous month 50000 USD loss. So I want to show 3201 loss I'd in a new table or query along with current month loss and previous month so that then I can take a difference of both.

So basically I need a query which can give me all unique loss IDs along with current month and previous month loss column. There are many loss columns like this bit if I get one loss like this I can arrange others by Applying the same logic.

ANSWER: Ok, so you make 2 queries, one with date criteria to return the current month, the other to return the previous month.

Next you take those 2 queries and create another query joining them on LossID. You bring in the amount column from the previous month and the amount column from the current month. Add a column to show the difference.

---------- FOLLOW-UP ----------

QUESTION: Hi Scott - can you share your email Id. I will share the file with you. I have tried something but it doesn't work. Thanks for your help.

Answer
if you want to share the file, there are 2 options. Either you can upload the file to cloud storage like OneDrive or Dropbox, then include a link in a response here. or you can use the Access forum at AskMeHelpDesk.com and upload the file as an attachment there.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.