Simple Append Query not Working. I do not have a clue why.

  • Thread starter Thread starter Fredrik
  • Start date Start date
F

Fredrik

Hi,


I have one table which is made from a tablequery.
From this table I want to add everything to an existing table.

The From table:
Loadingdate Unloadingdate Pallets Trucknumber
DATE DATE Number Text
| | | |
| | | |
The To table: | | |
Loadingdate Unloadingdate Pallets Trucknumber Yadayada Date
Date Number Text Text

I have 19 posts in the "temp" From table and when I run the query it finds
"0" records to add.
Off course there is something that i must do wrong, but I have no clue about
what.
Any ideas?

Thanks!
 
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
 
Do you have any criteria in the append query?
Does your To table have a primary key field?
Does your To table have an index set to unique?

I see what appears to be a problem in that your Unloadingdate is a date in
the From and number in the To table. Also the Pallets are number in the From
and text in the To.

Post your SQL of the append query. Open in design view, click on menu VIEW
- SQL View, highlight all, copy, and paste in a post.
 
Here it comes:

INSERT INTO [Bilpriser N&K] ( Lastningsdatum, Lossningsdatum, [EUR-Pall],
[BIG-Pall], Bilnummer )
SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer;

"Jerry Whittle" skrev:
 
Hello again,

No the formats are correct (date-date, text-text), only this messagesystem
screwing my text =).

I have no criteria what so ever. The To table has Trucknumber and
Loadingdate as prim. key. No no index is set to unique.

I'm going nuts over this. I mean this is one of the simplest things to do,
still it does not work.. :/.

"KARL DEWEY" skrev:
 
What happens when you run it as a simple select query like this:

SELECT tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer;

I'm thinking that no records are returned as you are doing an inner join on
a table that doesn't have those records.

Or the records are returned, but not added due to a unique constraint error.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Fredrik said:
Here it comes:

INSERT INTO [Bilpriser N&K] ( Lastningsdatum, Lossningsdatum, [EUR-Pall],
[BIG-Pall], Bilnummer )
SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer;

"Jerry Whittle" skrev:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
Click to expand...
Click to expand...
 
When I do this i get nothing.
Even if I delete the joins I dont get anything.

Can it have something to do with that the table "tempbilnr" is a temporary
table made from a query? (It has values in it now)

"Jerry Whittle" skrev:
What happens when you run it as a simple select query like this:

SELECT tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer;

I'm thinking that no records are returned as you are doing an inner join on
a table that doesn't have those records.

Or the records are returned, but not added due to a unique constraint error.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Fredrik said:
Here it comes:

INSERT INTO [Bilpriser N&K] ( Lastningsdatum, Lossningsdatum, [EUR-Pall],
[BIG-Pall], Bilnummer )
SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer;

"Jerry Whittle" skrev:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,


I have one table which is made from a tablequery.
From this table I want to add everything to an existing table.

The From table:
Loadingdate Unloadingdate Pallets Trucknumber
DATE DATE Number Text
| | | |
| | | |
The To table: | | |
Loadingdate Unloadingdate Pallets Trucknumber Yadayada Date
Date Number Text Text

I have 19 posts in the "temp" From table and when I run the query it finds
"0" records to add.
Off course there is something that i must do wrong, but I have no clue about
what.
Any ideas?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
And if you run this --

SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr;

--
KARL DEWEY
Build a little - Test a little


Fredrik said:
When I do this i get nothing.
Even if I delete the joins I dont get anything.

Can it have something to do with that the table "tempbilnr" is a temporary
table made from a query? (It has values in it now)

"Jerry Whittle" skrev:
What happens when you run it as a simple select query like this:

SELECT tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer;

I'm thinking that no records are returned as you are doing an inner join on
a table that doesn't have those records.

Or the records are returned, but not added due to a unique constraint error.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Fredrik said:
Here it comes:

