To Include All Data

Assuming i hv two tables and same fieldacno as per below :-

TblA                TblB
FieldAcno         FieldAcno
A                     A
B                     D
C                     E

How to create a query that joined fieldacno from the two tables but will 
produce records of A,B,C,D & E.


0
Utf
2/23/2010 9:14:01 AM
access 16762 articles. 3 followers. Follow

19 Replies
762 Views

Similar Articles

[PageSpeed] 37

hi,

On 23.02.2010 10:14, zyus wrote:
> How to create a query that joined fieldacno from the two tables but will
> produce records of A,B,C,D&  E.
Take a look at the UNION query. As SQL:

SELECT fieldacno FROM TblA
UNION
SELECT fieldacno FROM TblB

Also take at the difference when using UNION ALL.


mfG
--> stefan <--
0
Stefan
2/23/2010 9:18:42 AM
Dear Stefan,

If i use the approach, will it create duplicate record for A. 

"Stefan Hoffmann" wrote:

> hi,
> 
> On 23.02.2010 10:14, zyus wrote:
> > How to create a query that joined fieldacno from the two tables but will
> > produce records of A,B,C,D&  E.
> Take a look at the UNION query. As SQL:
> 
> SELECT fieldacno FROM TblA
> UNION
> SELECT fieldacno FROM TblB
> 
> Also take at the difference when using UNION ALL.
> 
> 
> mfG
> --> stefan <--
> .
> 
0
Utf
2/23/2010 10:38:01 AM
hi,

On 23.02.2010 11:38, zyus wrote:
> If i use the approach, will it create duplicate record for A.
Just test it :)


mfG
--> stefan <--
0
Stefan
2/23/2010 1:16:47 PM
On Tue, 23 Feb 2010 02:38:01 -0800, zyus <zyus@discussions.microsoft.com>
wrote:

>If i use the approach, will it create duplicate record for A. 

Stefan is right; it will not create a duplicate. UNION removes duplicates.

If there are other fields in the table, though, it gets more complicated.
Suppose you had:


TblA                TblB
FieldAcno  FieldX FieldY         FieldAcno FieldX FieldY
A               25      True           A               30      False
B                40      True           D               28     True
C                30       False         E               31      True


You want one record for A. What value do you want to see for FieldX and
FieldY?

-- 

             John W. Vinson [MVP]
0
John
2/23/2010 4:17:33 PM
Hi John,

My latest value is in tbl-b...based on my example for A i will take value in 
tbl-b. same goes to D & C. For B & C i will use value in tbl-A.

FYI i use access for query & reporting. Tbl-A is a previous month data and 
tbl-B is the latest month data.

When i use union query, it will create duplicate record for A. Is there any 
way i can select A,B,C,D & E without any duplicates

"John W. Vinson" wrote:

> On Tue, 23 Feb 2010 02:38:01 -0800, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >If i use the approach, will it create duplicate record for A. 
> 
> Stefan is right; it will not create a duplicate. UNION removes duplicates.
> 
> If there are other fields in the table, though, it gets more complicated.
> Suppose you had:
> 
> 
> TblA                TblB
> FieldAcno  FieldX FieldY         FieldAcno FieldX FieldY
> A               25      True           A               30      False
> B                40      True           D               28     True
> C                30       False         E               31      True
> 
> 
> You want one record for A. What value do you want to see for FieldX and
> FieldY?
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
2/24/2010 12:36:01 AM
On Tue, 23 Feb 2010 16:36:01 -0800, zyus <zyus@discussions.microsoft.com>
wrote:

>My latest value is in tbl-b...based on my example for A i will take value in 
>tbl-b. same goes to D & C. For B & C i will use value in tbl-A.

I simply don't understand that logic at all. Sorry, but it makes no sense to
me. Perhaps some real examples with real fieldnames and meanings would make it
make sense.

>FYI i use access for query & reporting. Tbl-A is a previous month data and 
>tbl-B is the latest month data.
>
>When i use union query, it will create duplicate record for A. Is there any 
>way i can select A,B,C,D & E without any duplicates

You could use a UNION ALL query grouping by the field, and selecting the
appropriate criteria; or you could use some combination of LEFT JOIN queries
to select the value from tblA in some cases, and from tblB in others.

Since I don't understand the goal, or the table structure, I don't know what
to suggest.
-- 

             John W. Vinson [MVP]
0
John
2/24/2010 1:00:48 AM
>>Tbl-A is a previous month data and tbl-B is the latest month data.  When i 
use union query, it will create duplicate record for A. 
If the data is for two different months how can it be duplicate?
  
-- 
Build a little, test a little.


"zyus" wrote:

