join type not supported

G

Guest

I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker]) Is
Null));

I am geting an error message that basically says "join type not supported"
when I try to save the query. How can I fix this? I think it has something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee
 
A

Allen Browne

In the FROM clause, you have a table name starting with a number. I suspect
that Access will not be able to process that unless you place square
brackets around it, i.e.:
FROM ([1_Stg_WBStatus_Flag] INNER JOIN ...

If that doesn't solve the problem, break it down until you do get it
working, and then you can build it up again to identify where the issue
lies.

For example, just start with just:
SELECT [1_Stg_WBStatus_Flag].histflag, Tbl_Archive.[marker]
FROM ([1_Stg_WBStatus_Flag] INNER JOIN Tbl_MasterPop
ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[marker]) Is Null));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

geebee said:
I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next
Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past
Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active
BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date],
Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts
Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK
Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS
marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT
JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker]) Is
Null));

I am geting an error message that basically says "join type not supported"
when I try to save the query. How can I fix this? I think it has
something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee
Click to expand...
 
G

Guest

When I take out the following line, it saves and works well:

LEFT JOIN Tbl_Archive ON marker=Tbl_Archive.[marker]

however, when I add the line it, it will not save or work.



Allen Browne said:
In the FROM clause, you have a table name starting with a number. I suspect
that Access will not be able to process that unless you place square
brackets around it, i.e.:
FROM ([1_Stg_WBStatus_Flag] INNER JOIN ...

If that doesn't solve the problem, break it down until you do get it
working, and then you can build it up again to identify where the issue
lies.

For example, just start with just:
SELECT [1_Stg_WBStatus_Flag].histflag, Tbl_Archive.[marker]
FROM ([1_Stg_WBStatus_Flag] INNER JOIN Tbl_MasterPop
ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[marker]) Is Null));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

geebee said:
I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next
Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past
Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active
BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date],
Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts
Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK
Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS
marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT
JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker]) Is
Null));

I am geting an error message that basically says "join type not supported"
when I try to save the query. How can I fix this? I think it has
something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee
Click to expand...
Click to expand...
 
G

Gary Walter

PMFBI

A query doesn't get processed "instantly"...

In Access, first the JOIN(s) is processed,
then the WHERE clause, then the SELECT
clause.

You created an alias in the SELECT clause
and tried to use it in the JOIN clause. In the
"JOIN step," it doesn't even know what your alias
means. The same goes for a WHERE clause.

.....
CDate([Datex]) AS Expr1,
.....
[tbl_masterpop].[loan acct #] & [expr1]
& [tbl_masterpop].[status] AS Newmarker
FROM
([1_Stg_WBStatus_Flag]
INNER JOIN
Tbl_MasterPop
ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN
Tbl_Archive
ON
[tbl_masterpop].[loan acct #] & CDate([Datex])
& [tbl_masterpop].[status] = Tbl_Archive.[marker]
WHERE
((([1_Stg_WBStatus_Flag].histflag)=1)
AND
((Tbl_Archive.[marker]) Is Null));



geebee said:
When I take out the following line, it saves and works well:

LEFT JOIN Tbl_Archive ON marker=Tbl_Archive.[marker]

however, when I add the line it, it will not save or work.



Allen Browne said:
In the FROM clause, you have a table name starting with a number. I
suspect
that Access will not be able to process that unless you place square
brackets around it, i.e.:
FROM ([1_Stg_WBStatus_Flag] INNER JOIN ...

If that doesn't solve the problem, break it down until you do get it
working, and then you can build it up again to identify where the issue
lies.

For example, just start with just:
SELECT [1_Stg_WBStatus_Flag].histflag, Tbl_Archive.[marker]
FROM ([1_Stg_WBStatus_Flag] INNER JOIN Tbl_MasterPop
ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[marker]) Is Null));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

geebee said:
I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt,
PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next
Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg
Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot
Past
Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig],
[Active
BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off
Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered
Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus,
Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date],
Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts
Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar
Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended
This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth
of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK
Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion
Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive
Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS
marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT
JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker])
Is
Null));

I am geting an error message that basically says "join type not
supported"
when I try to save the query. How can I fix this? I think it has
something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Did you type that in, or create it graphically?

Try creating it graphically, by dragging from one table to the other in the
upper pane of query design view, and then double-clicking the join line to
specify it as an outer join.

You may find that Access brackets the joins differently in the FROM clause,
and the query works.

We are presuming here that the marker field is the same data type in both
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

geebee said:
When I take out the following line, it saves and works well:

LEFT JOIN Tbl_Archive ON marker=Tbl_Archive.[marker]

however, when I add the line it, it will not save or work.



Allen Browne said:
In the FROM clause, you have a table name starting with a number. I
suspect
that Access will not be able to process that unless you place square
brackets around it, i.e.:
FROM ([1_Stg_WBStatus_Flag] INNER JOIN ...

If that doesn't solve the problem, break it down until you do get it
working, and then you can build it up again to identify where the issue
lies.

For example, just start with just:
SELECT [1_Stg_WBStatus_Flag].histflag, Tbl_Archive.[marker]
FROM ([1_Stg_WBStatus_Flag] INNER JOIN Tbl_MasterPop
ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[marker]) Is Null));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

geebee said:
I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt,
PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next
Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg
Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot
Past
Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig],
[Active
BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off
Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered
Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus,
Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date],
Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts
Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar
Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended
This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth
of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK
Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion
Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive
Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS
marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT
JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker])
Is
Null));

I am geting an error message that basically says "join type not
supported"
when I try to save the query. How can I fix this? I think it has
something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Normally in a join you need to specify the tablename along with the field
name. What is Marker in this context? Is it a field? If so, what table
does it belong to?

If it is a parameter then you will probably have to move the test to the
where clause and test for it being present or being null.

geebee said:
When I take out the following line, it saves and works well:

LEFT JOIN Tbl_Archive ON marker=Tbl_Archive.[marker]

however, when I add the line it, it will not save or work.



Allen Browne said:
In the FROM clause, you have a table name starting with a number. I
suspect
that Access will not be able to process that unless you place square
brackets around it, i.e.:
FROM ([1_Stg_WBStatus_Flag] INNER JOIN ...

If that doesn't solve the problem, break it down until you do get it
working, and then you can build it up again to identify where the issue
lies.

For example, just start with just:
SELECT [1_Stg_WBStatus_Flag].histflag, Tbl_Archive.[marker]
FROM ([1_Stg_WBStatus_Flag] INNER JOIN Tbl_MasterPop
ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[marker]) Is Null));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

geebee said:
I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt,
PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next
Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg
Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot
Past
Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig],
[Active
BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off
Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered
Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus,
Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date],
Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts
Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar
Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended
This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth
of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK
Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion
Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive
Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS
marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT
JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker])
Is
Null));

I am geting an error message that basically says "join type not
supported"
when I try to save the query. How can I fix this? I think it has
something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 

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

query speed 5
delete query 1
duplicate records 3
unwanted duplicate loan account numbers 1
DUPLICATE QUERY results 1
Query Error 2
combine 2 access queries 3
runtime error 3001 invalid argument error message 2

Top