INSERT INTO [Bilpriser N&K] ( Lastningsdatum, Lossningsdatum, [EUR-Pall],
[BIG-Pall], Bilnummer )
SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer;

"Jerry Whittle" skrev:

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,


I have one table which is made from a tablequery.
From this table I want to add everything to an existing table.

The From table:
Loadingdate Unloadingdate Pallets Trucknumber
DATE DATE Number Text
| | | |
| | | |
The To table: | | |
Loadingdate Unloadingdate Pallets Trucknumber Yadayada Date
Date Number Text Text

I have 19 posts in the "temp" From table and when I run the query it finds
"0" records to add.
Off course there is something that i must do wrong, but I have no clue about
what.
Any ideas?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Then i get the result I want.

"KARL DEWEY" skrev:
And if you run this --

SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr;

--
KARL DEWEY
Build a little - Test a little


Fredrik said:
When I do this i get nothing.
Even if I delete the joins I dont get anything.

Can it have something to do with that the table "tempbilnr" is a temporary
table made from a query? (It has values in it now)

"Jerry Whittle" skrev:
What happens when you run it as a simple select query like this:

SELECT tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer;

I'm thinking that no records are returned as you are doing an inner join on
a table that doesn't have those records.

Or the records are returned, but not added due to a unique constraint error.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Here it comes:

INSERT INTO [Bilpriser N&K] ( Lastningsdatum, Lossningsdatum, [EUR-Pall],
[BIG-Pall], Bilnummer )
SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer;

"Jerry Whittle" skrev:

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,


I have one table which is made from a tablequery.
From this table I want to add everything to an existing table.

The From table:
Loadingdate Unloadingdate Pallets Trucknumber
DATE DATE Number Text
| | | |
| | | |
The To table: | | |
Loadingdate Unloadingdate Pallets Trucknumber Yadayada Date
Date Number Text Text

I have 19 posts in the "temp" From table and when I run the query it finds
"0" records to add.
Off course there is something that i must do wrong, but I have no clue about
what.
Any ideas?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Then try converting that to the Append Query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Fredrik said:
Then i get the result I want.

"KARL DEWEY" skrev:
And if you run this --

SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr;

--
KARL DEWEY
Build a little - Test a little


Fredrik said:
When I do this i get nothing.
Even if I delete the joins I dont get anything.

Can it have something to do with that the table "tempbilnr" is a temporary
table made from a query? (It has values in it now)

"Jerry Whittle" skrev:

What happens when you run it as a simple select query like this:

SELECT tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum,
tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR,
tempbilnr.SummaförBIG,
tempbilnr.Bilnummer;

I'm thinking that no records are returned as you are doing an inner join on
a table that doesn't have those records.

Or the records are returned, but not added due to a unique constraint error.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Here it comes:

INSERT INTO [Bilpriser N&K] ( Lastningsdatum, Lossningsdatum, [EUR-Pall],
[BIG-Pall], Bilnummer )
SELECT tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer
FROM tempbilnr INNER JOIN [Bilpriser N&K] ON ([Bilpriser N&K].Bilnummer =
tempbilnr.Bilnummer) AND (tempbilnr.Lastningsdatum = [Bilpriser
N&K].Lastningsdatum)
GROUP BY tempbilnr.Lastningsdatum, tempbilnr.Lossningsdatum,
tempbilnr.SummaförEUR, tempbilnr.SummaförBIG, tempbilnr.Bilnummer;

"Jerry Whittle" skrev:

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,


I have one table which is made from a tablequery.
From this table I want to add everything to an existing table.

The From table:
Loadingdate Unloadingdate Pallets Trucknumber
DATE DATE Number Text
| | | |
| | | |
The To table: | | |
Loadingdate Unloadingdate Pallets Trucknumber Yadayada Date
Date Number Text Text

I have 19 posts in the "temp" From table and when I run the query it finds
"0" records to add.
Off course there is something that i must do wrong, but I have no clue about
what.
Any ideas?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top