Append Query: Making too many records.

[dynamite9585]

SOLVED: Append Query: Making too many records.

I have 2 append queries behaving in a similar manner.

one of the values i need to append is from a combo box on the form.
it appends the correct values to the table but the number it appends in equal to the number of values in the combo box.

INSERT INTO CadetCourses ( CadetID, CourseID, DateAdded )
SELECT JncofinderQuery.[Cadet Number], [Forms]![JncofinderForm]![Combo53] AS Expr1, Date() AS [Date]
FROM JncofinderQuery, UpcomingJNCOQuery
WHERE (((JncofinderQuery.tempcourse)=Yes));

the combo box displays the upcoming courses and is used to choose what course to send the person on.

the code given has 3 results in the combo box, therefore appends the same record to CadetCourses 3 times.

 

[JamesMcS]

Have you tried putting the column property in the query? As in

[Forms]![JncofinderForm]![Combo53].column(x)

Where x is the column number that you want to add to the table, starting from 0.

It's also good practice to name your fields, rather than using Expr1, and Date is a reserved word in Access so you shouldn't use that either

 

[Galaxiom]

I would not expect the number of records in the combo to be the issue. The problem is more likey to be the lack of a join between the two source queries. Without a join it will create a cartesian product where the number of records is the mathematical product of the number of records in the two queries.

 

[dynamite9585]

I think you might be on to something there Galaxiom. any ideas on solutions?
JamesMcS: would that not limit it only to what is at the top of the list? i need to be able to select what course i need from that box.

 

[Galaxiom]

Without knowing what is in those queries and the structure of the dat ais is not possible to make well targetted suggestions.

But I would expect the two queries have a field in common and they should be joined.

 

[dynamite9585]

Galaxiom, no they do not. they take data from 2 completely unrelated tables.
the append is to add them to a relational table so i can then fill out the required report (as a form letter)

 

[Galaxiom]

You are inserting records with values for three fields.
One field is a value from a form, another is the current date, leaving just one field.

That field must come from one table or the other so why do you have two source tables?

 

[dynamite9585]

Query 1: finds courses from the ATP table and adds those results to the combo box on the form.
Query 2: finds people who meet the requirements to attend those courses.

EDIT: had a think about relationships and removed the query that populates the combo box from the append query.
works fine now.

thanks for your help guys.