Make table Query not "updating" 1 field

G

Guest

I created a make-table querry using the standard querry create screen. This
querry takes fields from a few tables and when I run it all fields bar one
appear in the new table. I have no idea why the one field does not show.
The SQL for the querry is shown below.

The field that does not get "updated" is the last "SELECT" field
"TCURRENT.LastUpdate". Does anybody have any ideas?

SELECT

QSelEsc.[Sm Inv Id],
QSelEsc.[Location Id],
QSelEsc.[Contract Amount],
QSelEsc.[Billing Stop],

TESCTYPE.EscTypeID,
TESCTYPE.EscTyprDescription,

TCURRENT.CurrentLabRate,
TLABRATE.Rate,
TCURRENT.CurrentMatInd,
TCURRENT.[Labour%],
[Contract Amount]*[Labour%] AS CurrLabPart,
[Contract Amount]-[CurrLabPart] AS CurrMatPart,
TCURRENT.CurrCPI,
TCURRENT.NotToExceed,
TCURRENT.IndEndDate,
TCURRENT.CPIEndDate,
TCURRENT.NewMatInd,
TCURRENT.NewCPIIndex,
TCURRENT.UpdateStatus,
TCURRENT.ApprovalStatus,
TCURRENT.LastUpdate

INTO TCURESC

FROM TLABRATE, TESCTYPE INNER JOIN (QSelEsc INNER JOIN TCURRENT ON
QSelEsc.[Sm Inv Id] = TCURRENT.SMInvID) ON TESCTYPE.EscTypeID =
TCURRENT.EscTypeID

WHERE (((TLABRATE.EndDate)>((SELECT[EndDate] FROM [TLABRATE] WHERE [Billing
Stop] >[EndDate]))) AND ((TLABRATE.[Start Date])<((SELECT[Start Date] FROM
[TLABRATE] WHERE [Billing Stop] <[Start Date]))));

Thank you
 
G

Guest

1. Is there a LastUpdate field in TCURRENT? Double check.

2. Take out the INTO TCURESC part of the SQL. Does TCURRENT show up then?

3. By "not show" do you mean that column is empty or there is no LastUpdate
column created? If empty, your SQL just isn't pulling in anything as you
should have seen in (2) above. If not created, try something like this:
TCURRENT.LastUpdate AS LU

4. Does the SQL have the blank lines like your example shows or is this just
a cut and paste problem? If the blank lines are actually there, remove them.
 
G

Guest

There is a "LastUpdate" field in TCURRENT.

When I change the make-table querry into a select querry the LastUpdate
field for TCURRENT is returned.

No "LastUpdate" column is created.

Showing TCURRENT.LastUpdate AS LU does not change the result.

There ar no lines - it is simply a cut & past from the SQL view of the querry.


Any other ideas?

Thnak you


Jerry Whittle said:
1. Is there a LastUpdate field in TCURRENT? Double check.

2. Take out the INTO TCURESC part of the SQL. Does TCURRENT show up then?

3. By "not show" do you mean that column is empty or there is no LastUpdate
column created? If empty, your SQL just isn't pulling in anything as you
should have seen in (2) above. If not created, try something like this:
TCURRENT.LastUpdate AS LU

4. Does the SQL have the blank lines like your example shows or is this just
a cut and paste problem? If the blank lines are actually there, remove them.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ralph Wischnewski said:
I created a make-table querry using the standard querry create screen. This
querry takes fields from a few tables and when I run it all fields bar one
appear in the new table. I have no idea why the one field does not show.
The SQL for the querry is shown below.

The field that does not get "updated" is the last "SELECT" field
"TCURRENT.LastUpdate". Does anybody have any ideas?

SELECT

QSelEsc.[Sm Inv Id],
QSelEsc.[Location Id],
QSelEsc.[Contract Amount],
QSelEsc.[Billing Stop],

TESCTYPE.EscTypeID,
TESCTYPE.EscTyprDescription,

