Problem designing Queries with dates

[dwaugh]

I currently have a Query with two tables (tblDates, and WIP). tblDate
has two fields, an ID field and a date field. The Date field is
listing of dates from 1/1/1998 through 12/31/2015. The ID field i
an autonumber field

The Date field from tblDates is linked to another Date field withi
the WIP table via a one-to-many relationship. Each date withi
tblDates only appears once, but the same date can appear numerou
times within each row of the WIP table

The criteria in the Query for the Date field from tblDates is a
follows
Between #1/1/2005# And #3/31/2005
With join properties set to option 2 - Include all records fro
tblDates and only those records from WIP where the joined fields ar
equal

What I am looking for
I am looking for a listing of dates (from 1/1/2005 through 3/31/2005
in the first column, as supplied by the date field from tblDates.
Then, if data exists on the same date for dates from the linked fiel
in the WIP table, to have these values displayed, if not, then thes
values should be blank (NULL)

But, I am only getting results for where both the date fields exis
within both tables (tblDates and WIP).

Any help will be greatly appreciated

Thanks
Davi

 

[Jeff Boyce]

Open the query in design mode. Highlight the line joining the two tables.
Change the property of the join to display all of your first table, and any
matching values from the second.

Regards

Jeff Boyce
<Access MVP>

 

[John Spencer (MVP)]

OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

 

[dwaugh]

Jeff and John - Thanks for your replies

Jeff
I had already changed the join properties between the two tables t
reflect what you offered, but it still didn't produce the result
that I am after

Here is the SQL of my Query. I truncated it a bit so that it onl
looks for one employee = 1 (WIP.WempID) and a subset range of dates
1/1/2005 thru 3/31/2005)

SELECT tblDates.Dates, WIP.Wdate, WIP.WEmpLName, WIP.WempID
Sum(WIP.Whours) AS SumOfWhour
FROM tblDates LEFT JOIN WIP ON tblDates.Dates = WIP.Wdat
GROUP BY tblDates.Dates, WIP.Wdate, WIP.WEmpLName, WIP.WempI
HAVING (((tblDates.Dates) Between #1/1/2005# And #3/31/2005#) AN
((WIP.WempID)=1)
ORDER BY tblDates.Dates, WIP.WEmpLName

There is a ONE TO MANY RELATIONSHIP from the tblDates table to the WI
table. A particularv date only appears once in the tblDates table
and the same date can appear on numerous rows within the WIP table

As I stated before, I am looking for the following in the datashee
view
First column is Dates from the tblDates table - A listing of dates
shown via the Criteria

Then, records from the WIP table where the joined fields are equal.
The joined fields are - tblDates.Dates - WIP.Wdat

Unfortunately I do not get the results I need

http://www.classic-computer.net/access/image1.jp
As you can see, Image1.jpg (above) shows my two tables within my quer
and the link between both of them

[img:f24752825e]http://www.classic-computer.net/access/image2.jpg[/img:f24752825e
For example, Image2.jpg (above) shows that Employee #1 had missin
days from Jaunary 2005. Even though Employee #1 does not have a
entry dated 1/1/2005, I want the date field from tblDates to display
and with blank entries for those fields from the WIP table

[img:f24752825e]http://www.classic-computer.net/access/image3.jpg[/img:f24752825e
Image3.jpg (above) displays the lower portion of my Query

Thanks again
Davi

 

[dwaugh]

Moving to the top of the list