Update Query with multiple criteria

  • Thread starter Thread starter TinMan
  • Start date Start date
T

TinMan

I have 2 sub tables that are both foreign key related to a main table. I need
to move to fields from one table (t1) and move it into the other (t2). I need
to import the data where the foreign key (Registrataion Number) along with
the weekending date field are a match. I have created an Append/Update Qry
and have posted the SQL as follows. Can anyone tell me what is wrong with it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding, WeekEnding )
SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO, [Weekly
Mileage].WeekEnding, [Vehicle Payments].WeekEnding
FROM [Weekly Mileage], [Vehicle Payments]
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending]) AND
(([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
 
Are you saying that you are trying to take data from one table and put it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to "move"
data.

If you'll describe in a bit more detail what moving the data will allow you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have been asked to produce a report where the data is in 2 seperate sub
tables linked to my main table Key (Reg No). When i try to run the query with
the 3 tables it says i have got an ambiguous join. So i took the main table
out leaving the 2 subs with no join showing and relationship to each other i
get 82,000 records if i add a relationship at query level i get 5,000 i am
only working with 288 records. So i decided to move data in t1 to t2 and
delete table 1. It only contains four fields 1 autonumber (Primary), Reg No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2 along with
other data collected/entered weekly. Does this help any

Jeff Boyce said:
Are you saying that you are trying to take data from one table and put it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to "move"
data.

If you'll describe in a bit more detail what moving the data will allow you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have 2 sub tables that are both foreign key related to a main table. I
need
to move to fields from one table (t1) and move it into the other (t2). I
need
to import the data where the foreign key (Registrataion Number) along with
the weekending date field are a match. I have created an Append/Update Qry
and have posted the SQL as follows. Can anyone tell me what is wrong with
it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,
WeekEnding )
SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO, [Weekly
Mileage].WeekEnding, [Vehicle Payments].WeekEnding
FROM [Weekly Mileage], [Vehicle Payments]
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending]) AND
(([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
 
An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An example
could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have been asked to produce a report where the data is in 2 seperate sub
tables linked to my main table Key (Reg No). When i try to run the query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each other
i
get 82,000 records if i add a relationship at query level i get 5,000 i am
only working with 288 records. So i decided to move data in t1 to t2 and
delete table 1. It only contains four fields 1 autonumber (Primary), Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2 along
with
other data collected/entered weekly. Does this help any

Jeff Boyce said:
Are you saying that you are trying to take data from one table and put it
in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to "move"
data.

If you'll describe in a bit more detail what moving the data will allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have 2 sub tables that are both foreign key related to a main table. I
need
to move to fields from one table (t1) and move it into the other (t2).
I
need
to import the data where the foreign key (Registrataion Number) along
with
the weekending date field are a match. I have created an Append/Update
Qry
and have posted the SQL as follows. Can anyone tell me what is wrong
with
it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,
WeekEnding )
SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO, [Weekly
Mileage].WeekEnding, [Vehicle Payments].WeekEnding
FROM [Weekly Mileage], [Vehicle Payments]
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])
AND
(([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
Click to expand...
 
Also, if you post the SQL statement you are using, we may be able to spot
something...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff Boyce said:
An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An
example could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have been asked to produce a report where the data is in 2 seperate sub
tables linked to my main table Key (Reg No). When i try to run the query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each
other i
get 82,000 records if i add a relationship at query level i get 5,000 i
am
only working with 288 records. So i decided to move data in t1 to t2 and
delete table 1. It only contains four fields 1 autonumber (Primary), Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2 along
with
other data collected/entered weekly. Does this help any

Jeff Boyce said:
Are you saying that you are trying to take data from one table and put
it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to
"move"
data.

If you'll describe in a bit more detail what moving the data will allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main table.
I
need
to move to fields from one table (t1) and move it into the other (t2).
I
need
to import the data where the foreign key (Registrataion Number) along
with
the weekending date field are a match. I have created an Append/Update
Qry
and have posted the SQL as follows. Can anyone tell me what is wrong
with
it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,
WeekEnding )
SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO, [Weekly
Mileage].WeekEnding, [Vehicle Payments].WeekEnding
FROM [Weekly Mileage], [Vehicle Payments]
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])
AND
(([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
Click to expand...
 
I have set up a DB for our Transport Managment Dept. Containing a main table
holding our trucks details (Vehicle Details). I have created a Servicing,
Maintenance, Inventory, Equipment, Defects, Weekly Mileage, Fuel Used and
Equipment tables. I have created a Single Main form with sub forms for all
the tables. (From Previous Question Post)

Jeff Boyce said:
An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An example
could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have been asked to produce a report where the data is in 2 seperate sub
tables linked to my main table Key (Reg No). When i try to run the query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each other
i
get 82,000 records if i add a relationship at query level i get 5,000 i am
only working with 288 records. So i decided to move data in t1 to t2 and
delete table 1. It only contains four fields 1 autonumber (Primary), Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2 along
with
other data collected/entered weekly. Does this help any

Jeff Boyce said:
Are you saying that you are trying to take data from one table and put it
in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to "move"
data.

If you'll describe in a bit more detail what moving the data will allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main table. I
need
to move to fields from one table (t1) and move it into the other (t2).
I
need
to import the data where the foreign key (Registrataion Number) along
with
the weekending date field are a match. I have created an Append/Update
Qry
and have posted the SQL as follows. Can anyone tell me what is wrong
with
it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,
WeekEnding )
SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO, [Weekly
Mileage].WeekEnding, [Vehicle Payments].WeekEnding
FROM [Weekly Mileage], [Vehicle Payments]
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])
AND
(([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
Click to expand...
 
Again, please post the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have set up a DB for our Transport Managment Dept. Containing a main
table
holding our trucks details (Vehicle Details). I have created a Servicing,
Maintenance, Inventory, Equipment, Defects, Weekly Mileage, Fuel Used and
Equipment tables. I have created a Single Main form with sub forms for all
the tables. (From Previous Question Post)

Jeff Boyce said:
An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An
example
could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have been asked to produce a report where the data is in 2 seperate
sub
tables linked to my main table Key (Reg No). When i try to run the
query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each
other
i
get 82,000 records if i add a relationship at query level i get 5,000 i
am
only working with 288 records. So i decided to move data in t1 to t2
and
delete table 1. It only contains four fields 1 autonumber (Primary),
Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2
along
with
other data collected/entered weekly. Does this help any

:

Are you saying that you are trying to take data from one table and put
it
in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to
"move"
data.

If you'll describe in a bit more detail what moving the data will
allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main
table. I
need
to move to fields from one table (t1) and move it into the other
(t2).
I
need
to import the data where the foreign key (Registrataion Number)
along
with
the weekending date field are a match. I have created an
Append/Update
Qry
and have posted the SQL as follows. Can anyone tell me what is wrong
with
it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,
WeekEnding )
SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,
[Weekly
Mileage].WeekEnding, [Vehicle Payments].WeekEnding
FROM [Weekly Mileage], [Vehicle Payments]
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly
Payments].[Weekending])
AND
(([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
Click to expand...
 
I have reposted the SQL at bottom of this note. I have all the sub tables set
up on 1-many with integrity set. I have 1 main form Vehicle Details and lots
of sub forms for the tables. I would have thought it would have been straight
forward. Thanx Jeff and we share the same surname

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));

Jeff Boyce said:
Also, if you post the SQL statement you are using, we may be able to spot
something...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff Boyce said:
An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An
example could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have been asked to produce a report where the data is in 2 seperate sub
tables linked to my main table Key (Reg No). When i try to run the query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each
other i
get 82,000 records if i add a relationship at query level i get 5,000 i
am
only working with 288 records. So i decided to move data in t1 to t2 and
delete table 1. It only contains four fields 1 autonumber (Primary), Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2 along
with
other data collected/entered weekly. Does this help any

:

Are you saying that you are trying to take data from one table and put
it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to
"move"
data.

If you'll describe in a bit more detail what moving the data will allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Click to expand...
Click to expand...
Click to expand...
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main table. I
need to move to fields from one table (t1) and move it into the other (t2). I
need to import the data where the foreign key (Registrataion Number) along
with the weekending date field are a match. I have created an Append/Update
Qry and have posted the SQL as follows. Can anyone tell me what is wrong
with it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
 
Too darn few of us out there...

I don't see a JOIN clause in your SQL (e.g., LEFT JOIN, RIGHT JOIN, ...).

If this were mine, I'd start out in design view for a new query, pull the
tables in, join them as appropriate, THEN look at the SQL that Access
writes.

Regards

Jeff Boyce
Microsoft Office/Access MVP


TinMan said:
I have reposted the SQL at bottom of this note. I have all the sub tables
set
up on 1-many with integrity set. I have 1 main form Vehicle Details and
lots
of sub forms for the tables. I would have thought it would have been
straight
forward. Thanx Jeff and we share the same surname

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));

