Append query help

[syntaktik]

I need some help with an append query. I found a website, which solves the exact problem i am trying to solve here. But, for some odd reason, it just does not seem to work. Could there be a problem with my database?

I had posted this in the other category, but as it turns out this is actually a query, not a form problem.

 

[Anchoress]

In what way is it not working? Do you get any specific error messages, and/or does the data append incorrectly?

 

[syntaktik]

the problem seems to be that nothing happens. It just wont pull the data from either tables. i tried adding a primary key to one of the tables, but still did not work.

 

[speakers_86]

I looked at your db, and qry_CadetCurRank is not their. When I created that query, it then said that the output table is not there.

 

[syntaktik]

Ah yes, that does not seem to work. But that is one i might solve at a later time.
However the query i am having difficulties with is "qryAddPeopleEvent". To add to that, i also may have found the reason why it isn't working. Append queries cant pull NULL fields. How do i change the fields to zeros maybe, or am i still wrong?

 

[syntaktik]

ok, well. After som fiddling around, i managed to get it to work just as i want it to. However, is it normal that is can create duplicates if i run it again?
so new question:
How do i prevent an append query making duplicates?

 

[speakers_86]

In regards to null fields, you need to make it a habit to use the nz() function. nz(fieldname,0) will return your field name or whatever is after the comma (which you can leave blank).

In regards to duplicates, run the query wizard and build an unmatched query. When that query runs correctly, you can change the query type from select to append.

 

[syntaktik]

In regards to null fields, you need to make it a habit to use the nz() function. nz(fieldname,0) will return your field name or whatever is after the comma (which you can leave blank).

In regards to duplicates, run the query wizard and build an unmatched query. When that query runs correctly, you can change the query type from select to append.

I took a look at that, but im clueless. How do i get it to do anything, it does not seem to do anything. Is there an example out there?

And i hope im not piling up all the questions, but how do you filter a subform? Every single answer i can find is different from another.
Any help is really appreciated.

 

[speakers_86]

I took a look at that, but im clueless. How do i get it to do anything, it does not seem to do anything. Is there an example out there?

Which part are you talking about? The nz function, or the unmatched query?

And i hope im not piling up all the questions, but how do you filter a subform? Every single answer i can find is different from another.
Any help is really appreciated.

There is more than one way to skin a cat. Without knowing more, I would have to tell you that in whatever command you use to open the form should have the filter in the WHERE clause. I have been told this is the best way, because the query is reusable, and it is easier on system resources.

You could include criteria in the query. This is easy, but the query may not be reusable if you use this method. Also, I have heard that this is not the best approach (if I remember correctly) because it is slightly rougher on system resources, though it is probably the approach I use the most.

If this is a subform, you can drag a query into the form, and the new subform will have an sql statement in the record source, not a query. You can then go to the record source and click on the elipses to see the query builder, where you can edit the sql statement, and not the original query. I just figured this out last night, so if I am mistaken on this one, please let me know. But that is what I gathered from playing with things.

If it is a datasheet form, the end user could filter themselves.

I am sure there are more ways. Use whatever suits your needs.