Duplicate Values in Queries

[bdehning]

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?

 

[khawkins]

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

 

[bdehning]

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.

 

[khawkins]

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

 

[khawkins]

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.

 

[bdehning]

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.

 

[bdehning]

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.

 

[khawkins]

To start off with I am having a hard time getting a good picture in my head of the table structure. Having said that I think you can either use a cross tab query or write a module to build the record set for the report. I think in your case where you have so many calculations to do I would write a code module. I would build the record set in steps. First build a blank table in you database with the fields you want. Then you can start filling the table with current info with the module. First line would be to empty the table of records and then start to fill it again. To me it looks like your key field should be the consultant. I would hope that every consultant has a unique ID code,(that would make the hole process very simple). In the module you could use an input box to get the date range you want or you can hard code something like the last 30 days or what ever time period you want to look at.
Just remember keep each step in the module simple fill only one or two fields with each step in the code.
Another advantage in the code method is that you can change any small part of the code and only affect a small part of the report. Using stacked queries to do this one change in the first query and it will mess the report up all together.
Another reason I say to build a table and then fill it with the information you want for the report is that you will more than likely have more questions about this data after the report is distributed. This way you have the data and can run other small queries to answer those questions. Happens to me all the time.
Another thing you may want to think about is fill the table with every bit of data you can about each record entry and then go out to XL and have a pivot table built to look at this table and that can be on the network so all can check out the numbers. No paper doc to print unless the viewer wants to and they can print from the pivot table in XL. Then you can put a timing mode in the database to run the module at end of every day , week , month or what every time frame you need. Much better than printing reports on demand. Easy for the users also.