SQL Comparing dates from form, Getting repeat data

[pdogg]

This is my first post, so I hope I'm not too detailed or ambiguous. I'm also
new to using SQL. Here is my problem:

I have a table with one data value for each month of the year. A form
selects what ranges of dates I'd like to compare.
Compare: StartDate1 to FinishDate1
To: StartDate2 to FinishDate2
(To Clarify: If you wanted to compare what happened between January and
February to what happened between March and April: StartDate1 = 1/1/2003,
FinishDate1 = 2/28/2003; StartDate2 = 3/1/2003, FinishDate2 = 4/30/2003.)

I have a query (qrydata1) that selects the data from the first range (using
the example dates above, I would get Jan and Feb data), and a second query
(qrydata2) that selects the data from the second range (using the example
dates, I would get Mar and Apr data). The problem comes when I try to compare
the two.

My third query attempts to compare the first two queries. The following is
my SQL for it:

SELECT qrydata1.date AS [Date 1], qrydata1.data AS [Data 1], qrydata2.date
AS [Date 2], qrydata2.data AS [Data 2]
FROM qrydata1, qrydata2;

This results in repeat data:
BillDate 1 Data 1 BillDate 2 Data 2
1/1/2003 12309 3/1/2003 34878
2/1/2003 48237 3/1/2003 34878
1/1/2003 12309 4/1/2003 38200
2/1/2003 48237 4/1/2003 38200

Is there some JOIN I can add, or some other method to populate the query
where I only get the data I want once? I tried INNER JOIN, but ended up
getting nothing since the BillDate fields aren't equal. Maybe I'm confusing
the issue with too many queries.

I would appreciate any help.

Thanks,
pdogg

 

[pdogg]

Sorry to confuse the issue. I just read my post and realized I didn't update
it completely since I changed all fields named 'Date' to 'BillDate' to avoid
later problems. If you see a 'Date' field name, it has since been changed.

Sorry for the confusion.

pdogg

 

[darrep]

Try using the DISTINCT Operators in some or all of your 3 queries.

I suggest first adding it into your 3rd query that does the comparison.

Just change the SELECT (rest of query)
to SELECT DISTINCT (rest of query)

HTH!

Sorry to confuse the issue. I just read my post and realized I didn't update
it completely since I changed all fields named 'Date' to 'BillDate' to avoid
later problems. If you see a 'Date' field name, it has since been changed.

Sorry for the confusion.

pdogg

This is my first post, so I hope I'm not too detailed or ambiguous. I'm also
new to using SQL. Here is my problem:

I have a table with one data value for each month of the year. A form
selects what ranges of dates I'd like to compare.
Compare: StartDate1 to FinishDate1
To: StartDate2 to FinishDate2
(To Clarify: If you wanted to compare what happened between January and
February to what happened between March and April: StartDate1 = 1/1/2003,
FinishDate1 = 2/28/2003; StartDate2 = 3/1/2003, FinishDate2 = 4/30/2003.)

I have a query (qrydata1) that selects the data from the first range (using
the example dates above, I would get Jan and Feb data), and a second query
(qrydata2) that selects the data from the second range (using the example
dates, I would get Mar and Apr data). The problem comes when I try to compare
the two.

My third query attempts to compare the first two queries. The following is
my SQL for it:

SELECT qrydata1.date AS [Date 1], qrydata1.data AS [Data 1], qrydata2.date
AS [Date 2], qrydata2.data AS [Data 2]
FROM qrydata1, qrydata2;

This results in repeat data:
BillDate 1 Data 1 BillDate 2 Data 2
1/1/2003 12309 3/1/2003 34878
2/1/2003 48237 3/1/2003 34878
1/1/2003 12309 4/1/2003 38200
2/1/2003 48237 4/1/2003 38200

Is there some JOIN I can add, or some other method to populate the query
where I only get the data I want once? I tried INNER JOIN, but ended up
getting nothing since the BillDate fields aren't equal. Maybe I'm confusing
the issue with too many queries.

I would appreciate any help.

Thanks,
pdogg

 

[pdogg]

Thanks for the reply. I tried the SELECT DISTINCT with no luck. It just order
the data differently.

Try using the DISTINCT Operators in some or all of your 3 queries.

I suggest first adding it into your 3rd query that does the comparison.

Just change the SELECT (rest of query)
to SELECT DISTINCT (rest of query)

HTH!

Sorry to confuse the issue. I just read my post and realized I didn't update
it completely since I changed all fields named 'Date' to 'BillDate' to avoid
later problems. If you see a 'Date' field name, it has since been changed.

Sorry for the confusion.

pdogg

This is my first post, so I hope I'm not too detailed or ambiguous. I'm also
new to using SQL. Here is my problem:

I have a table with one data value for each month of the year. A form
selects what ranges of dates I'd like to compare.
Compare: StartDate1 to FinishDate1
To: StartDate2 to FinishDate2
(To Clarify: If you wanted to compare what happened between January and
February to what happened between March and April: StartDate1 = 1/1/2003,
FinishDate1 = 2/28/2003; StartDate2 = 3/1/2003, FinishDate2 = 4/30/2003.)

I have a query (qrydata1) that selects the data from the first range (using
the example dates above, I would get Jan and Feb data), and a second query
(qrydata2) that selects the data from the second range (using the example
dates, I would get Mar and Apr data). The problem comes when I try to compare
the two.

My third query attempts to compare the first two queries. The following is
my SQL for it:

SELECT qrydata1.date AS [Date 1], qrydata1.data AS [Data 1], qrydata2.date
AS [Date 2], qrydata2.data AS [Data 2]
FROM qrydata1, qrydata2;

This results in repeat data:
BillDate 1 Data 1 BillDate 2 Data 2
1/1/2003 12309 3/1/2003 34878
2/1/2003 48237 3/1/2003 34878
1/1/2003 12309 4/1/2003 38200
2/1/2003 48237 4/1/2003 38200

Is there some JOIN I can add, or some other method to populate the query
where I only get the data I want once? I tried INNER JOIN, but ended up
getting nothing since the BillDate fields aren't equal. Maybe I'm confusing
the issue with too many queries.

I would appreciate any help.

Thanks,
pdogg