Duplicates on a report

[Sarah74]

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.

 

[Maurice]

If it's just the duplicates you want to hide in the report then you can set
that option in the report properties [show duplicates=no]. If you want the
source to be correct you should modify the query so it provides you with the
correct dataset you want to accomplish.

hth

 

[KARL DEWEY]

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

 

[Sarah74]

Do you have any ideas or suggestions on how I could fix the query? I want it
to pick up any record that has any one of three fields with a certain date.
i.e., if the start date, renewal date or expiration date all fall between
1/1/08 and 12/31/08 I want it to show up on the query. What I put in the
query for Criteria was "Between [From what date?] And [Thru what date?]" and
the same thing in the or section.

If it's just the duplicates you want to hide in the report then you can set
that option in the report properties [show duplicates=no]. If you want the
source to be correct you should modify the query so it provides you with the
correct dataset you want to accomplish.

hth
--
Maurice Ausum

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.

 

[Maurice]

Sarah,

We can help you trying to improve the query but we do need to know how the
query is setup. Does it contain one table or more.

--
Maurice Ausum

Do you have any ideas or suggestions on how I could fix the query? I want it
to pick up any record that has any one of three fields with a certain date.
i.e., if the start date, renewal date or expiration date all fall between
1/1/08 and 12/31/08 I want it to show up on the query. What I put in the
query for Criteria was "Between [From what date?] And [Thru what date?]" and
the same thing in the or section.

If it's just the duplicates you want to hide in the report then you can set
that option in the report properties [show duplicates=no]. If you want the
source to be correct you should modify the query so it provides you with the
correct dataset you want to accomplish.

hth
--
Maurice Ausum

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.

 

[Sarah74]

There are 3 tables.

Sarah,

We can help you trying to improve the query but we do need to know how the
query is setup. Does it contain one table or more.

--
Maurice Ausum

Do you have any ideas or suggestions on how I could fix the query? I want it
to pick up any record that has any one of three fields with a certain date.
i.e., if the start date, renewal date or expiration date all fall between
1/1/08 and 12/31/08 I want it to show up on the query. What I put in the
query for Criteria was "Between [From what date?] And [Thru what date?]" and
the same thing in the or section.

If it's just the duplicates you want to hide in the report then you can set
that option in the report properties [show duplicates=no]. If you want the
source to be correct you should modify the query so it provides you with the
correct dataset you want to accomplish.

hth
--
Maurice Ausum


:

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.

 

[John W. Vinson]

Do you have any ideas or suggestions on how I could fix the query? I want it
to pick up any record that has any one of three fields with a certain date.
i.e., if the start date, renewal date or expiration date all fall between
1/1/08 and 12/31/08 I want it to show up on the query. What I put in the
query for Criteria was "Between [From what date?] And [Thru what date?]" and
the same thing in the or section.

You need the same criterion three times, one under each field, all on separate
lines in the query grid.

If there are criteria on other fields you'll need to make three copies of them
as well, one on each OR line.

 

[Sarah74]

I kind of cheated and in the properties field checked yes for "Unique
Fields". I don't know if that caused another problem so that now another new
lease that I added with a commencement date that falls in the range doesn't
show up in the query. Either way, the SQL is below.