Jeff Boyce said:
Also, if you post the SQL statement you are using, we may be able to spot
something...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff Boyce said:
An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An
example could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have been asked to produce a report where the data is in 2 seperate
sub
tables linked to my main table Key (Reg No). When i try to run the
query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each
other i
get 82,000 records if i add a relationship at query level i get 5,000
i
am
only working with 288 records. So i decided to move data in t1 to t2
and
delete table 1. It only contains four fields 1 autonumber (Primary),
Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2
along
with
other data collected/entered weekly. Does this help any

:

Are you saying that you are trying to take data from one table and
put
it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to
"move"
data.

If you'll describe in a bit more detail what moving the data will
allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Click to expand...
Click to expand...
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main table. I
need to move to fields from one table (t1) and move it into the other
(t2). I
need to import the data where the foreign key (Registrataion Number) along
with the weekending date field are a match. I have created an
Append/Update
Qry and have posted the SQL as follows. Can anyone tell me what is wrong
with it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
 
I have split db the tables with relationships set in 1 db while the main db
has links to these tables. When i add the 2 sub tables i get 80k records when
the table only holds 288 records. When i add a join i get 5k records but when
i add the main table which has the parent key i get the ambiguous warning.
What can i do. I have tried going back to design and re-adding the tables to
the query.