> Hi John,
> 
> My latest value is in tbl-b...based on my example for A i will take value in 
> tbl-b. same goes to D & C. For B & C i will use value in tbl-A.
> 
> FYI i use access for query & reporting. Tbl-A is a previous month data and 
> tbl-B is the latest month data.
> 
> When i use union query, it will create duplicate record for A. Is there any 
> way i can select A,B,C,D & E without any duplicates
> 
> "John W. Vinson" wrote:
> 
> > On Tue, 23 Feb 2010 02:38:01 -0800, zyus <zyus@discussions.microsoft.com>
> > wrote:
> > 
> > >If i use the approach, will it create duplicate record for A. 
> > 
> > Stefan is right; it will not create a duplicate. UNION removes duplicates.
> > 
> > If there are other fields in the table, though, it gets more complicated.
> > Suppose you had:
> > 
> > 
> > TblA                TblB
> > FieldAcno  FieldX FieldY         FieldAcno FieldX FieldY
> > A               25      True           A               30      False
> > B                40      True           D               28     True
> > C                30       False         E               31      True
> > 
> > 
> > You want one record for A. What value do you want to see for FieldX and
> > FieldY?
> > 
> > -- 
> > 
> >              John W. Vinson [MVP]
> > .
> > 
0
Utf
2/24/2010 4:04:01 AM
Hi Karl,

Hope i can explain what i'm doing with my access.

Tbl-A & Tbl-B are on the same structure.

Say Tbl-A consist of dec09 month end position & Tbl-B is Jan10 position

Assuming 
Tbl-A
FieldAcno   FieldBal
A               1000.00   (still remain active from dec09 to jan10)
B                 500.00   (active in dec09 but settled in Jan10)
C                 400.00   (active in dec09 but settled in Jan10)

Tbl-B
FieldAcno   FieldBal
A                 900.00 
D                 300.00   (new accounts created in Jan10)
E                 200.00   (new accounts created in Jan10)

When i tick join properties for fieldacno 1..i will only get A
When i tick 2 or 3 i can only display ABC or ADE.
What i expect to display in my query is all data as follow

FieldAcno   FieldBal
A                 900.00 
D                 300.00   (new accounts created in Jan10)
E                 200.00   (new accounts created in Jan10)
B                 500.00   (active in dec09 but settled in Jan10)
C                 400.00   (active in dec09 but settled in Jan10)

Thanks


 
"KARL DEWEY" wrote:

> >>Tbl-A is a previous month data and tbl-B is the latest month data.  When i 
> use union query, it will create duplicate record for A. 
> If the data is for two different months how can it be duplicate?
>   
> -- 
> Build a little, test a little.
> 
> 
> "zyus" wrote:
> 
> > Hi John,
> > 
> > My latest value is in tbl-b...based on my example for A i will take value in 
> > tbl-b. same goes to D & C. For B & C i will use value in tbl-A.
> > 
> > FYI i use access for query & reporting. Tbl-A is a previous month data and 
> > tbl-B is the latest month data.
> > 
> > When i use union query, it will create duplicate record for A. Is there any 
> > way i can select A,B,C,D & E without any duplicates
> > 
> > "John W. Vinson" wrote:
> > 
> > > On Tue, 23 Feb 2010 02:38:01 -0800, zyus <zyus@discussions.microsoft.com>
> > > wrote:
> > > 
> > > >If i use the approach, will it create duplicate record for A. 
> > > 
> > > Stefan is right; it will not create a duplicate. UNION removes duplicates.
> > > 
> > > If there are other fields in the table, though, it gets more complicated.
> > > Suppose you had:
> > > 
> > > 
> > > TblA                TblB
> > > FieldAcno  FieldX FieldY         FieldAcno FieldX FieldY
> > > A               25      True           A               30      False
> > > B                40      True           D               28     True
> > > C                30       False         E               31      True
> > > 
> > > 
> > > You want one record for A. What value do you want to see for FieldX and
> > > FieldY?
> > > 
> > > -- 
> > > 
> > >              John W. Vinson [MVP]
> > > .
> > > 
0
Utf
2/24/2010 7:20:02 AM
hi,

On 24.02.2010 08:20, zyus wrote:
> Say Tbl-A consist of dec09 month end position&  Tbl-B is Jan10 position
It may make sense to store account balances, but under most 
circumstances it is wrong to do that.

It is wrong to store the different months end balances in different tables.

Normalized it should be:

MontlyEndBalance:
[ID] AutoNumber
[Year] Number (Long), Not Null, Not Empty
[Month] Number (Long) Not Null, Not Empty
[Account] your data type Not Null, Not Empty
[Balance] Currency Not Null, Not Empty

With a combined primary key over [Year], [Month] and [Account]. Then you 
can simply select waht you want.


> Assuming
> Tbl-A
> FieldAcno   FieldBal
> A               1000.00   (still remain active from dec09 to jan10)
> B                 500.00   (active in dec09 but settled in Jan10)
> C                 400.00   (active in dec09 but settled in Jan10)
>
> Tbl-B
> FieldAcno   FieldBal
> A                 900.00
> D                 300.00   (new accounts created in Jan10)
> E                 200.00   (new accounts created in Jan10)
>
> When i tick join properties for fieldacno 1..i will only get A
> When i tick 2 or 3 i can only display ABC or ADE.
> What i expect to display in my query is all data as follow
>
> FieldAcno   FieldBal
> A                 900.00
> D                 300.00   (new accounts created in Jan10)
> E                 200.00   (new accounts created in Jan10)
> B                 500.00   (active in dec09 but settled in Jan10)
> C                 400.00   (active in dec09 but settled in Jan10)
Does this result make really sense?

