duplicates on report

[Cristina]

Hello All,
I need some help with a report.
I have a table with expenditure that is reported on quarterly. summary report as follows
Budgeted Amt spent previously Amt spent this quarter Balance

I also have to do a detail report, in the same format. It is not working out, as eg there are 200 records previously, and only 100 this period, records are duplicated to where there are 200 in each column. I have tried the three types of joins. I have tried in properties,,Unique values, Unique records.
Grateful for some assistance.

Cristina

 

[plog]

Can you post the relevant data in an access database so we can see an example?

 

[Cristina]

I tried attaching a zip file but it did not attach. I went to advanced, and browse and upload, and it said invalid type file.

Any suggestions?

Thanks

Cristina

 

[plog]

Make a copy of the database, strip out everything but the relevant parts, compact and repair, then just upload the database as is, no zipping.

 

[Cristina]

when the dbase opens, click on open report form The queries read from the combo box. Eg enter 7/1/14 for start date and today date for ending. Previous records is 73 current is 80 yet when I run the Detail Rpt, when I bring both previous and current, I get 462 records,

Thanks four assistance.

Cristina

 

[JHB]

There is no way to open a report in the form in which you type in/choose the dates - AND there is NO report in you uploaded database!

 

[plog]

She's talking about running queries. They feed off the Dialog form.

I honestly don't know what to tell you about your query producing unexpected results. From someone who only knows SQL and not your specific data nor your intentions, your queries are working correctly.

Here's some things I think you should research or try to understand:

1. When you use a LEFT JOIN and apply criteria to the joined table (the one from which you are only showing matching data), you have undone your LEFT JOIN making it a regular INNER JOIN (shows only matching data).

In 'Detail Previous' you have applied criteria to Expenditure.[Data of payment]. That negated the LEFT JOIN into Expenditure and your query will now only show matching records.

2. An aggregate query (one that uses the Sigma/Summation signal) generates your data at a different level than a regular query. It's also called a totals query because it totals your data, which means it takes all similar data and merges their data into 1 record of the output.

This is probably the cause of your record discrepancy. Detail Previous is a regular query, which means it shows every row it finds. Detail Rpt is an aggregate query which means it shows 1 record for every set of rows that are unique per your instructions.

You should really research about aggregate queries and how the Group By clause effects your data. This is why your record results don't match your expectations.

Lastly and unrelated, you should name your tables/fields/queries better. Try to use only alphanumeric characters. That means avoid spaces and any special characters (#, %, etc.). Also, you have a field named 'Date' which is a reserved word. Try not to name your fields so generically. I would prefix it with what that date represents (e.g. SalesDate, TerminationDate, BahMitzvahDate, etc.).

 

[Cristina]

Thanks very much for your help and suggestions. I will research the aggregate query before I totally give up and export my data to excel.
Regards
Cristina