SELECT DISTINCTROW TblTenants.[Tenant Name], TblTenants.[Suite #],
TblTenants.Property, TblTenants.[Date of Lease], TblTenants.[Commencement
Date], TblTenants.[Renewal Date], TblTenants.[Expiration Date],
TblRentReview.Date
FROM (TblTenants INNER JOIN TblRent ON TblTenants.[Access ID#] =
TblRent.[Access ID]) INNER JOIN TblRentReview ON TblTenants.[Access ID#] =
TblRentReview.[Access ID]
WHERE (((TblTenants.[Commencement Date]) Between [From what date?] And [Thru
what date?])) OR (((TblTenants.[Renewal Date]) Between [From what date?] And
[Thru what date?])) OR (((TblRentReview.Date) Between [From what date?] And
[Thru what date?]));

 

[KARL DEWEY]

The problem may be TblRent.[Access ID] in the query.
Why do you have it?
Do you have multiple records for each [Access ID] ?
--
KARL DEWEY
Build a little - Test a little

I kind of cheated and in the properties field checked yes for "Unique
Fields". I don't know if that caused another problem so that now another new
lease that I added with a commencement date that falls in the range doesn't
show up in the query. Either way, the SQL is below.

SELECT DISTINCTROW TblTenants.[Tenant Name], TblTenants.[Suite #],
TblTenants.Property, TblTenants.[Date of Lease], TblTenants.[Commencement
Date], TblTenants.[Renewal Date], TblTenants.[Expiration Date],
TblRentReview.Date
FROM (TblTenants INNER JOIN TblRent ON TblTenants.[Access ID#] =
TblRent.[Access ID]) INNER JOIN TblRentReview ON TblTenants.[Access ID#] =
TblRentReview.[Access ID]
WHERE (((TblTenants.[Commencement Date]) Between [From what date?] And [Thru
what date?])) OR (((TblTenants.[Renewal Date]) Between [From what date?] And
[Thru what date?])) OR (((TblRentReview.Date) Between [From what date?] And
[Thru what date?]));

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

 

[Sarah74]

That was genius...that worked great. You wouldn't have any idea why the new
record I put isn't showing up would you?

The problem may be TblRent.[Access ID] in the query.
Why do you have it?
Do you have multiple records for each [Access ID] ?
--
KARL DEWEY
Build a little - Test a little

I kind of cheated and in the properties field checked yes for "Unique
Fields". I don't know if that caused another problem so that now another new
lease that I added with a commencement date that falls in the range doesn't
show up in the query. Either way, the SQL is below.

SELECT DISTINCTROW TblTenants.[Tenant Name], TblTenants.[Suite #],
TblTenants.Property, TblTenants.[Date of Lease], TblTenants.[Commencement
Date], TblTenants.[Renewal Date], TblTenants.[Expiration Date],
TblRentReview.Date
FROM (TblTenants INNER JOIN TblRent ON TblTenants.[Access ID#] =
TblRent.[Access ID]) INNER JOIN TblRentReview ON TblTenants.[Access ID#] =
TblRentReview.[Access ID]
WHERE (((TblTenants.[Commencement Date]) Between [From what date?] And [Thru
what date?])) OR (((TblTenants.[Renewal Date]) Between [From what date?] And
[Thru what date?])) OR (((TblRentReview.Date) Between [From what date?] And
[Thru what date?]));

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.

 

[KARL DEWEY]

No, but I do not understand your statement -- I kind of cheated and in the
roperties field checked yes for "Unique Fields".
--
KARL DEWEY
Build a little - Test a little

That was genius...that worked great. You wouldn't have any idea why the new
record I put isn't showing up would you?

The problem may be TblRent.[Access ID] in the query.
Why do you have it?
Do you have multiple records for each [Access ID] ?
--
KARL DEWEY
Build a little - Test a little

I kind of cheated and in the properties field checked yes for "Unique
Fields". I don't know if that caused another problem so that now another new
lease that I added with a commencement date that falls in the range doesn't
show up in the query. Either way, the SQL is below.

SELECT DISTINCTROW TblTenants.[Tenant Name], TblTenants.[Suite #],
TblTenants.Property, TblTenants.[Date of Lease], TblTenants.[Commencement
Date], TblTenants.[Renewal Date], TblTenants.[Expiration Date],
TblRentReview.Date
FROM (TblTenants INNER JOIN TblRent ON TblTenants.[Access ID#] =
TblRent.[Access ID]) INNER JOIN TblRentReview ON TblTenants.[Access ID#] =
TblRentReview.[Access ID]
WHERE (((TblTenants.[Commencement Date]) Between [From what date?] And [Thru
what date?])) OR (((TblTenants.[Renewal Date]) Between [From what date?] And
[Thru what date?])) OR (((TblRentReview.Date) Between [From what date?] And
[Thru what date?]));


:

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.

 

[Sarah74]

Before you suggested I delete the rent table, I went into the properties for
the query and there is a field called Unique fields and I checked yes for it
thinking that would delete any duplicate records. It worked and only
returned one record but I changed it back to no when you told me to delete
the rent table and that worked.

No, but I do not understand your statement -- I kind of cheated and in the
roperties field checked yes for "Unique Fields".
--
KARL DEWEY
Build a little - Test a little

That was genius...that worked great. You wouldn't have any idea why the new
record I put isn't showing up would you?

The problem may be TblRent.[Access ID] in the query.
Why do you have it?
Do you have multiple records for each [Access ID] ?
--
KARL DEWEY
Build a little - Test a little


:

I kind of cheated and in the properties field checked yes for "Unique
Fields". I don't know if that caused another problem so that now another new
lease that I added with a commencement date that falls in the range doesn't
show up in the query. Either way, the SQL is below.

SELECT DISTINCTROW TblTenants.[Tenant Name], TblTenants.[Suite #],
TblTenants.Property, TblTenants.[Date of Lease], TblTenants.[Commencement
Date], TblTenants.[Renewal Date], TblTenants.[Expiration Date],
TblRentReview.Date
FROM (TblTenants INNER JOIN TblRent ON TblTenants.[Access ID#] =
TblRent.[Access ID]) INNER JOIN TblRentReview ON TblTenants.[Access ID#] =
TblRentReview.[Access ID]
WHERE (((TblTenants.[Commencement Date]) Between [From what date?] And [Thru
what date?])) OR (((TblTenants.[Renewal Date]) Between [From what date?] And
[Thru what date?])) OR (((TblRentReview.Date) Between [From what date?] And
[Thru what date?]));


:

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I have a report that I generated from a query. On the query, I need it to
pick up data if the dates in any of the three specific fields I give it are
between two certain dates. In the query, I put the criteria for the date in
one field and the other two fields they were in the "or" section. There were
several that came back multiple times. I hope that makes sense.