Hello

Im fairly new to databasing but have gained some knowledge over the years. I am still really using the onboard RAD systems as my SQL is minimal.

Im after help with Append & New Table querys. I am producing a business database for myself. The contents of the DB are as follows..

Tables: [Client Demographics] [Pet Demographics] [Pet Documents] and [Accounts]

No macros as yet

A basic Select Query to produce the main form with no critera added.

FORMS: [CarryPet]

The relationships involved are one to many through the ID keys.

I want to be able to creat a table that collates all the info from the main form to produce and account table where i can then add or append the data should the client return to the company for more work.

I am having an issue with the querys as it returnes an error of Multi valued fields are not allowed in Select INTO atatements?

Where am i going wrong? I understand i should have no multi valued field in the table of which i have two. Do i need to re think the table or is there a way around this issue?

I hope you can get a jist of what im trying to do. If you need i would be happy to send the DB for you to view the error as long as you dont laugh too much lol.

Any help would be very much appreciated.

Kind regards
Brian

Multivalue fields are best avoided. They are an Access 2007 (and 2010) only capability.
The same functionality can be achieved with a related table.

Thanks Galaxicom

I thought it might be ok back to the design board....

I want to be able to creat a table that collates all the info from the main form to produce and account table where i can then add or append the data should the client return to the company for more work.

The "main" form should be connected to a table with all the detailed information for the transactions. In a database one does not then record a summary of the same information in another table. The summary information is calculated "on the fly" for the reports and forms.

A properly indexed database can retreive and make enormous calculations on million records in a remarkably short time. Although it might seem useful to keep a summary, these figures can become inaccurate if the input data changes.

Database Normalization is lot about not storing figures that can be calculated from other fields or records.

The main form is connected to the tables via a Select Query which is then used to produce said form. The reason for doing this was to be able to use a tabed form with all the related info from each table. Then i was going to produce a command buttom on the last tab form which would then add all the contents of that to a table. I understand what your saying about doubling up information but the table was then going to be linked to the client info with an Append query so i could add record to individual accounts. Maybe im looking at this from thewrong angle?

Thank you for the advice

Brian