The interpretation of the fact that in your January 2010 table the 
accounts B and C are missing would be: They do not longer exist.

Otherwise you need the UNION ALL with an additional restriction:

qryNormalized:
SELECT 200912 AS Sort, FieldAcno, FieldBal FROM [TblA]
UNION ALL
SELECT 201001 AS Sort, FieldAcno, FieldBal FROM [TblA]

and

SELECT *
FROM qryNormalized O
WHERE O.Sort =
(
   SELECT Max(Sort)
   FROM qryNormalized I
   WHERE I.FieldAcno = O.FieldAcno
)


mfG
--> stefan <--
0
Stefan
2/24/2010 9:25:25 AM
On Tue, 23 Feb 2010 23:20:02 -0800, zyus <zyus@discussions.microsoft.com>
wrote:

>Hi Karl,
>
>Hope i can explain what i'm doing with my access.
>
>Tbl-A & Tbl-B are on the same structure.
>
>Say Tbl-A consist of dec09 month end position & Tbl-B is Jan10 position
>
>Assuming 
>Tbl-A
>FieldAcno   FieldBal
>A               1000.00   (still remain active from dec09 to jan10)
>B                 500.00   (active in dec09 but settled in Jan10)
>C                 400.00   (active in dec09 but settled in Jan10)
>
>Tbl-B
>FieldAcno   FieldBal
>A                 900.00 
>D                 300.00   (new accounts created in Jan10)
>E                 200.00   (new accounts created in Jan10)
>
>When i tick join properties for fieldacno 1..i will only get A
>When i tick 2 or 3 i can only display ABC or ADE.
>What i expect to display in my query is all data as follow
>
>FieldAcno   FieldBal
>A                 900.00 
>D                 300.00   (new accounts created in Jan10)
>E                 200.00   (new accounts created in Jan10)
>B                 500.00   (active in dec09 but settled in Jan10)
>C                 400.00   (active in dec09 but settled in Jan10)
>
>Thanks

I will absolutely agree with Stefan that your table design is *wrong*, and
this should all be one table. Do you plan to create twelve new tables every
year as long as you're in business!? OUCH! That might make sense in a
spreadsheet, but not in a relational database!

With your current design - if I understand it and the problem correctly -
you'll need to use a complicated outer join and UNION query, since Tbl-B
"outranks" tbl-A:

SELECT tblB.FieldAcno, tblB.FieldBal
FROM tblB INNER JOIN tblA ON tblB.FieldAcno = tblA.FieldAcno
UNION ALL
SELECT tblA.FieldAcno, TblA.FieldBal
FROM tblA LEFT JOIN tblB
ON tblB.FieldAcno = tblA.FieldAcno
WHERE tblB.FieldAcno IS NULL;

The first query in the union will return all records from tblB which *do* have
matches in tblA; the second will pick up those records in tblA which no longer
exist in tblB.

Your job will be MUCH MUCH easier if you have one table with fields for the
creation date, amount, and settlement date.
-- 

             John W. Vinson [MVP]
0
John
2/24/2010 5:27:31 PM
Thanks for your response.

Agreed with stefan & you on the "bad design" of table.

I dont really do a "proper database" program with access. I use it to manage 
500K records of data that i extracted from other programs where my 2003 excel 
cant handled.

My usage of access is more on data query, comparison, massage, reporting etc 
and normally i compare monthly data from the 2 tables..

I tried the Union query as suggested, but there's syntax error which i 
couldnt find the solution.

  

"John W. Vinson" wrote:

