I’m trying to do an Append query, which will add records to an existing table. I have a query which gives me the records I want to add, (and I’ve tried this twice, once not using the table I wish to append to, and one query using the table) but when I try to set up the append, the drop-down only lets me see the other table, the one I don’t need to append.
The tables are pretty straightforward. An aging table which give cust number, invoice number, invoice date, etc. The other table has the cust number, invoice number, and a memo field for comments made about attempts to collect the invoice (For example “Mailed copy invoice to John Smith”).
What I’m trying to do is append the comments table, so I can see the past due in that table, even if there aren’t notes yet. If I just write the query to see comments for past due, it will show the invoices if there’s a matching value in the comments and aging tables, but not if it’s just in the invoice table. I want to set them up so all you have to do is enter the comment when you work it, rather than manually entering the cust number, invoice number and comment for each item. I can then set it so it shows the list for which there aren’t comments, which are the items that need to be worked.
Any Access pros out there? I know I could do it if I could get that table to append.
Append means to add additional records to the bottom of an existing table, not to copy a field from one table to another, which is what I am understanding you wanting to do.
If I am correct, then the thing to do would be to write a make-table query to create a new table which combines all the fields. This is probably how it should have been created in the first place, if there is only one comments field for each customer/invoice combonation.
Grim Pixie - No, I want to add to the comments table more records, records of accounts which will have comments added. For example:
Comments Table (as it is)
Cust Num Invoice Num Comment
123 ABC Pay Inv
234 BCD Disputing
345 CDE Sent Copy
I want to add it so it looks like this:
Comments Table
Cust Num Invoice Num Comment
123 ABC Pay Inv
234 BCD Disputing
345 CDE Sent Copy
456 DEF
567 EFG
Those extra appended invoices haven’t been contacted yet, but I need them in the table.
I think that you might be right that ‘append’ is not what is called for.
Or you could use an ‘update query’ to copy fields from a query into existing records in one of the current base tables - you can modify the table first to give it the right field, and then update the values into it.
Okay, when you pick query type and ‘append’, you should get a dialog box asking you for the append to table name - this should be comments. If comments does not appear in this dialog box, then something very strange is going in, (like comments not being a ‘real’ table but a link, query, etcetera.)
Once that is done, the ‘append to’ line in the query designer should show you comments fields.
Does this help you out any? Also, what MSaccess version are you on?
When I use the Append dialogue box, it gives me the aging table, but not the comments table. It does see the table when creating regular queries, though. Both tables were created by importing data from an excel spreadsheet.
Alright, go to the main database window, click on ‘tables’, and look at the icons next to aging and comments. Is there any difference?
If there’s a little arrow next to the comments table icon, then that means you didn’t actually import the spreadsheet, you linked to the spreadsheet. Linked tables are fairly transparent in most parts of access, but apparently they don’t qualify as the target of an append query.
If all of this is true, then you need to:
‘delete’ the table, (actually just deleting the link, the data should be safe in the spreadsheet, but you can back up the database .mdb file beforehand just in case.)
import the spreadsheet all over again, making sure to pick ‘import’ and not ‘link table.’
HiOpal.mdb
After all this, you should be able to append to the table.
Okay, this is interesting. I’ve done a little looking, and there seems to be a glitch in access 2003 SP3 with this feature. It won’t show the first table in the full database in alphabetical order, or something like that.
So - try making a dummy table and calling it aa? If the aging table is actually called ‘aging’, then this might not be it, but I thought it was worth mentioning.
Another approach is to use the ‘sql’ tab in the query toolbar to turn your query into an append query manually - basically you just need to stick ‘insert into comments ([Cust Num], [Invoice Num], [Comment])’ in front of the select part.