duplicate records

[LizJohnson]

I have built an Access database last year. I created a query in which I want the query to total up the whole years values. We have a table which we input our returns each month. This also has multiple years in the database.

Now that we have information in for a couple of years (we entered info for past years), I am noticing that when I look at this query that some of the companies will be showing the same year and exact same values twice. It is not happening for each company

We have one customer number which we have labels AcctNmbr. This is our main linking field.

Why would some of the companies be showing twice for the same year and how can I fix this?

I am not an Access expert, just for your information.

Thanks in advance for your help.:banghead:

 

[plog]

I understood very little of what you explained, but my guess is you are joining tables with duplicates in one of them:

Table1
AcctNmbr, Returns
1, 12
3, 17
7, 19
7, 20

Table2
AcctNmbr, Returns
1, 22
3, 4
3, 81
7, 5
7, 44


If you wer to join those tables via AcctNmbr you would get 1 result for AcctNmbr=1, 2 results for AcctNmbr=3 and 4 results for AcctNmbr=7

If that's not helpful, post your SQL and sample data to demonstrate what your issue is.

 

[LizJohnson]

Thank you for your help in looking to see where my files may be duplicated. Let me try to clarify what our database does.

This database is for the Dept. of Revenue (state gov't). We use this database to keep track of our monthly returns that need to be filed.

I have set up one table to keep track of all of our "customers" (ContactInfo). I have this set up so that the customer # cannot be duplicated. This table keept the AcctNmbr (account number), AcctName (account name) and also the other contact information such as phone #'s & addresses.

I have another table set up to store our monthly returns (InsuranceTax). There are numerous lines on the return that we input into access. This has the AcctNmbr, MonthFiled, YearFiled and other information we need to track for these returns. **Note that the AcctNmbr is linked to the customer table.**

The third table that I'm using for this query is one that we just upload finance charges on (which we get from an outside source) (FinanceCharge). On this table I only have the AcctNmbr, FinSvcCharge, and Year. These charges are only done once a year.

We have to compare our yearly totals with that of another agency so therefore I created a query to totaly up the monthly amounts (per year) and give me a grand total.

The fields that I have in my query are: AcctNmber, AcctName, Year and then the rest of the files are the totals for each field we input. The final column is the FinSvcCharge totals.

For some reason, just some of the AcctNmbr are duplicating. I looked at the each of the tables to see if the numbers were duplicated and they were not.

I do have the 3 tables linked by AcctNmbr with the ContactInfo table the main table linking the other two tables, InsuranceTax & FinanceCharge with a link of #2 (Include ALL records from "InsuranceContact" and only those records from "FinanceCharge" (InsuranceTax) where the joined fields are equal (one to many). As a note, we do have customers listed in the contact table which we do not use on the other two tables.

Would you like me to send you the query SQL. This may help you understand better.

 

[plog]

Yes SQL along with some sample data that it returns to demonstrate your duplicates.

Actually if you could upload a sample database that would be best, but I understand if that isn't something you can upload.

 

[LizJohnson]

SELECT tblInsuranceTax.AcctNmbr, tblInsuranceContactInfo.AcctName, tblInsuranceTax.YearFiled, Sum(tblInsuranceTax.Premiums) AS SumOfPremiums, Sum(tblInsuranceTax.A_PremiumsReturned) AS SumOfTOTDeductions, Sum(tblInsuranceTax!Premiums-tblInsuranceTax![A_PremiumsReturned]) AS TotAmtSubject, Sum((tblInsuranceTax!Premiums-tblInsuranceTax![A_PremiumsReturned])*tblInsuranceTax!SurchargeRate) AS SurchargeDueL, Sum(tblInsuranceTax.Adjustments) AS SumOfAdjustments, Sum(((tblInsuranceTax!Premiums-tblInsuranceTax![A_PremiumsReturned])*tblInsuranceTax!SurchargeRate)-tblInsuranceTax!Adjustments) AS TotAmtDue, tblFinanceCharge.FinSvcCharge
FROM (tblInsuranceContactInfo LEFT JOIN tblFinanceCharge ON tblInsuranceContactInfo.AcctNmbr = tblFinanceCharge.AcctNmbr) INNER JOIN tblInsuranceTax ON tblInsuranceContactInfo.AcctNmbr = tblInsuranceTax.AcctNmbr
GROUP BY tblInsuranceTax.AcctNmbr, tblInsuranceContactInfo.AcctName, tblInsuranceTax.YearFiled, tblFinanceCharge.FinSvcCharge;


I would post what the query link looks like but I can't copy/paste it into this reply. I also put it on a word doc but then when I tried to upload that it was too large.

 

[plog]

From what I see each record returned from that query is unique. I think the issue is that your definition of "duplicate" is incorrect. Can you please demonstrate what you are calling duplicate data? I need to see every value from every column for the records you are calling duplicates.

My guess is that every value from every column doesn't match, but to you that is still a "duplicate".

 

[LizJohnson]

Here would be what the sample data would look like. The names have been changed to protect the innocent!

AcctNmbr AcctName YearFiled SumOfPremium SumOfTotal
01010 Hello Kitty 2014 1,200,500 1,200,500
01115 Fred Flintstone 2014 7,500,000 7,500,000
01115 Fred Flintstone 2014 7,500,000 7,500,000
11553 Tim the Toolman 2014 3,500 3,500

 

[plog]

Where's the FinSvcCharge field?

 

[LizJohnson]

I am uploading a word doc which shows all of my fields. I had to take the name out for confidentialtly purposes. If you notice the one that has Test 2 is listed twice.

Also, I noticed that this has two different amounts for the finance charge but there is only one entry for the year 2014. The other entry was from the year 2013. Why would it say that the 2013 year was the 2014 year and then list it twice?

 

[plog]

I don't understand your question, but I do know those aren't duplicates.

You have multiple FinSvcCharge values per account number.

 

[LizJohnson]

In our table for the Finace Charges, we only have one value per year. We have multiple years.

 

[plog]

In your query, the year has nothing to do with how they are joined:

LEFT JOIN tblFinanceCharge ON tblInsuranceContactInfo.AcctNmbr = tblFinanceCharge.AcctNmbr

If it needs to be joined on year as well, you need to establish that.

 

[LizJohnson]

Plog,
I'm sorry it's taken so long to get back to this. I was pulled to do something else.

When I'm running my query, I am using a date filter, so when I run my report I put in my filter the year that I want. I am not trying to join any tables based on the year. If I use my filters and only select year 2014, I should only see those values, not the 2013 values. It appears that the year # is being changed to 2014 to show up in my report but when I look at the table, it clearly shows the date of 2013.

I'm so confused on this.

 

[plog]

Review my first post on this thread then comeback to this one.

Here's another example using more relevant datasets:


Table1
AcctNmbr, Year
1, 2012
3, 2012
7, 2012
7, 1999
9, 2012

Table2
AcctNmbr, Year
1, 2012
3, 2000
3, 2012
7, 2012
7, 1999
9, 1999

If you were to join those tables via AcctNmbr you would get 1 result for AcctNmbr=1 (1*1), 2 results for AcctNmbr=3 (1*2), 4 results for AcctNmbr=7 (2*2) and 1 result for AcctNmbr=9 (1*1). This is what you are doing to get your "duplicates". You are not joining on enough data.

If you were to join those tables via AcctNmbr AND Year you would get the results you expect: 1 for AcctNmbr=1, 1 for AcctNmbr=3, 2 results for AcctNmbr=7 and 0 results for AcctNmbr=9.

You are not joining correctly.

 

[LizJohnson]

I'm trying to join the two properties on the query. I went into the design view and linked the two together. I then clicked on the line and indicated I wanted to join the properties. I then used the following:

left table Name Right Table Name
tblFinanceCharge tblInsuranceTax

Left Column Name Right Column Name
YearFiled YearFiled

When I try to do this I get an error message that says:
The SQL statement could not be excuted because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join, and then include that query in your SQL statement.

I did try to have the tblInsuranceTax be the first table but I could do this with the year filed because it is the first table with the AcctNmbr.

Does this mean that I have to have two queries to be able to get this information? How then do I join the two queries?

 

[plog]

Possibly. I think its time to start from scratch. Can you give me a copy of your database?

If not can you post sample data from your tables then what you expect as the result? Include table and field names.

 

[LizJohnson]

Below I have attached my current SQL that I have for this. I have also zipped my detailed view of what the joining looks like now. Finally I have attached a copy of a sample what the report looks like.

For this report, we have 3 years of information in our system.

If you notice, for Test 1 company it is showing 6 lines on my report (qryInsuranceYearly)

For Test 2 company, it is only showing 3 lines.

We receive monthy tax forms for each company. We enter these in our data base. What the query is suppose to do is to add up all of the months for each year and give us yearly totals for each item (deductions, surcharge, premiums, etc.). This way we can run this queryin conjunction with another query that I'm in the process of creating now to see if the amount are correct for both reports (the reports comes from different sources). I will then create a report that I can combine the two queries (or aspects of them).

I thought that if I just filtered by the year that it would only give me one line per year. I see where you are saying because I'm using two tables (tblInsuranceTax and tblFinanceCharge) that I am getting duplicate information.

One thing I don't understand is why, when I filter out the years, it gives me two lines but the amount under the finance charge is from two different years (2013 & 2014). I thought that if it was giving me duplicate information that the amount under the finance charge would have been the same. It seems like it is changing the year value somehow.

Also, I do have another question that I hope you can help me with.
As we are entering these returns we sometimes get amended returns. We need to keep both the original numbers and also the amended numbers. I'm not very good with macros but how can I have it give totals so that it only uses the last information received (the amended numbers) without including the original numbers from the report. I have created a field for the date when the return was used thinking I might need to use this date in a macro to get the most current return in our numbers.

Do you need more samples? If so, I can send you some more.

Thank you in advance for your help. You have been great. Tell you supervisor I said so!!

 

[plog]

Like I said, I'm willing to start from scratch, but I'm not going to debug code. To start from scratch, provide me with 2 sets of data:

A. Sample starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show me what you expect the results to be of that sample data.

 

[LizJohnson]

I have finally gotten together the sample data and the expected outcome. Please see the attachments.

 

[plog]

Can you give me the data in Excel or Access? Something importable?