> On Tue, 23 Feb 2010 23:20:02 -0800, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >Hi Karl,
> >
> >Hope i can explain what i'm doing with my access.
> >
> >Tbl-A & Tbl-B are on the same structure.
> >
> >Say Tbl-A consist of dec09 month end position & Tbl-B is Jan10 position
> >
> >Assuming 
> >Tbl-A
> >FieldAcno   FieldBal
> >A               1000.00   (still remain active from dec09 to jan10)
> >B                 500.00   (active in dec09 but settled in Jan10)
> >C                 400.00   (active in dec09 but settled in Jan10)
> >
> >Tbl-B
> >FieldAcno   FieldBal
> >A                 900.00 
> >D                 300.00   (new accounts created in Jan10)
> >E                 200.00   (new accounts created in Jan10)
> >
> >When i tick join properties for fieldacno 1..i will only get A
> >When i tick 2 or 3 i can only display ABC or ADE.
> >What i expect to display in my query is all data as follow
> >
> >FieldAcno   FieldBal
> >A                 900.00 
> >D                 300.00   (new accounts created in Jan10)
> >E                 200.00   (new accounts created in Jan10)
> >B                 500.00   (active in dec09 but settled in Jan10)
> >C                 400.00   (active in dec09 but settled in Jan10)
> >
> >Thanks
> 
> I will absolutely agree with Stefan that your table design is *wrong*, and
> this should all be one table. Do you plan to create twelve new tables every
> year as long as you're in business!? OUCH! That might make sense in a
> spreadsheet, but not in a relational database!
> 
> With your current design - if I understand it and the problem correctly -
> you'll need to use a complicated outer join and UNION query, since Tbl-B
> "outranks" tbl-A:
> 
> SELECT tblB.FieldAcno, tblB.FieldBal
> FROM tblB INNER JOIN tblA ON tblB.FieldAcno = tblA.FieldAcno
> UNION ALL
> SELECT tblA.FieldAcno, TblA.FieldBal
> FROM tblA LEFT JOIN tblB
> ON tblB.FieldAcno = tblA.FieldAcno
> WHERE tblB.FieldAcno IS NULL;
> 
> The first query in the union will return all records from tblB which *do* have
> matches in tblA; the second will pick up those records in tblA which no longer
> exist in tblB.
> 
> Your job will be MUCH MUCH easier if you have one table with fields for the
> creation date, amount, and settlement date.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
2/25/2010 1:45:01 AM
On Wed, 24 Feb 2010 17:45:01 -0800, zyus <zyus@discussions.microsoft.com>
wrote:

>I tried the Union query as suggested, but there's syntax error which i 
>couldnt find the solution.

Please post the SQL of the query and the exact error message.
-- 

             John W. Vinson [MVP]
0
John
2/25/2010 3:54:39 AM
Hi John,

below are my sql

SELECT tbl-sks.Acno, tbl-sks.net_bal
FROM tbl-sks INNER JOIN tbl-previousmonth ON tbl-sks.Acno = 
tbl-previousmonth.Acno
UNION ALL
SELECT tbl-previousmonth.acno, tbl-previousmonth.net_Bal
FROM tbl-previousmonth LEFT JOIN tbl-sks
ON tbl-sks.Acno = tbl-previousmonth.Acno
WHERE tbl-sks.Acno IS NULL;


and the error is syntax error in from clause

Thanks 
"John W. Vinson" wrote:

> On Wed, 24 Feb 2010 17:45:01 -0800, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >I tried the Union query as suggested, but there's syntax error which i 
> >couldnt find the solution.
> 
> Please post the SQL of the query and the exact error message.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/8/2010 4:14:01 AM
hi john...thanks for your advise on the hypen...tried the sql but with syntax 
error in join operation

"John W. Vinson" wrote:

> On Sun, 7 Mar 2010 20:14:01 -0800, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >Hi John,
> >
> >below are my sql
> >
> >SELECT tbl-sks.Acno, tbl-sks.net_bal
> >FROM tbl-sks INNER JOIN tbl-previousmonth ON tbl-sks.Acno = 
> >tbl-previousmonth.Acno
> >UNION ALL
> >SELECT tbl-previousmonth.acno, tbl-previousmonth.net_Bal
> >FROM tbl-previousmonth LEFT JOIN tbl-sks
> >ON tbl-sks.Acno = tbl-previousmonth.Acno
> >WHERE tbl-sks.Acno IS NULL;
> >
> >
> >and the error is syntax error in from clause
> 
> Since you (unwisely) have hyphens in your table names, I'd suggest putting all
> the table names in [square brackets]:
> 
> SELECT [tbl-sks].Acno, [tbl-sks].net_bal
> FROM [tbl-sks] INNER JOIN [tbl-previousmonth] ON tbl-sks.Acno = 
> [tbl-previousmonth].Acno
> UNION ALL
> SELECT [tbl-previousmonth].acno, [tbl-previousmonth].net_Bal
> FROM [tbl-previousmonth] LEFT JOIN [tbl-sks]
> ON [tbl-sks].Acno = [tbl-previousmonth].Acno
> WHERE [tbl-sks].Acno IS NULL;
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/8/2010 6:26:01 AM
If you simply used the SQL that John posted, there was one table name that 
wasn't placed in square brackets.  I'll leave it as an exercise for you to 
find it - the error message provides a clue.

Rob


