To Include All Data

Z

zyus

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.
 
S

Stefan Hoffmann

hi,

How to create a query that joined fieldacno from the two tables but will
produce records of A,B,C,D& E.
Click to expand...
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 <--
 
J

John W. Vinson

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

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?
 
Z

zyus

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
 
J

John W. Vinson

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.
Click to expand...

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
Click to expand...

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.
 
K

KARL DEWEY

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?
 
Z

zyus

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
 
S

Stefan Hoffmann

hi,

Say Tbl-A consist of dec09 month end position& Tbl-B is Jan10 position
Click to expand...
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)
Click to expand...
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 <--
 
J

John W. Vinson

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
Click to expand...

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.
 
Z

zyus

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.
 
J

John W. Vinson

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

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

zyus

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
 
J

John W. Vinson

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
Click to expand...

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;
 
Z

zyus

hi john...thanks for your advise on the hypen...tried the sql but with syntax
error in join operation

John W. Vinson said:
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
Click to expand...

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;
Click to expand...
 
R

Rob Parker

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 said:
hi john...thanks for your advise on the hypen...tried the sql but with
syntax
error in join operation

John W. Vinson said:
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
Click to expand...

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;
Click to expand...
Click to expand...
 
Z

zyus

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 said:
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 said:
hi john...thanks for your advise on the hypen...tried the sql but with
syntax
error in join operation

John W. Vinson said:
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;
Click to expand...
Click to expand...

.
Click to expand...
 
Z

zyus

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]
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
Click to expand...

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;
Click to expand...
 
J

John W. Vinson

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;
Click to expand...

That's what I would expect if there are no matching Acno values in the two
tables. The first query finds records which have matching Acno in the two
tables, the second finds those which exist only in PreviousMonth and not in
tbl-sks.

It's not clear to me what records you want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exclusion-Type Join 3
Adding data to MS Access from csv file 0
modified pivot table using a macro 2
VERY URGENT! Large query taking 20mins 21
For ... next 7
Most efficent way to produce new table from 2 tables 2
Project Online Timesheets Actuals Mismatches - data not publishing to projects 0
id display's funny on form built on qry built on 2 tables ?? (usnint the form Wizard) 4

Top