TCURRENT.CurrentLabRate,
TLABRATE.Rate,
TCURRENT.CurrentMatInd,
TCURRENT.[Labour%],
[Contract Amount]*[Labour%] AS CurrLabPart,
[Contract Amount]-[CurrLabPart] AS CurrMatPart,
TCURRENT.CurrCPI,
TCURRENT.NotToExceed,
TCURRENT.IndEndDate,
TCURRENT.CPIEndDate,
TCURRENT.NewMatInd,
TCURRENT.NewCPIIndex,
TCURRENT.UpdateStatus,
TCURRENT.ApprovalStatus,
TCURRENT.LastUpdate

INTO TCURESC

FROM TLABRATE, TESCTYPE INNER JOIN (QSelEsc INNER JOIN TCURRENT ON
QSelEsc.[Sm Inv Id] = TCURRENT.SMInvID) ON TESCTYPE.EscTypeID =
TCURRENT.EscTypeID

WHERE (((TLABRATE.EndDate)>((SELECT[EndDate] FROM [TLABRATE] WHERE [Billing
Stop] >[EndDate]))) AND ((TLABRATE.[Start Date])<((SELECT[Start Date] FROM
[TLABRATE] WHERE [Billing Stop] <[Start Date]))));

Thank you
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Ralph,

I've never been comfortable with
mixing cartesian join and inner/outer joins
in same query, i.e.,

FROM TLABRATE, TESCTYPE INNER JOIN

Can you use some prelim or post query
to get your cartesian query separately.

I don't know if that is it, but I don't
see anything else obvious.

good luck,

gary
 
G

Guest

Ouch! There went all my best ideas. Here's one screwball idea that worked on
a similar, but not the same, problem back in A97: Move up TCURRENT.LastUpdate
in the select statement somewhere instead of the end. Remember to get all the
commas in the right place.

For some strange reason, I once had a table that would not work until I
moved a field to a different position. I wasn't using any reserved words or
special characters in any of the field names. It just messed up. It also
didn't act like corruption as importing the table into another database
didn't fix the problem. Speaking of corruption, if it is that:

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ralph Wischnewski said:
There is a "LastUpdate" field in TCURRENT.

When I change the make-table querry into a select querry the LastUpdate
field for TCURRENT is returned.

No "LastUpdate" column is created.

Showing TCURRENT.LastUpdate AS LU does not change the result.

There ar no lines - it is simply a cut & past from the SQL view of the querry.


Any other ideas?

Thnak you


Jerry Whittle said:
1. Is there a LastUpdate field in TCURRENT? Double check.

2. Take out the INTO TCURESC part of the SQL. Does TCURRENT show up then?

3. By "not show" do you mean that column is empty or there is no LastUpdate
column created? If empty, your SQL just isn't pulling in anything as you
should have seen in (2) above. If not created, try something like this:
TCURRENT.LastUpdate AS LU

4. Does the SQL have the blank lines like your example shows or is this just
a cut and paste problem? If the blank lines are actually there, remove them.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ralph Wischnewski said:
I created a make-table querry using the standard querry create screen. This
querry takes fields from a few tables and when I run it all fields bar one
appear in the new table. I have no idea why the one field does not show.
The SQL for the querry is shown below.

The field that does not get "updated" is the last "SELECT" field
"TCURRENT.LastUpdate". Does anybody have any ideas?

SELECT

QSelEsc.[Sm Inv Id],
QSelEsc.[Location Id],
QSelEsc.[Contract Amount],
QSelEsc.[Billing Stop],

TESCTYPE.EscTypeID,
TESCTYPE.EscTyprDescription,

TCURRENT.CurrentLabRate,
TLABRATE.Rate,
TCURRENT.CurrentMatInd,
TCURRENT.[Labour%],
[Contract Amount]*[Labour%] AS CurrLabPart,
[Contract Amount]-[CurrLabPart] AS CurrMatPart,
TCURRENT.CurrCPI,
TCURRENT.NotToExceed,
TCURRENT.IndEndDate,
TCURRENT.CPIEndDate,
TCURRENT.NewMatInd,
TCURRENT.NewCPIIndex,
TCURRENT.UpdateStatus,
TCURRENT.ApprovalStatus,
TCURRENT.LastUpdate