"zyus" <zyus@discussions.microsoft.com> wrote in message 
news:983ECAE5-3E1F-446D-B88B-CD7C762763EC@microsoft.com...
> hi john...thanks for your advise on the hypen...tried the sql but with 
> syntax
> error in join operation
>
> "John W. Vinson" wrote:
>
>> On Sun, 7 Mar 2010 20:14:01 -0800, zyus <zyus@discussions.microsoft.com>
>> wrote:
>>
>> >Hi John,
>> >
>> >below are my sql
>> >
>> >SELECT tbl-sks.Acno, tbl-sks.net_bal
>> >FROM tbl-sks INNER JOIN tbl-previousmonth ON tbl-sks.Acno =
>> >tbl-previousmonth.Acno
>> >UNION ALL
>> >SELECT tbl-previousmonth.acno, tbl-previousmonth.net_Bal
>> >FROM tbl-previousmonth LEFT JOIN tbl-sks
>> >ON tbl-sks.Acno = tbl-previousmonth.Acno
>> >WHERE tbl-sks.Acno IS NULL;
>> >
>> >
>> >and the error is syntax error in from clause
>>
>> Since you (unwisely) have hyphens in your table names, I'd suggest 
>> putting all
>> the table names in [square brackets]:
>>
>> SELECT [tbl-sks].Acno, [tbl-sks].net_bal
>> FROM [tbl-sks] INNER JOIN [tbl-previousmonth] ON tbl-sks.Acno =
>> [tbl-previousmonth].Acno
>> UNION ALL
>> SELECT [tbl-previousmonth].acno, [tbl-previousmonth].net_Bal
>> FROM [tbl-previousmonth] LEFT JOIN [tbl-sks]
>> ON [tbl-sks].Acno = [tbl-previousmonth].Acno
>> WHERE [tbl-sks].Acno IS NULL;
>> -- 
>>
>>              John W. Vinson [MVP]
>> .
>> 

0
Rob
3/8/2010 7:17:26 AM
Hi rob....i'm not so familiar with SQL...normally view query by the design 
view.....anyway i have managed to find the abnormality from the exercise that 
u given me.... 

Thanks for the response

Thank John....

"Rob Parker" wrote:

> If you simply used the SQL that John posted, there was one table name that 
> wasn't placed in square brackets.  I'll leave it as an exercise for you to 
> find it - the error message provides a clue.
> 
> Rob
> 
> 
> "zyus" <zyus@discussions.microsoft.com> wrote in message 
> news:983ECAE5-3E1F-446D-B88B-CD7C762763EC@microsoft.com...
> > hi john...thanks for your advise on the hypen...tried the sql but with 
> > syntax
> > error in join operation
> >
> > "John W. Vinson" wrote:
> >
> >> On Sun, 7 Mar 2010 20:14:01 -0800, zyus <zyus@discussions.microsoft.com>
> >> wrote:
> >>
> >> >Hi John,
> >> >
> >> >below are my sql
> >> >
> >> >SELECT tbl-sks.Acno, tbl-sks.net_bal
> >> >FROM tbl-sks INNER JOIN tbl-previousmonth ON tbl-sks.Acno =
> >> >tbl-previousmonth.Acno
> >> >UNION ALL
> >> >SELECT tbl-previousmonth.acno, tbl-previousmonth.net_Bal
> >> >FROM tbl-previousmonth LEFT JOIN tbl-sks
> >> >ON tbl-sks.Acno = tbl-previousmonth.Acno
> >> >WHERE tbl-sks.Acno IS NULL;
> >> >
> >> >
> >> >and the error is syntax error in from clause
> >>
> >> Since you (unwisely) have hyphens in your table names, I'd suggest 
> >> putting all
> >> the table names in [square brackets]:
> >>
> >> SELECT [tbl-sks].Acno, [tbl-sks].net_bal
> >> FROM [tbl-sks] INNER JOIN [tbl-previousmonth] ON tbl-sks.Acno =
> >> [tbl-previousmonth].Acno
> >> UNION ALL
> >> SELECT [tbl-previousmonth].acno, [tbl-previousmonth].net_Bal
> >> FROM [tbl-previousmonth] LEFT JOIN [tbl-sks]
> >> ON [tbl-sks].Acno = [tbl-previousmonth].Acno
> >> WHERE [tbl-sks].Acno IS NULL;
> >> -- 
> >>
> >>              John W. Vinson [MVP]
> >> .
> >> 
> 
> .
> 
0
Utf
3/8/2010 10:55:01 AM
Hi john,

when i used belwo sql it only show record from tbl-previousmonth and not a 
combined data with tbl-sks.



SELECT [tbl-sks].Acno, [tbl-sks].net_bal
FROM [tbl-sks] INNER JOIN [tbl-previousmonth] ON [tbl-sks].Acno = 
[tbl-previousmonth].Acno
UNION ALL SELECT [tbl-previousmonth].acno, [tbl-previousmonth].net_Bal
FROM [tbl-previousmonth] LEFT JOIN [tbl-sks]
ON [tbl-sks].Acno = [tbl-previousmonth].Acno
WHERE [tbl-sks].Acno IS NULL;

"John W. Vinson" wrote:

