Date limited query?????

[Sara P.]

Hi,

Ok here's the scenario........I have data going back to Jan/06 but when I
run a query based on date submitted with no criteria attached my query only
reports as far back as Jan/07. How do I get around this or more importantly
why is it happening?
Can someone please enlighten me!

Thanks,
Sara

 

[NthDegree via AccessMonster.com]

Sara,
It would be very helpful if you would submit the query you are trying to run
(sql mode) and how you are setting the date (programmatically, manually etc.)

 

[Sara P.]

Hi,
I'm just using the wizard and it's part of the table associated with the
query I'm trying to create. Sorry I'm new at this so I'm not sure how I would
go about submitting the query I'm trying to run. Please help.

Thanks,
Sara

 

[NthDegree via AccessMonster.com]

Sara, if you open the query in desgin mode you should see an icon just below
the menu option "File" and if you mouse over it, it should say "View" click

Hi,
I'm just using the wizard and it's part of the table associated with the
query I'm trying to create. Sorry I'm new at this so I'm not sure how I would
go about submitting the query I'm trying to run. Please help.

Thanks,
Sara

Sara,
It would be very helpful if you would submit the query you are trying to run

[quoted text clipped - 10 lines]

 

[Sara P.]

Hi,

OK here you go, hope this is what you need


SELECT Contacts.DateSubmitted, [Position Types].[Position Type],
Contacts.FirstName, Contacts.LastName, Contacts.City, Contacts.HomePhone,
Contacts.MobilePhone, Contacts.Notes AS Contacts_Notes
FROM ([Position Types] INNER JOIN Contacts ON [Position Types].[Position
Type ID] = Contacts.PositionTypeID) INNER JOIN Calls ON Contacts.ContactID =
Calls.ContactID
ORDER BY Contacts.DateSubmitted DESC;

Sara, if you open the query in desgin mode you should see an icon just below
the menu option "File" and if you mouse over it, it should say "View" click

Hi,
I'm just using the wizard and it's part of the table associated with the
query I'm trying to create. Sorry I'm new at this so I'm not sure how I would
go about submitting the query I'm trying to run. Please help.

Thanks,
Sara

Sara,
It would be very helpful if you would submit the query you are trying to run

[quoted text clipped - 10 lines]

Thanks,
Sara

--
Never let it be said that I was totally comitted to sanity. It is the dark
places of my mind that fascinate me.

NthDegree

 

[NthDegree via AccessMonster.com]

Sara,