INTO TCURESC

FROM TLABRATE, TESCTYPE INNER JOIN (QSelEsc INNER JOIN TCURRENT ON
QSelEsc.[Sm Inv Id] = TCURRENT.SMInvID) ON TESCTYPE.EscTypeID =
TCURRENT.EscTypeID

WHERE (((TLABRATE.EndDate)>((SELECT[EndDate] FROM [TLABRATE] WHERE [Billing
Stop] >[EndDate]))) AND ((TLABRATE.[Start Date])<((SELECT[Start Date] FROM
[TLABRATE] WHERE [Billing Stop] <[Start Date]))));

Thank you
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I tried to move the field to no avail. I will review the articles you
referenced.

Jerry Whittle said:
Ouch! There went all my best ideas. Here's one screwball idea that worked on
a similar, but not the same, problem back in A97: Move up TCURRENT.LastUpdate
in the select statement somewhere instead of the end. Remember to get all the
commas in the right place.

For some strange reason, I once had a table that would not work until I
moved a field to a different position. I wasn't using any reserved words or
special characters in any of the field names. It just messed up. It also
didn't act like corruption as importing the table into another database
didn't fix the problem. Speaking of corruption, if it is that:

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ralph Wischnewski said:
There is a "LastUpdate" field in TCURRENT.

When I change the make-table querry into a select querry the LastUpdate
field for TCURRENT is returned.

No "LastUpdate" column is created.

Showing TCURRENT.LastUpdate AS LU does not change the result.

There ar no lines - it is simply a cut & past from the SQL view of the querry.


Any other ideas?

Thnak you


Jerry Whittle said:
1. Is there a LastUpdate field in TCURRENT? Double check.

2. Take out the INTO TCURESC part of the SQL. Does TCURRENT show up then?

3. By "not show" do you mean that column is empty or there is no LastUpdate
column created? If empty, your SQL just isn't pulling in anything as you
should have seen in (2) above. If not created, try something like this:
TCURRENT.LastUpdate AS LU

4. Does the SQL have the blank lines like your example shows or is this just
a cut and paste problem? If the blank lines are actually there, remove them.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I created a make-table querry using the standard querry create screen. This
querry takes fields from a few tables and when I run it all fields bar one
appear in the new table. I have no idea why the one field does not show.
The SQL for the querry is shown below.

The field that does not get "updated" is the last "SELECT" field
"TCURRENT.LastUpdate". Does anybody have any ideas?

SELECT

QSelEsc.[Sm Inv Id],
QSelEsc.[Location Id],
QSelEsc.[Contract Amount],
QSelEsc.[Billing Stop],

TESCTYPE.EscTypeID,
TESCTYPE.EscTyprDescription,

TCURRENT.CurrentLabRate,
TLABRATE.Rate,
TCURRENT.CurrentMatInd,
TCURRENT.[Labour%],
[Contract Amount]*[Labour%] AS CurrLabPart,
[Contract Amount]-[CurrLabPart] AS CurrMatPart,
TCURRENT.CurrCPI,
TCURRENT.NotToExceed,
TCURRENT.IndEndDate,
TCURRENT.CPIEndDate,
TCURRENT.NewMatInd,
TCURRENT.NewCPIIndex,
TCURRENT.UpdateStatus,
TCURRENT.ApprovalStatus,
TCURRENT.LastUpdate

INTO TCURESC

FROM TLABRATE, TESCTYPE INNER JOIN (QSelEsc INNER JOIN TCURRENT ON
QSelEsc.[Sm Inv Id] = TCURRENT.SMInvID) ON TESCTYPE.EscTypeID =
TCURRENT.EscTypeID

WHERE (((TLABRATE.EndDate)>((SELECT[EndDate] FROM [TLABRATE] WHERE [Billing
Stop] >[EndDate]))) AND ((TLABRATE.[Start Date])<((SELECT[Start Date] FROM
[TLABRATE] WHERE [Billing Stop] <[Start Date]))));

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Top