Auto populate a table

[keiath]

I have a table that's called invoices that will be populated via a excel spread sheet. The Information that is imported is missing some detail, I could do this in excel but there can be 1000's of invoices for 1 site, I just want to import without messing about with the data in excel.



(The query will sort and output into the form in the order I need)

When I import the data it is missing the Expense Code Description, but has the Expense Code.

I then created a table called Expense Code, that has the code number and the description

Is it possible when i import the data that it can then add the description without the need for a form. The Exp Code field is already a combo box (as some invoices might be coded incorrectly and i need to be able to change it)

Thanks

[ItsMe]

Would it be reasonable to join the expense code column with the other table's expense code and then retrieve the description using a query?

[keiath]

That's what i was trying to do, but didn't seem to be working, however I went about it another way created a query (using both tables) and that gave all the information, and when I change the exp code it changes the description, this is what I wanted, just wanted to do it directly into the table, but as I said works just as well in the query, I will use the data just to import, but the query to display the results in need in the forms and reports.

Thinking about it this is actually a cleaner way to do it as it means that i can change a exp code in the query and it will update the table and same vis-a-versa.

I then created another query so that i can sub total by group.

This now gives me two things for the report a summary of invoices by code, and a detailed invoice listing.

One thing I would like to know is it possible to sub total in the query for each exp code?

[June7]

That is an aggregate (Totals) GROUP BY query.

Or use report Grouping & Sorting features with aggregate calcs in footer section. This allows display of detail records as well as summary calcs.

[ItsMe]

... i can change a exp code in the query...

You want to edit/update data in the table. You can do this via some queries. Use a form to view, edit, add data. If you need to edit a category for a given record, edit the primary key value.

[keiath]

That's what i was trying to do, but didn't seem to be working, however I went about it another way created a query (using both tables) and that gave all the information, and when I change the exp code it changes the description, this is what I wanted, just wanted to do it directly into the table, but as I said works just as well in the query, I will use the data just to import, but the query to display the results in need in the forms and reports.

Thinking about it this is actually a cleaner way to do it as it means that i can change a exp code in the query and it will update the table and same vis-a-versa.

I then created another query so that i can sub total by group.

This now gives me two things for the report a summary of invoices by code, and a detailed invoice listing.

One thing I would like to know is it possible to sub total in the query for each exp code?

I think this is totally the way to do this, allow tables/Queries to collect/sort data and then let the report itself subtotal etc

Thanks to you and ItsMe