If you are running this query as-is then i suspect that entries older than a
certain date don't have matching rows in either the of the two tables you are
doing an inner join on. If the table on the left (in this case Position Types)
does not have a matching row on the right table (Contacts) and the same
applies to the other inner join you have. Try changing the last inner join
(where i suspect the problem is) to LEFT JOIN (you can do this in sql view
and then run it), if that doesnt do it change it back to the way it was and
change the first inner join to left join. I suspect the real problem is that
your contacts table should be the main table (you have it being a child of
(position types).

Maybe you really want this (copy and past into a new query in sql mode)

SELECT Contacts.DateSubmitted, [Position Types].[Position Type],
Contacts.FirstName, Contacts.LastName, Contacts.City, Contacts.HomePhone,
Contacts.MobilePhone, Contacts.Notes AS Contacts_Notes
FROM (Contacts LEFT JOIN [Position Types] ON [Position Types].[Position
Type ID] = Contacts.PositionTypeID) LEFT JOIN Calls ON Contacts.ContactID =
Calls.ContactID
ORDER BY Contacts.DateSubmitted DESC;

Hi,

OK here you go, hope this is what you need

SELECT Contacts.DateSubmitted, [Position Types].[Position Type],
Contacts.FirstName, Contacts.LastName, Contacts.City, Contacts.HomePhone,
Contacts.MobilePhone, Contacts.Notes AS Contacts_Notes
FROM ([Position Types] INNER JOIN Contacts ON [Position Types].[Position
Type ID] = Contacts.PositionTypeID) INNER JOIN Calls ON Contacts.ContactID =
Calls.ContactID
ORDER BY Contacts.DateSubmitted DESC;

Sara, if you open the query in desgin mode you should see an icon just below
the menu option "File" and if you mouse over it, it should say "View" click

[quoted text clipped - 14 lines]

 

[Sara P.]

Hi,

OK so that didn't really work. I tried three different ways: 1. With the
expressions that you gave me which resulted in having to specify a position
(which is fine) when I opened the query but gave me no data. 2. With the
first join an Inner and the last join a left one as you had suggested and
that resulted in the same thing. Now when I go to the design view in either
query I get an error message that Access can't represent the join expression
in the design view but shows everything when I hit OK. 3. With the first join
as a left and the last as an Inner and that kicked me out of access all
together and prompted me to send an error report to Microsoft (which I did).
My new question does the whole row have to match or should it work as long as
there is a position attached to a name?

Thanks again for letting me pick your brain!

Sara

Sara,

If you are running this query as-is then i suspect that entries older than a
certain date don't have matching rows in either the of the two tables you are
doing an inner join on. If the table on the left (in this case Position Types)
does not have a matching row on the right table (Contacts) and the same
applies to the other inner join you have. Try changing the last inner join
(where i suspect the problem is) to LEFT JOIN (you can do this in sql view
and then run it), if that doesnt do it change it back to the way it was and
change the first inner join to left join. I suspect the real problem is that
your contacts table should be the main table (you have it being a child of
(position types).

Maybe you really want this (copy and past into a new query in sql mode)

SELECT Contacts.DateSubmitted, [Position Types].[Position Type],
Contacts.FirstName, Contacts.LastName, Contacts.City, Contacts.HomePhone,
Contacts.MobilePhone, Contacts.Notes AS Contacts_Notes
FROM (Contacts LEFT JOIN [Position Types] ON [Position Types].[Position
Type ID] = Contacts.PositionTypeID) LEFT JOIN Calls ON Contacts.ContactID =
Calls.ContactID
ORDER BY Contacts.DateSubmitted DESC;

Hi,

OK here you go, hope this is what you need

SELECT Contacts.DateSubmitted, [Position Types].[Position Type],
Contacts.FirstName, Contacts.LastName, Contacts.City, Contacts.HomePhone,
Contacts.MobilePhone, Contacts.Notes AS Contacts_Notes
FROM ([Position Types] INNER JOIN Contacts ON [Position Types].[Position
Type ID] = Contacts.PositionTypeID) INNER JOIN Calls ON Contacts.ContactID =
Calls.ContactID
ORDER BY Contacts.DateSubmitted DESC;

Sara, if you open the query in desgin mode you should see an icon just below
the menu option "File" and if you mouse over it, it should say "View" click

[quoted text clipped - 14 lines]

Thanks,
Sara

--
Never let it be said that I was totally comitted to sanity. It is the dark
places of my mind that fascinate me.

NthDegree

 

[NthDegree via AccessMonster.com]

Sarah,

No it only matches on the fields you specfiy, (e.g. from contact inner join
[position type] on contact.key1 = [position type].key2

if you specify inner join there MUST be a match in both tables for the row to
be shown. So in the above case, if there was a row in table contact with a
key1 value of 1234 but no row in position type that had a key2 value of 1234,
you would not be shown the row in contact.

If you used LEFT JOIN in the above case you would see the contact table row,
inner join shows all rows from the "left" table.

RIGHT JOIN is just the opposite, it will show all of the rows of the "right"
table (position type in the above case) but only the rows from contact that
have a matching value base on the FROM statement.

One suggestion, don't use blanks in field names, table names etc. in access,
can cause problems and extra work.

Hi,

OK so that didn't really work. I tried three different ways: 1. With the
expressions that you gave me which resulted in having to specify a position
(which is fine) when I opened the query but gave me no data. 2. With the
first join an Inner and the last join a left one as you had suggested and
that resulted in the same thing. Now when I go to the design view in either
query I get an error message that Access can't represent the join expression
in the design view but shows everything when I hit OK. 3. With the first join
as a left and the last as an Inner and that kicked me out of access all
together and prompted me to send an error report to Microsoft (which I did).
My new question does the whole row have to match or should it work as long as
there is a position attached to a name?

Thanks again for letting me pick your brain!

Sara

Sara,

[quoted text clipped - 36 lines]

 

[Sara P.]

Thanks I'll keep trying!

Sara

Sarah,

No it only matches on the fields you specfiy, (e.g. from contact inner join
[position type] on contact.key1 = [position type].key2

if you specify inner join there MUST be a match in both tables for the row to
be shown. So in the above case, if there was a row in table contact with a
key1 value of 1234 but no row in position type that had a key2 value of 1234,
you would not be shown the row in contact.

If you used LEFT JOIN in the above case you would see the contact table row,
inner join shows all rows from the "left" table.

RIGHT JOIN is just the opposite, it will show all of the rows of the "right"
table (position type in the above case) but only the rows from contact that
have a matching value base on the FROM statement.

One suggestion, don't use blanks in field names, table names etc. in access,
can cause problems and extra work.

Hi,

OK so that didn't really work. I tried three different ways: 1. With the
expressions that you gave me which resulted in having to specify a position
(which is fine) when I opened the query but gave me no data. 2. With the
first join an Inner and the last join a left one as you had suggested and
that resulted in the same thing. Now when I go to the design view in either
query I get an error message that Access can't represent the join expression
in the design view but shows everything when I hit OK. 3. With the first join
as a left and the last as an Inner and that kicked me out of access all
together and prompted me to send an error report to Microsoft (which I did).
My new question does the whole row have to match or should it work as long as
there is a position attached to a name?

Thanks again for letting me pick your brain!

Sara

Sara,

[quoted text clipped - 36 lines]

Thanks,
Sara

--
Never let it be said that I was totally comitted to sanity. It is the dark
places of my mind that fascinate me.

NthDegree