Another query query.

[John Sh]

I have an append query that Access seems to think is not good enough. The one field, that is the main criteria, "Taxon.Family" does not get appended.
Access keeps adding this field to the append list, causing an error with two fields being appended to one.
How do I stop Access from causing this error?

This is the query as written.....

INSERT INTO nomatch ( Family, Genus, Accession, Collector )
SELECT [Ourimbah Collection].Family, [Ourimbah Collection].Genus, [Ourimbah Collection].AccessionNumber, [Ourimbah Collection].Collector
FROM [Ourimbah Collection] LEFT JOIN Taxon ON [Ourimbah Collection].Genus = Taxon.genus
WHERE (((Taxon.family) Is Null))
ORDER BY [Ourimbah Collection].Family, [Ourimbah Collection].Genus, [Ourimbah Collection].AccessionNumber;

and this is the query after Access has had a go at it....
Access has included the "Taxon.Family" as part of the append.

INSERT INTO nomatch ( Family, Genus, Accession, Collector, Family )
SELECT [Ourimbah Collection].Family, [Ourimbah Collection].Genus, [Ourimbah Collection].AccessionNumber, [Ourimbah Collection].Collector, Taxon.family
FROM [Ourimbah Collection] LEFT JOIN Taxon ON [Ourimbah Collection].Genus = Taxon.genus
WHERE (((Taxon.family) Is Null))
ORDER BY [Ourimbah Collection].Family, [Ourimbah Collection].Genus, [Ourimbah Collection].AccessionNumber;

It seems the query remains untouched until it is run, then Access does it's dirty work.

 

[John Sh]

I have tried adding "null" to the append field but Access thinks [null] is a field name and causes a different error.

 

[jdraw]

??? Have you checked your SQL to ensure you didn't have Family in the code twice??
Have you tried retyping the entire query?

INSERT INTO nomatch ( Family, Genus, Accession, Collector, Family )

 

[John Sh]

??? Have you checked your SQL to ensure you didn't have Family in the code twice??
Have you tried retyping the entire query?

INSERT INTO nomatch ( Family, Genus, Accession, Collector, Family )

It has, among others, SELECT [Ourimbah Collection].Family WHERE (((Taxon.family) Is Null))
The where clause is a necessary part of the query but should not be included in the append list.

 

[jdraw]

Does the select query give the result you intend for the append?

SELECT [Ourimbah Collection].Family, [Ourimbah Collection].Genus, [Ourimbah Collection].AccessionNumber, [Ourimbah Collection].Collector
FROM [Ourimbah Collection] LEFT JOIN Taxon ON [Ourimbah Collection].Genus = Taxon.genus
WHERE (((Taxon.family) Is Null))
ORDER BY [Ourimbah Collection].Family, [Ourimbah Collection].Genus, [Ourimbah Collection].AccessionNumber;

I don't see anything in the query:
SELECT [Ourimbah Collection].Family
,[Ourimbah Collection].Genus
,[Ourimbah Collection].AccessionNumber
,[Ourimbah Collection].Collector
FROM [Ourimbah Collection]
LEFT JOIN Taxon ON [Ourimbah Collection].Genus = Taxon.genus
WHERE (((Taxon.family) IS NULL))
ORDER BY [Ourimbah Collection].Family
,[Ourimbah Collection].Genus
,[Ourimbah Collection].AccessionNumber;

 

[John Sh]

The first query in the OP is what I need. The second query in the OP is the result of Access amending the first query.
I have a work around by appending the "Where" field to an unused field. This fixes the immediate problem but How do I stop Access from modifying a working query into something that doesn't work.?

 

[John Sh]

If I were using the query without the append clause it would do as required. Problem is, it's an Append query, not a Select query

 

[jdraw]

Yes, I understand you want to use the append query. My question regarding the Select portion was to ensure that there was data and it was the proper data to be used for the append.

I'm not following this statement
I have a work around by appending the "Where" field to an unused field.

Is that not criteria to be used in the Select?
I have not seen Access add fields to a query independently. You have explicit fields identified, so what would cause Access to add fields to the query?

If you have a test database showing this behaviour, I'll look at it. But, as I said, I haven't seen this behaviour.

 

[John Sh]

I've just gutted my form and tables to a suitable size for uploading and the problem is not occurring even though the queries and tables are copies of the originals..
I will keep playing with this and see if I can pinpoint the problem.
I have debugged the code many times but that doesn't always show up a crazy logic error.
Watch this space!!!
John

 

[ebs17]

The complained about change was not caused by Access, but by you yourself.

In the QBE, Taxon.Family must be noted as invisible so that it only acts as a condition in the WHERE clause.
If it is set to visible, it will also appear in the SELECT part and will only show NULL content there because of the condition set.
Small cause - larger effects. This probably wouldn't have happened when editing in the SQL view.

 

[John Sh]

The complained about change was not caused by Access, but by you yourself.

In the QBE, Taxon.Family must be noted as invisible so that it only acts as a condition in the WHERE clause.
If it is set to visible, it will also appear in the SELECT part and will only show NULL content there because of the condition set.
Small cause - larger effects. This probably wouldn't have happened when editing in the SQL view.

I accept the cause might be mine but I have no idea what you're talking about when you say set it to invisible.
Please explain.

 

[ebs17]

Open your eyes. In the QBE there is a CheckBox for each column to set the visibility of the field.
You can then clearly see the effect of activating or deactivating from the SQL statement in the SQL view.

I always say: Despite all the joy of the QBE's help, in the end the SQL statement from the SQL view is executed as a query. This must be correct, both in terms of syntax and the desired functionality. How you get there is irrelevant. If I work with an assistant like the QBE, I should also have the best possible control over him => the boss tells the assistant where to go!
If the assistant dominates the boss, not much can be expected from that boss (the database developer).

Actually, you should first ask yourself how and with what do YOU create your queries?

 

[mike60smart]

I accept the cause might be mine but I have no idea what you're talking about when you say set it to invisible.
Please explain.

Are you able to upload a copy of the database?

 

[jdraw]

John Sh,
I think ebs17 has the answer. If you were using the query Grid and you had a checkmark in the column showing Taxon.family, then Access would include Taxon.family in the Select portion of the SQL. If you have a column with Taxon.family in the query grid and you don't have a checkmark in that column, then Taxon.family will only show in the where clause.
If that is all true, then ebs17 has given the explanation.

Here's a graphic showing the concept:



Here is the "behind the scenes SQL".
SELECT MintyRaw.FromPostalcode, MintyRaw.ToPostalCode
FROM MintyRaw
WHERE (((MintyRaw.NumCars)>"4"));

If you have a checkmark in the show row in the NumCars column, then this is the resulting SQL

SELECT MintyRaw.FromPostalcode, MintyRaw.ToPostalCode, MintyRaw.NumCars
FROM MintyRaw
WHERE (((MintyRaw.NumCars)>"4"));