Prev: Total Number of fields in a query
Next: How to convert Null values to zero when create an average query
From: NPell on 15 Apr 2010 07:26
I have a table which to all intent and purposes is:
Recieved and Sent.

I have a table which has:
Rec. Date >> Sent Date
(with other information, but i can manage that)

I want a query to show :

Date >> Recieved >> Sent.

But on some days there might not be any entries for Sent or Received.
This would be fine if i can guraentee that one will be done each day.

However some days we might get alot in, so we would spend all day
logging the Recieved info.
Then other days we might have a backlog of stuff, so would not log an
Rec'd but only sort out Sending info.

How can i make sure ALL dates are picked up via a query?

Really appreciate any help..
Kind Regards,
From: NPell on 15 Apr 2010 09:11
On 15 Apr, 12:26, NPell <noalenp...(a)gmail.com> wrote:
> I have a table which to all intent and purposes is:
> Recieved and Sent.
>
> I have a table which has:
> Rec. Date >> Sent Date
> (with other information, but i can manage that)
>
> I want a query to show :
>
> Date >> Recieved >> Sent.
>
> But on some days there might not be any entries for Sent or Received.
> This would be fine if i can guraentee that one will be done each day.
>
> However some days we might get alot in, so we would spend all day
> logging the Recieved info.
> Then other days we might have a backlog of stuff, so would not log an
> Rec'd but only sort out Sending info.
>
> How can i make sure ALL dates are picked up via a query?
>
> Really appreciate any help..
> Kind Regards,

Date Received Sent
01/01/2010 30 83
02/01/2010 88 0
03/01/2010 91 0
04/01/2010 0 29
05/01/2010 0 71
06/01/2010 95 64
07/01/2010 18 37

If anyone was struggling to picture what i mean.
The above is what im striving for.
But if i only use the date from Recieved if it appears, then i will
miss 4th and 5th, as it is null (showing as zero).
Same applies for Sent, i would miss 2nd and 3rd, as it is null
(showing as zero).

Hope this helps, if it was needed.
From: Jerry Whittle on 15 Apr 2010 09:16
Access does not support full outer joins in a query.

What you could do is a Left Join (#2 if you double click on the join line in
the query). That would get the Receives even if there isn't a Sent. Then
create a similar query except using the Right Join option (#3) to get the
records the other way.

Next join these two queries in a Union query.

Select * From Query1
Union
Select * From Query2;

Using Union (instead of Union All) will get rid of any duplicates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"NPell" wrote:

> I have a table which to all intent and purposes is:
> Recieved and Sent.
>
> I have a table which has:
> Rec. Date >> Sent Date
> (with other information, but i can manage that)
>
> I want a query to show :
>
> Date >> Recieved >> Sent.
>
> But on some days there might not be any entries for Sent or Received.
> This would be fine if i can guraentee that one will be done each day.
>
> However some days we might get alot in, so we would spend all day
> logging the Recieved info.
> Then other days we might have a backlog of stuff, so would not log an
> Rec'd but only sort out Sending info.
>
> How can i make sure ALL dates are picked up via a query?
>
> Really appreciate any help..
> Kind Regards,
> .
>
From: NPell on 15 Apr 2010 10:08
On 15 Apr, 14:16, Jerry Whittle
<JerryWhit...(a)discussions.microsoft.com> wrote:
> Access does not support full outer joins in a query.
>
> What you could do is a Left Join (#2 if you double click on the join line in
> the query). That would get the Receives even if there isn't a Sent. Then
> create a similar query except using the Right Join option (#3) to get the
> records the other way.
>
> Next join these two queries in a Union query.
>
> Select * From Query1
> Union
> Select * From Query2;
>
> Using Union (instead of Union All) will get rid of any duplicates.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
>
> "NPell" wrote:
> > I have a table which to all intent and purposes is:
> > Recieved and Sent.
>
> > I have a table which has:
> > Rec. Date >> Sent Date
> > (with other information, but i can manage that)
>
> > I want a query to show :
>
> > Date >> Recieved >> Sent.
>
> > But on some days there might not be any entries for Sent or Received.
> > This would be fine if i can guraentee that one will be done each day.
>
> > However some days we might get alot in, so we would spend all day
> > logging the Recieved info.
> > Then other days we might have a backlog of stuff, so would not log an
> > Rec'd but only sort out Sending info.
>
> > How can i make sure ALL dates are picked up via a query?
>
> > Really appreciate any help..
> > Kind Regards,
> > .- Hide quoted text -
>
> - Show quoted text -

Perfect. Thankyou so much.
 | 
Pages: 1
Prev: Total Number of fields in a query
Next: How to convert Null values to zero when create an average query