Append Query

[RedHeadedMonster via AccessMonster.com]

I have an append query that is being used to populate a table. I have two
fields that are being pulled from data on the Form that initiates the append
they are set up as follows:

Title: [Forms]![BuyersGuideADD]![Title] appending to TITLE
CBGYear: [Forms]![BuyersGuideADD]![CBGYear] appending to CBGYear

When I VIEW the query, all the information is populated, including Title &
CBGYear. But when I RUN the query the table is populated with everything BUT
Title & CBGYear.

To make matters worse....It worked 2 weeks ago and now it doesnt.

Thanx in advance!
RHM

 

[John W. Vinson]

I have an append query that is being used to populate a table. I have two
fields that are being pulled from data on the Form that initiates the append
they are set up as follows:

Title: [Forms]![BuyersGuideADD]![Title] appending to TITLE
CBGYear: [Forms]![BuyersGuideADD]![CBGYear] appending to CBGYear

When I VIEW the query, all the information is populated, including Title &
CBGYear. But when I RUN the query the table is populated with everything BUT
Title & CBGYear.

To make matters worse....It worked 2 weeks ago and now it doesnt.

Thanx in advance!
RHM

Please open the query in SQL view and post the SQL text here. Possibly your
form references are being used as *criteria* to select records, rather than as
fields to be added.

 

[RedHeadedMonster via AccessMonster.com]

Here is the SQL

I have an append query that is being used to populate a table. I have two
fields that are being pulled from data on the Form that initiates the append

[quoted text clipped - 11 lines]

Thanx in advance!
RHM

Please open the query in SQL view and post the SQL text here. Possibly your
form references are being used as *criteria* to select records, rather than as
fields to be added.

 

[RedHeadedMonster via AccessMonster.com]

Ok...Here is the SQL

INSERTINTO CBGBuyersGuidebyParts ( MEI, PartNumber, QTYBreak1, QTY1, Costing1,
comment, Title, CBGYear )
SELECT DISTINCT luMEIsforBG.MEI, BGCostbyPart1.PartNumber, BGCostbyPart1.
QTYBreak1, BGCostbyPart1.QTY1, BGCostbyPart1.Costing1, luMEIsforBG.comment,
[Forms]![BuyersGuideADD]![Title] AS Title, [Forms]![BuyersGuideADD]![CBGYear]
AS CBGYear
FROM luMEIsforBG LEFT JOIN BGCostbyPart1 ON luMEIsforBG.MEI = BGCostbyPart1.
MEI
ORDER BY luMEIsforBG.MEI, BGCostbyPart1.PartNumber;

Thanx for looking at it.
s.

Here is the SQL
[quoted text clipped - 5 lines]

form references are being used as *criteria* to select records, rather than as
fields to be added.

 

[Duane Hookom]

If you have a question regarding your query, it just makes sense to provide
the SQL view of the query in your first post.

Try removing the "DISTINCT".

--
Duane Hookom
Microsoft Access MVP

Ok...Here is the SQL

INSERTINTO CBGBuyersGuidebyParts ( MEI, PartNumber, QTYBreak1, QTY1, Costing1,
comment, Title, CBGYear )
SELECT DISTINCT luMEIsforBG.MEI, BGCostbyPart1.PartNumber, BGCostbyPart1.
QTYBreak1, BGCostbyPart1.QTY1, BGCostbyPart1.Costing1, luMEIsforBG.comment,
[Forms]![BuyersGuideADD]![Title] AS Title, [Forms]![BuyersGuideADD]![CBGYear]
AS CBGYear
FROM luMEIsforBG LEFT JOIN BGCostbyPart1 ON luMEIsforBG.MEI = BGCostbyPart1.
MEI
ORDER BY luMEIsforBG.MEI, BGCostbyPart1.PartNumber;

Thanx for looking at it.
s.

Here is the SQL

I have an append query that is being used to populate a table. I have two
fields that are being pulled from data on the Form that initiates the append

[quoted text clipped - 5 lines]

form references are being used as *criteria* to select records, rather than as
fields to be added.

--



.

 

[John W. Vinson]

Ok...Here is the SQL

INSERTINTO CBGBuyersGuidebyParts ( MEI, PartNumber, QTYBreak1, QTY1, Costing1,
comment, Title, CBGYear )
SELECT DISTINCT luMEIsforBG.MEI, BGCostbyPart1.PartNumber, BGCostbyPart1.
QTYBreak1, BGCostbyPart1.QTY1, BGCostbyPart1.Costing1, luMEIsforBG.comment,
[Forms]![BuyersGuideADD]![Title] AS Title, [Forms]![BuyersGuideADD]![CBGYear]
AS CBGYear
FROM luMEIsforBG LEFT JOIN BGCostbyPart1 ON luMEIsforBG.MEI = BGCostbyPart1.
MEI
ORDER BY luMEIsforBG.MEI, BGCostbyPart1.PartNumber;

Well, just for starters, there should be a blank between INSERT and INTO!!!

For another, there are no criteria: this will return all records in table
luMEIsforBG and any matching records in BGCostByPart1 and (attempt to) insert
them into CBGBuyersGuideByParts.

Another possible problem (speculation on my part): the fact that you're
sorting the INSERT query, and the "by parts" portion of the name, makes me
suspect that you're assuming that this table will be sorted by MEI and by
PartNumber. IT WON'T. Tables have *no order*; they're unordered "heaps" of
data. If you want records on a report or form displayed in some particular
order you must use a sorted Query, or (for a report) use the Report's Sorting
and Grouping. An Order By clause in an INSERT query is (at best) pointless.

 

[RedHeadedMonster via AccessMonster.com]

Thanx! Works like a charm now.
RHM

If you have a question regarding your query, it just makes sense to provide
the SQL view of the query in your first post.

Try removing the "DISTINCT".

Ok...Here is the SQL

[quoted text clipped - 18 lines]

 

[RedHeadedMonster via AccessMonster.com]

Ok...Here is the SQL

[quoted text clipped - 7 lines]

MEI
ORDER BY luMEIsforBG.MEI, BGCostbyPart1.PartNumber;

Well, just for starters, there should be a blank between INSERT and INTO!!!

There was/is a space between these two. This server would not let me post
the SQL until I took the space out.

For another, there are no criteria: this will return all records in table
luMEIsforBG and any matching records in BGCostByPart1 and (attempt to) insert
them into CBGBuyersGuideByParts.

Exactly what it is supposed to do.

Another possible problem (speculation on my part): the fact that you're
sorting the INSERT query, and the "by parts" portion of the name, makes me
suspect that you're assuming that this table will be sorted by MEI and by
PartNumber. IT WON'T. Tables have *no order*; they're unordered "heaps" of
data. If you want records on a report or form displayed in some particular
order you must use a sorted Query, or (for a report) use the Report's Sorting
and Grouping. An Order By clause in an INSERT query is (at best) pointless.

I had sorted the query so that I could see All the MEIs with their assocaited
parts together.