Jeff Boyce said:
Too darn few of us out there...

I don't see a JOIN clause in your SQL (e.g., LEFT JOIN, RIGHT JOIN, ...).

If this were mine, I'd start out in design view for a new query, pull the
tables in, join them as appropriate, THEN look at the SQL that Access
writes.

Regards

Jeff Boyce
Microsoft Office/Access MVP


TinMan said:
I have reposted the SQL at bottom of this note. I have all the sub tables
set
up on 1-many with integrity set. I have 1 main form Vehicle Details and
lots
of sub forms for the tables. I would have thought it would have been
straight
forward. Thanx Jeff and we share the same surname

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));

Jeff Boyce said:
Also, if you post the SQL statement you are using, we may be able to spot
something...

Regards

Jeff Boyce
Microsoft Office/Access MVP

An 'ambiguous join' means Access doesn't understand how to relate the
tables.

"How" depends on "what" -- what are you storing in your tables? An
example could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have been asked to produce a report where the data is in 2 seperate
sub
tables linked to my main table Key (Reg No). When i try to run the
query
with
the 3 tables it says i have got an ambiguous join. So i took the main
table
out leaving the 2 subs with no join showing and relationship to each
other i
get 82,000 records if i add a relationship at query level i get 5,000
i
am
only working with 288 records. So i decided to move data in t1 to t2
and
delete table 1. It only contains four fields 1 autonumber (Primary),
Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2
along
with
other data collected/entered weekly. Does this help any

