Duplicates on repost

[Cristina]

Duplicates in report

Hi All,
I need some help with a report.
My budget in the database has three levels. Main Heading 1 Account 1.1 Item 1.1.1 etc
From the Dbase, I am supposed to run four reports quarterly.
One a detail of all transactions by item, account and main heading
One a total expense by Main heading
One summary expense by all levels showing previous and current expenses with balances.
The fourth is a detail of previous and current expenses. This is where I am having problems, as it duplicates data.
I would be grateful for your assistance.
Thanks

This is is a small sample of the report I did in excel, as when I try to run in access it duplicate the data.

Title Budget Previous records Current record Sum total of records Available balance 2 OPEREXPENSES 2.1 Supplies Scotia Bank €3.94 Christopher Williams €1,850.87 People's Store €117.43 The Stationery House €1,234.41 €5,000.00 €1,854.81 €1,351.84 €3,206.65 €1,793.35 2.2 Office space Adrian Escalante €2,776.31 Adrian Escalante €925.44 Adrian Escalante €925.44 Adrian Escalante €925.44 €6,000.00 €3,701.75 €1,850.87 €5,552.62 €447.38 2.8 Bank Charges Atlantic Bank €2.78 Scotia Bank €4.07 Scotia Bank €14.34 Scotia Bank €4.53 Scotia Bank €0.28 Scotia Bank €4.07 €500.00 €21.19 €8.88 €30.07 €0.00 €11,500.00 €5,577.75 €3,211.59 €8,789.34 €2,240.73






el

Good day,
Yesterday I posted a thread asking for help with a report.. Maybe I did not explain the problem properly.
I have a database with accounting info. A budget with three levels. Main heading, accounting heading, and Item Code.
I need to run four reports quartely. Three I am able to do.
1 - A report with summary expenses by Main heading
2 - A summary of expenses By Main Heading, Accounting, and Item code
3 - A summary of expenses as in #2, but showing previous and current quarter expenses seperately. with budget allocated and a balance eg
Budget Previous Current Balance % spent

4. Report as above but showing detail expenses.

What I did was at the end of a reporting period, copy the data in a seperate table. So I am able to run summary data from both tables. But when I try running the detail I get duplication of data.

Grateful for some assistance
PS I dont know any SQL or VBA I just work with queries etc in Access

Cristina

 

[CJ_London]

not enough detail to provide a solution, but try using distinct in your report recordsource i.e.

SELECT DISTINCT fld1, fld2, fld3
FROM myTable

To do this from the query window, open the properties (top right on the ribbon), then click on the top part of the query window and change unique values property to yes

You should also only be bring through the detail you require so it may be you query has too much detail, but without seeing, impossible to say

I dont know any SQL or VBA I just work with queries etc in Access Queries are SQL

I recommend you start to view your queries in the SQL window (top left of the ribbon) to see how they are put together. Most responses you will get here will be quoting SQL as above and you will be asked to post it - a screenshot of a query window is OK sometimes but only works with smaller queries

 

[Cristina]

Thanks for responding. This budget has only two levels.
I changed the query to read Unique Values, but the data is still duplicated. Please see attached data from the summary query. This reports works well. Let me explain how I get my report. I have one table with expenditure. But after the quarter's report is run, I put a copy of the data in a table from which to draw my previous records from for my next quarter's report. The current is then retrieved from the expenditure table with a date criteria. The summary report works well, as there is only one total from each table.
However the next report requires the detail of those summaries. For any one account the number of transactions vary in the current records and the previous records, and duplicates occur.
I was unable to attach the excel file with the data.
Is there someway I can send to you so you can see what I am trying to explain.

Thanks

 

[CJ_London]

use the advanced editor and you can add attachments, zip your db and excel files together and upload the zip file.

Only include some test data to demonstrate the problem.

I don't know why you need a separate table - you should be able to do this with a query based on the transaction date or whatever

 

[Cristina]

Good day,
The reason why (with my limited knowledge) I created two table is because the report needs to have the previous records in one column and the current in the next column as shown in TEST QUERY in the attached DBase.
Click on OPEN REPORT FORM, choose data from 1/7/14 to 31/12/14.
Hope my explanation is clear.
Grateful for your help.

Please bear with me...I dont see the advanced editor, and an error is occurring like yesterday..Invalid file. I tried sending and Execl yesterday , to day is a Access file.

 

[Cristina]

Main Heading Item Code Budget Previous Records Current records 2 2.2.1.1 $4,684.00 372.64 80.37 2 2.2.1.1 $4,684.00 548 80.37 2 2.2.1.1 $4,684.00 1337.11 80.37 2 2.2.1.1 $4,684.00 87.68 80.37 2 2.2.1.1 $4,684.00 -2.59 80.37

 

[Cristina]

I was trying to send an excel spreadsheet in the post put it did not come out as a table

 

[CJ_London]

click here for the advanced editor

Regret I am out of time, so someone else will need to help