Access to Word mail merge - data format

[Lex_iuk]

Hi - I have just started using Access again after a break of about 10 years so I hope you can help!

Basically I am using Access to create a Customer Relationship Management system for my brother's business, I have set up all the tables and queries I need fine and am now creating an invoice/delivery note template in word so that I can 'mail merge' all the data in from the Access database. However, I have set up one of the data fields (Product type) in the 'Products Table' to link to the 'Orders Table' as a 'lookup' list - I though I was being clever - so it would be easy to add new products to the 'Products Table' in the future, which would then just appear in the 'Orders' table for the admin person to select from the dropdown list. Although the product names column is set up as 'short text' when it appears in the 'Orders' table it is shown as a number and hence not suitable for my mail merge as instead of reading the product name i.e. Softwood Kindling it just says '5'.



Hope this makes sense and you can help!

Many thanks

[orange]

Show readers a jpg (screen capture) of your relationships window.
Are you using Lookup fields in your table(s)?

[ItsMe]

...However, I have set up one of the data fields (Product type) in the 'Products Table' to link to the 'Orders Table' as a 'lookup' list - I though I was being clever...

This is a common mistake. It seems easy but it just creates issues down the road. What I do is create a table and store the values in a table. I use a naming convention that indicates the table object is a list. So, instead of tblStates I would call it lstStates.

Then, I would simply use my lstStates in a query object.

Also, there is a wizard to create a word doc that links to your DB and a query within. The wizard is under the External Data tab of the ribbon > Word Merge. Just click on the query object within the Navigation Pane to highlight it before launching the Wizard.

[Lex_iuk]

Show readers a jpg (screen capture) of your relationships window.
Are you using Lookup fields in your table(s)?

Screenshot attached (hopefully!) I am using the 'Product List' table as a lookup list for the 'Orders' table.

Cheers!

[Lex_iuk]

Thanks - I'm happy with the fundamentals of creating the word merge doc, it's just the data converting to numbers somewhere down the line I need to fix. Hopefully the screenshot I've uploaded will make it clearer, I'm not sure I'm explaining it very well! I've already inputted over 200 records so I'm hoping I can sort it out without having to re-do it!! Cheers

[ItsMe]

...Screenshot attached (hopefully!) I am using the 'Product List' table as a lookup list for the 'Orders' table....

One thing that many developers do not like to use are Lookup Fields. If you used the Wizard to determine the properties of a field within a table then you likely have one of these "Lookup Fields". I do not believe the Relationships Window will discern whether or not a field is of type Lookup.
http://access.mvps.org/access/lookupfields.htm

What I can see by your screenshot is that your data is not Normalized. A search on the term will provide more info on the topic than you will probably ever care to understand. However, you may be able to find a basic explanation. I did not read the whole thing but this one seems to be rather basic.
http://databases.about.com/od/specif...malization.htm

[orange]

I agree with ItsMe --your tables are not normalized. When you see fields within tables with names like Product1, Product2...Product5, it sends up a red flag suggesting the table design is not normalized. I see tables(CustomerDetail) in the tables list that are not in the relationships window. Why?

Here is a free sample data model from Barry Williams that shows a generic set up for Customers, Orders and and Products.
It shows the general set up of tables and relationships for Customers, orders and products. It may not represent all that you need, and may have more info in some areas than you need. It is generic, so you can use and/or modify as necessary to meet your needs.

With Access you should choose a naming convention that uses only alphanumeric and "_" underscore characters for names of fields and objects. You should not have embedded spaces or special characters. This will save you a lot of syntax errors and frustration.

There are some links to free videos( related to Customers/Orders/Products) and tutorials in this post that may help you with design concepts, normalization and relationships.

Good luck.