> On Sun, 7 Mar 2010 20:14:01 -0800, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >Hi John,
> >
> >below are my sql
> >
> >SELECT tbl-sks.Acno, tbl-sks.net_bal
> >FROM tbl-sks INNER JOIN tbl-previousmonth ON tbl-sks.Acno = 
> >tbl-previousmonth.Acno
> >UNION ALL
> >SELECT tbl-previousmonth.acno, tbl-previousmonth.net_Bal
> >FROM tbl-previousmonth LEFT JOIN tbl-sks
> >ON tbl-sks.Acno = tbl-previousmonth.Acno
> >WHERE tbl-sks.Acno IS NULL;
> >
> >
> >and the error is syntax error in from clause
> 
> Since you (unwisely) have hyphens in your table names, I'd suggest putting all
> the table names in [square brackets]:
> 
> SELECT [tbl-sks].Acno, [tbl-sks].net_bal
> FROM [tbl-sks] INNER JOIN [tbl-previousmonth] ON tbl-sks.Acno = 
> [tbl-previousmonth].Acno
> UNION ALL
> SELECT [tbl-previousmonth].acno, [tbl-previousmonth].net_Bal
> FROM [tbl-previousmonth] LEFT JOIN [tbl-sks]
> ON [tbl-sks].Acno = [tbl-previousmonth].Acno
> WHERE [tbl-sks].Acno IS NULL;
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/9/2010 2:20:01 AM
"zyus" <zyus@discussions.microsoft.com> wrote in message 
news:8162CCE5-54FF-4178-8F8F-0957EA4F0A59@microsoft.com...
> Hi John,
>
> My latest value is in tbl-b...based on my example for A i will take value 
> in
> tbl-b. same goes to D & C. For B & C i will use value in tbl-A.
>
> FYI i use access for query & reporting. Tbl-A is a previous month data and
> tbl-B is the latest month data.
>
> When i use union query, it will create duplicate record for A. Is there 
> any
> way i can select A,B,C,D & E without any duplicates
>
> "John W. Vinson" wrote:
>
>> On Tue, 23 Feb 2010 02:38:01 -0800, zyus <zyus@discussions.microsoft.com>
>> wrote:
>>
>> >If i use the approach, will it create duplicate record for A.
>>
>> Stefan is right; it will not create a duplicate. UNION removes 
>> duplicates.
>>
>> If there are other fields in the table, though, it gets more complicated.
>> Suppose you had:
>>
>>
>> TblA                TblB
>> FieldAcno  FieldX FieldY         FieldAcno FieldX FieldY
>> A               25      True           A               30      False
>> B                40      True           D               28     True
>> C                30       False         E               31      True
>>
>>
>> You want one record for A. What value do you want to see for FieldX and
>> FieldY?
>>
>> -- 
>>
>>              John W. Vinson [MVP]
>> .
>> 

0
De
3/13/2010 6:01:57 PM
ôl!nnnn

"zyus" <zyus@discussions.microsoft.com> a écrit dans le message de groupe de 
discussion : 4FFD25CD-C388-45B2-8AC8-BC9BC158E82F@microsoft.com...
> Hi John,
>
> below are my sql
>
> SELECT tbl-sks.Acno, tbl-sks.net_bal
> FROM tbl-sks INNER JOIN tbl-previousmonth ON tbl-sks.Acno =
> tbl-previousmonth.Acno
> UNION ALL
> SELECT tbl-previousmonth.acno, tbl-previousmonth.net_Bal
> FROM tbl-previousmonth LEFT JOIN tbl-sks
> ON tbl-sks.Acno = tbl-previousmonth.Acno
> WHERE tbl-sks.Acno IS NULL;
>
>
> and the error is syntax error in from clause
>
> Thanks
> "John W. Vinson" wrote:
>
>> On Wed, 24 Feb 2010 17:45:01 -0800, zyus <zyus@discussions.microsoft.com>
>> wrote:
>>
>> >I tried the Union query as suggested, but there's syntax error which i
>> >couldnt find the solution.
>>
>> Please post the SQL of the query and the exact error message.
>> -- 
>>
>>              John W. Vinson [MVP]
>> .
>> 
0
joelgeraldine
3/17/2010 1:09:37 PM
Reply:

Similar Artilces:

How to import DATA from SQL2K to Access?
I am using get external data, import, ODBC type and it's error out saying I cannot use ODBC to import/export data. I would appreciate if someone please show me the better way of importing data from sql. Mehbs, Two ways. 1 - From SQL Server, use DTS to copy the data from the SQL Server table to the Access table. 2 - From Access, link your SQL Server table through ODBC and then use an append query to copy the data from the linked SQL Server table to your Access table. Good luck. -- Sco M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005 Denver Area Ac...

How do I change data in one cell so other copies& vice versa
i have two cells that i want linked.....i know how to link one cell to another but my question is this how do i link cells so that when data is changed in either cell BOTH of the cells change.....example enter data in a1 to make b1 change....how do i make it so i can enter data in b1 to make a1 change....so they both can change each other.. thanx Hi not possible without using VBA 8an event procedure) -- Regards Frank Kabel Frankfurt, Germany sedain wrote: > i have two cells that i want linked.....i know how to link one cell to > another but my question is this how do i link...

Data file not closed properly?
Frequently, although not every day, when I boot up and open Outlook 2003 (SP1), I get a brief message which reads "The data file "MAIN" was not closed properly". It then runs a quick check, a progress bar zips by, and then my "Main" Outlook .pst file opens. The thing is, I close Outlook the same way every night before shutting down, and cannot figure out why it thinks there's a problem. There hasn't been a problem with my data. BTW, this behavior began - albeit intermittently - after installing Windows XP Pro SP2. Could that have something to do...