:

Are you saying that you are trying to take data from one table and
put
it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to
"move"
data.

If you'll describe in a bit more detail what moving the data will
allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Click to expand...
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main table. I
need to move to fields from one table (t1) and move it into the other
(t2). I
need to import the data where the foreign key (Registrataion Number) along
with the weekending date field are a match. I have created an
Append/Update
Qry and have posted the SQL as follows. Can anyone tell me what is wrong
with it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
 
Please re-read my previous response...

Create a new query. Add in the main table. Add in one of the related
tables. JOIN them (if you don't, you get a 'cartesian product' - check it
in HELP). When you get that part working, add in the other related table.
JOIN it (if you don't, ...).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinMan said:
I have split db the tables with relationships set in 1 db while the main db
has links to these tables. When i add the 2 sub tables i get 80k records
when
the table only holds 288 records. When i add a join i get 5k records but
when
i add the main table which has the parent key i get the ambiguous warning.
What can i do. I have tried going back to design and re-adding the tables
to
the query.

Jeff Boyce said:
Too darn few of us out there...

I don't see a JOIN clause in your SQL (e.g., LEFT JOIN, RIGHT JOIN, ...).

If this were mine, I'd start out in design view for a new query, pull the
tables in, join them as appropriate, THEN look at the SQL that Access
writes.

Regards

Jeff Boyce
Microsoft Office/Access MVP


TinMan said:
I have reposted the SQL at bottom of this note. I have all the sub
tables
set
up on 1-many with integrity set. I have 1 main form Vehicle Details and
lots
of sub forms for the tables. I would have thought it would have been
straight
forward. Thanx Jeff and we share the same surname

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));

:

Also, if you post the SQL statement you are using, we may be able to
spot
something...

Regards

Jeff Boyce
Microsoft Office/Access MVP

An 'ambiguous join' means Access doesn't understand how to relate
the
tables.

"How" depends on "what" -- what are you storing in your tables? An
example could help us diagnose...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have been asked to produce a report where the data is in 2
seperate
sub
tables linked to my main table Key (Reg No). When i try to run the
query
with
the 3 tables it says i have got an ambiguous join. So i took the
main
table
out leaving the 2 subs with no join showing and relationship to
each
other i
get 82,000 records if i add a relationship at query level i get
5,000
i
am
only working with 288 records. So i decided to move data in t1 to
t2
and
delete table 1. It only contains four fields 1 autonumber
(Primary),
Reg
No
(Foreign Key), [StartMiles] and [FinishMiles] which could be in t2
along
with
other data collected/entered weekly. Does this help any

:

Are you saying that you are trying to take data from one table and
put
it in
another? If so, that sounds quite ... spreadsheetly!

In a well-normalized relational database it is rarely necessary to
"move"
data.

If you'll describe in a bit more detail what moving the data will
allow
you
to do, folks here may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 sub tables that are both foreign key related to a main table.
I
need to move to fields from one table (t1) and move it into the other
(t2). I
need to import the data where the foreign key (Registrataion Number)
along
with the weekending date field are a match. I have created an
Append/Update
Qry and have posted the SQL as follows. Can anyone tell me what is
wrong
with it:

INSERT INTO [Vehicle Payments] ( StartODO, FinishODO, WeekEnding,_
WeekEnding )

SELECT [Weekly Mileage].StartODO, [Weekly Mileage].FinishODO,_
[WeeklyMileage].WeekEnding, [Vehicle Payments].WeekEnding_
FROM [Weekly Mileage], [Vehicle Payments]_
WHERE ((([Weekly Mileage].WeekEnding)=[Weekly Payments].[Weekending])_
AND (([Vehicle Payments].Reg)=[Weekly Payments].[RegNo]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top