Duplicate Values in Queries

[Guest]

I am trying to use a query to bring together2 queries from 2 forms in order to produce 1 report which will sort by Date to be able to obtain monthly totals.

The query returns duplicate values in one date column no matter what join properties I try. Do I need to consider a subreport or can I exclude duplicate vales somewhere on the form?

The 2 seperate queries return the desired results but I cant join them and still get the results I want.

Any ideas on best way to proceed?

 

[Guest]

I guess to help I need a little more info. From your question it sounds like you have two forms where the record set for each form is produced by two different queries. Question 1 is the data the two queries are based on in the same table/tables?
Question 2 you want this summary report separate from the two forms I assume?
If so why not build a new query that is setup to summarize the data for the report? Then base your report on that summarized data. Just make sure you us the grouping function if you are using the query designer or if you are writing the sql statement then just group it there. That should fix the dups problem. Just remember that if any of the data in the fields are different where the key field is the same you will still get two records, even if you group them. If that is the case then break the date down so it is the same in all like records. (example only look at which month ,week or year).

Keith

 

[Guest]

Data is not in same table/tables. I had to create a different table for one of the results I needed and this created the use of a form created after the initial forms were created.

Should I try to connect this forms/tables?

I am trying to create one report which will combine both query results by use of a Date field.

 

[Guest]

That would explain the dup records. Unless the date is unique in at least one table you will always end up with dups. Can you make a relation ship that has at least one unique field in these tables? If not can you use two fields for the join. Say your date field and one of the other fields. The two together may give you a one to one relation ship that you can group on. What I think you are seeing now is that the query sees that the same date is in both tables more than once so it creates a record from each table twice. Meaning
Table 1 has
Date name
01/01/04 me

Table 2 has
Date place
01/01/04 home

results are coming out as
01/01/04 me from table 1
01/01/04 me from table 2
01/01/04 home table 2
01/01/04 home table 1

it may only do one of the dups or both hard to say. You could use a different join type say right or left but that would only eliminate one of them if you are getting both. Best thing to do is create a unique field and either create a one to many or one to one join. Next best is to use two fields for joins as I said above. Hope that helps.

Keith

 

[Guest]

That would explain the dup records. Unless the date is unique in at least one table you will always end up with dups. Can you make a relation ship that has at least one unique field in these tables? If not can you use two fields for the join. Say your date field and one of the other fields. The two together may give you a one to one relation ship that you can group on. What I think you are seeing now is that the query sees that the same date is in both tables more than once so it creates a record from each table twice. Meaning
Table 1 has
Date name
01/01/04 me

Table 2 has
Date place
01/01/04 home

results are coming out as
01/01/04 me from table 1
01/01/04 me from table 2
01/01/04 home table 2
01/01/04 home table 1

it may only do one of the dups or both hard to say. You could use a different join type say right or left but that would only eliminate one of them if you are getting both. Best thing to do is create a unique field and either create a one to many or one to one join. Next best is to use two fields for joins as I said above. Hope that helps.

Keith

 

[Van T. Dinh]

You probably need to post the relevant structure of your Tables, what you
want in your Report and the SQL Strings of the Queries.

RecordSource for Report are not related to the Forms so don't confuse the
problem more by referring to the Form(s).

--
HTH
Van T. Dinh
MVP (Access)

Data is not in same table/tables. I had to create a different table for

one of the results I needed and this created the use of a form created after
the initial forms were created.

 

[Guest]

I decided to back to beginning to get first part to work.

I am using a query with Fields! I even have countof fields currently. Having trouble getting report started.

Assigned Consultant
Date Written Report Sent - Month/Date Field
Call Type - Other, Service, Agency
Type of Service - Phone, Physical

I would first like to create report that would list person(Assigned Consultant) and then list months of the year(Date Written Report Sent). Under each month I would like to see list of Call Type(Other, Service or Agency with totals for each Type of Service(Phone, Physical) and then ultimately month total. I would like this for each month.

For example
Bdehning
January 2004 Phone Physical
Other 3 4
Service 2 3
Agency 4 3
-- Total Call Type 9 10

I plan to add other items as well but they could be added to existing report if you can help out.
Brian

You probably need to post the relevant structure of your Tables, what you
want in your Report and the SQL Strings of the Queries.

RecordSource for Report are not related to the Forms so don't confuse the
problem more by referring to the Form(s).

--
HTH
Van T. Dinh
MVP (Access)

Data is not in same table/tables. I had to create a different table for

one of the results I needed and this created the use of a form created after
the initial forms were created.

 

[Guest]

Ok I figured out the first part that I asked about on my previous post.

Now I need to add the other query which is a from a tbel not connected to other tables. This table is called Monthly Report Special Activities.

This query has the following fields Location Servicing Division and Assigned Consultant which are fields in the other query which is the first part of report from previous post I have working.

I now want to be able to add the following fields to the report from the second query
Staff Meeting/Training
Vacation/Sick
Special Project

These fields are number fields with 2 fixed digits. Example 1.75

I would like to get totals by Consultant and then by Date Written Report Sent which I can add to the table for this query.

I would like to get totals by month for the 3 fields above.

Can I write code or do I need to join to previous query

I would like help.