averaging data in multiple columns
I'm looking for a formula to lookup criteria in column A and average the results in columns B, C, D, E, etc. For example I would like to know the average of how many "CATS" have been sold in a given year and I will be adding addtional months as time progresses and do not want to have to adjust my range or formula each time a new months worth of data is added. For example. column A column B column C column D column E DOGS 100 200 150 50 CATS 200 300 100 0 FISH 50 ...

Data control in CRM for business units
Dear all, We currently have a large scale CRM deployment in the offing, but we need to be prepared for how data, fields, etc can be segregated between Business Units - if indeed this is possible - although I did read somewhere previously that this was a possible way of deployment - clearly, there are default data fields, views, data, etc were site-wide, but there were also business-unit wide ones too that could be created and controlled??? We have a few Business Units that will share the single CRM deployment/installation. The ethos was that those in Business Unit 'A' had full contro...

Changing mulitple fields visibility from a single data entry
I want to use one control to determine which others within my form are visible i.e. In a text box labelled 'How Many' if 1 is entered then the text box labelled 'one' is visible and box's labelled two, three and four are invisible, if 2 is entered then 'one' and two' are visible and so on. Any help would be appreciated. Thanks You've described a "how" ... how you are trying to do something. If you'll describe the "why" and "what" (what will having this allow you to accomplish?), the newsgroup readers may be able to...

Data Selection
Fellow Excellians, I need a little help Picture my screen I have 16 teams and about 12 to 15 people per team Cell C1 TO C100 Contains Various Team Names Cell D1 to D200 Contains peoples Names In Cell A1 Contains "Team Name" in Cell A2 i want to select a person from the list i have created in C1 & C2 but it needs to filter out only the people that play for that team selected in A1 Anyone Got any idea's Thanking so much in advance Steve ...

unable to enter data on form. help?
I have created a form using a relationship between two tables. This was to be a data entry form but when I try to enter data nothing happens. The cursor moves to the first field but typing doesn't result in data appearing on the screen. I could do with some help It sounds like you have based the form on a non-updatable query. For a list of why this might be so, see: Why is my query read-only? at: http://allenbrowne.com/ser-61.html There are other possible explanations, e.g.: - you set the form's AllowEdits property to No - you set the form's Recordset Type property to...

