SQL Query Design - Date Range Help Needed

[StevenCV]

I have recently posted the following thread for help creating a report (https://www.accessforums.net/showthr...164#post107164).



I am new to Access (this is my first database) so I am learning as I go. From what I have read, I need to use SQL to get what I need.

I have a database that includes fields for inputting details about visits. There are 14 per record, and each bit has fields "1) Date:", "1) Hrs:", 1) Min:". Underneath that I have fields "2) Date:", "2) Hrs:", 2) Min:". And so on through to 14.

I have a report that totals all the Hrs fields, and totals all the Min fields, and then converts them to the proper HH:MM format (note, these Hrs and Min fields are all formatted as Number > General Number).

What I need to do now is repeat the report, but with the added complexity of only showing these Hrs and Mins that are within a date range.

I have started building a query, and this used a form to input date from and date to. Howevever I had a couple of problems with this:

1) It creates the SQL code as AND, so it only returns records where BOTH the dates are within the range.

2) If I change the AND to 'OR', it does pick up every record that has a visit within these dates from the date1 or date2 fields, BUT it also displays BOTH visits from date1 and date2 even if one of thoise dates is NOT within the dates.

I need it to only show the visit hrs and min fields if the corresponding date field is within the date range. I thought I had cracked it when I learned about the 'UNION ALL' statement.

I went ahead and wrote this code:

SELECT [Crisis Calls Attended].[Log No:], [Crisis Calls Attended].[Client Name:], [Crisis Calls Attended].[Client Address:]
FROM [Crisis Calls Attended]

UNION ALL

SELECT[Crisis Calls Attended].[1) Date:], [Crisis Calls Attended].[1) Hrs:], [Crisis Calls Attended].[1) Min:]
FROM [Crisis Calls Attended]
WHERE ((([Crisis Calls Attended].[1) Date:]) Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate]))

UNION ALL

SELECT[Crisis Calls Attended].[2) Date:], [Crisis Calls Attended].[2) Hrs:], [Crisis Calls Attended].[2) Min:]
FROM [Crisis Calls Attended]
WHERE ((([Crisis Calls Attended].[2) Date:]) Between [Forms]![Date Range].[StartDate] And [Forms]![Date Range].[EndDate]));

As I am sure you all know, but I didn't, this didn't work. Well, it did, but it didn't. It actually brought all the correct information over, but it appended it underneath my results as data, rather than creating new fields across the top.

I am sure I am close to getting a solution. Can anyone point me in the right direction?

[aytee111]

The first thing you need to do is learn about "database normalization". Your three fields should be on a separate table. The data entry will use a form for the parent table and a subform for the child table. The data will then be a lot easier to deal with throughout the database - forms, reports and queries.