Change Pie Chart segment color based on value in source data
I am creating a training matrix which shows current status of training progress. I want the pie chart to always display three equal segments. I want each segment color to change based on the data input in the source data. Hopefully this is an adequate definition of what I am trying to do? -- Doug Hi, Assuming you have 3 possible colours for each segment you need to create a pie chart based on 9 cells. The content of the cells is determined by formula and will either display a 1 or 0. Training data in range B2:B4 with values 1, 2 or 3 to indicate level. Formula in cells F1:F9 F1: =IF(...

OLEDB
I=92ve set up an OLEDB link between two excel workbooks. When I bring the data across the first 7 columns convert the numerical data to text whilst the following columns are correctly formatted as numbers. I=92ve gone back to the source spreadsheet and everything is correctly formatted as numbers. Why does OLEDB do this ? I cannot find out how to get the data coming across in the correct format ? ...

data refresh cell reference
Hi, I have a workbook that has several different spreadsheets, each with different query that gets refreshed through ODBC when the workbook i used. Each query requires that a date range be entered. It will always be the case that the same date range will be entered fo each of the 6 queries. Question: is it possible to have the query reference specific cells i the workbook, so that my users don't have to enter the same "To" an "From" dates six different times? In Access I'm able to referenc values on forms, but the same logic doesn't seem to apply with ...

Sample data wizard not starting up 02-26-07
I have successfully installed CRM 3.0 on a W2003 SP1 box with SQL2000 SP4. When I try to run the sample data wizard nothing happens but this line is added to the logfile: Sample Data Wizard started at 2/25/2007 3:02:41 PM Error occurred while trying to retrieve the users from the Active Directory Domain - 2/25/2007 3:02:48 PM The type initializer for "Microsoft.Crm.Tools.SampleDataWizard.ChooseUserForm" threw an exception. - 2/25/2007 3:02:48 PM Sample Data Wizard completed at - 2/25/2007 3:02:48 PM Does anybody have any idea what might cause this, the wizard has wor...

moving data from one db to another
I have a pretty general question about what might be the best way to handle this. I need to write a watcher program to export flagged rows in an MSSQL 2005 db out to a MySQL db. After each row is copied, I need to reset the flag in the source. I'm wondering if anyone could tell me what the best process for this would be. Looping through all the rows in the source one at a time and then posting them one at a time seems like the wrong way to go. There's a lot of vb.net that I don't know so I'm thinking there might be a better, simpler way to do this. Initially there...

Can I use data from different worksheets to create a chart
I am running Excel 2000. I have 3 worksheets from different schools. I want to compare ratings from each of the schools in one chart. Can I do that and, if so, how. Thanks Yes you can build a single chart that plots data from 3 different worksheets. You haven't said what type of chart, so I will walk you through an XY (Scatter) chart. Let me know if you need help with another chart type. Step 1 - Create new worksheet where you will place embedded chart. Name your new worksheet Charts Step 2 - In your new Charts sheet, from the top menu select Insert > Chart to start Excel&#...

updating data on tab pages
I have a form with a tab control with multiple pages. When a value is selected on a control on one of the pages, several related values are filled in on other pages. I've tried this with a macro that sets a value based on a dlookup, and I've also done it with an update query based on the same dlookup amount. Either way works until I click onto another page on the tab control. When I click to one of the other pages it says "This record has been changed by another user etc." and gives the choices to drop the changes, save the record,etc. I tried adding a saverecord command...

Data validation for Multiple columns
Hi all, I am creating a spreadsheet which will have data entry from different users. I want to give the validation in such a way that a combination of values in 2 columns do not occur more than once. Like say A B 1 a 1 b 2 a 2 b 2 a in above example the combination 2 and a is recurring. it should be restricted by validation while entering the data itself. can anybody please help Regards NC Hi, sorry for bothering you all but i got a solution on other group here is link for others reference http://groups-beta.google.com/group/microsoft.public.excel.programming...

Excel Drop Down box or check box data to Excel Database
I desperately need help with this! I have an Excel form which I have included check boxes, drop down boxes and Yes no. How do I incorporate the data into the Excel database? Maybe you could use some of the techniques that Debra Dalgleish shows at: http://contextures.com/xlUserForm01.html TotallyConfused wrote: > > I desperately need help with this! I have an Excel form which I have > included check boxes, drop down boxes and Yes no. How do I incorporate the > data into the Excel database? -- Dave Peterson Hi, You can send me the workbook and I may be able to assist. ...

Can I include the timezone in the savedate field in MS-Word?
Can I include the timezone in the savedate field in MS-Word? For example, i'd like the result of the savedate field to be: mm/dd/yyyy 01:00 PM EST Either simply type EST after the field or use a switch \@ "MM/dd/yyyy hh:mm AM/PM 'EST'" -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>...

saving data in excel from word doc.
I have address labels in word doc. and I want to save it in excel. But it should be each address in differant column. How should I do that? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Evette" <Evette@discussions.microsoft.com&g...

Auto updating pivot tables using external XML data
Hi all, I'm new to Excel and have been playing with importing XML data and pivot tables recently. My goal is this: I have bunch of XML files in a directory. I'd like to generate pivot tables and "visualize" the data in them. Once my excel sheet looks good, I'd like to publish it as an HTML file so everyone else can view it using Internet Explorer. In fact, I already did all this. Here is the tough part though: These XML files get updated from time to time. And when I open the Internet Explorer, I want to see those latest data in XMLs. In my case, Excel publishes the ...

How import data???????
Hi my name is Edson, and I need impor data to new entity create for my, named participante. The infomation is in SQL SERVER. I try with DMP but the CDF haven=B4t my entity.. How can I make it??? If I need some software when can I download??? THAKS ...

Rebuild of Data Base
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Email Client: Exchange Hi, <br> I had an error/corruption of my entourage data base. I used the wizard to validate the data base, which after was completed was determined to be OK. I then performed a rebuild of the data base, which also completed with out any issues. After the two actions were completed I have found that I now have two Main Identity folders labeled as follows: <br><br>Main Identity <br> Main Identity (Backed up 2009-12-03 17.37) <br><br>Two Questions: <br> 1 Do i need t...

linking data #2
I know how to copy data from one Excel file and do a Paste Special, Paste as Link onto another file. That works fine but the updated data flows in one way only. Is there a way of linking two Excel files so that you have the same data on each one, but they are linked so that updating data in one file will automatically be shown in the second file, and this will happen no matter which of the files is updated? I don't see anything in Excel that would allow this. Stephen, You can do the tow way flow, but it is a BAD idea. Better is to use one workbook, with one worksheet as your...

How does money get its data?
I bank with Navy Federal Credit Union and I am *trying* to adapt to MS Money plus (demo right now) from scattered use of Quicken. I'd like to learn how money gets my bank information so I can make an educated call/complaint to the credit unions technical group about the data I am getting. I am currently testing two mny files, one automated and one manual. Today with NFCU, when I do their recommended manual method by signing on to the website, selecting a date period then importing a QIF file, all the transactions show up as "POS Debit Visa - Acct#last4" and no other in...

Creating checkboxes that change data
Hello everyone, Can anyone help / make suggestions in achieving the following: I am trying to create an invoice that summarises the total of the products at the bottom of the spreadsheet. What I want to achieve is some way of having various products in a row which a user can select via some sort of check box (giving the product a boolean value). Each product would have a price value attached to it and when it is selected the price should be added to the total. This would therefore allow the user to select various products and the total would be calculated accordingly. This seems relativel...