I need help writing an expression that combines several fields into one.

[Ben@AccessForums]

I stopped using Access several years ago because I could not get it to work like I wanted or at least the way thought it should work. I have been working with FileMakerPro because it seems so much easier to work with and was able to work toward getting my database completed. Now I am concerned that it is a program into itself and may not allow me to work with other programs.

Now I have updated my Office program and I want to see if I can finish what I started several months ago in Access. But I seem to have came back to the same problem I had before, which I thought I had resolved because it works in FileMakerPro.

I have been able to create an expression in my description field that looks at the color field and finds only the ID number and not the color itself. The color field is chosen/supplied by the Color_Table. When I change colors the ID number changes in the description field but I can't find any way of displaying the colors.

I don't want to continue with this project until I get this resolved. I have several other fields and tables that I want to add to this expression, but if one won't work than the others won't work either.

[alansidman]

If I understand correctly you have a color ID in one table which is linked to another table which holds the description. You wish for your query to display the color and not the ID. Add your color table to the grid. Join it to the primary table on the color id. Uncheck the box to display the field holding the color ID in your QBE. Add the description field from the color table to the QBE.

[Ben@AccessForums]

OK, first of all you need to know that I barely know what I am doing here. I have been working with these database programs using trial and error. But I have been able to figure some of this out even better than some of "pros" I've talked to about my problems as they have come up. I'm saying that to let you know that I am not stupid. As I have said, I have been able to get this to work in FileMakerPro, but the same formulas do not work in Access. I hope you can understand.

I have copied the first five fields of my Master_Material_Table and are shown below. When I get this working, I will be adding several more fields after the "Color" field. Each of these will be a different part of the Description field when I get the expression to work the way I want. The Color field gets its color from the Color_Table using the "lookup" option. Right now you can see that amber is the first color in the Color_Table and that is what is being shown in the Description field. I want the word "amber" to be shown there and not the ID number. This is the formula that works in FileMakerPro....Description = Size & " " & Length & " " & Name & " " & Style & " " & Color and I have tried several different expressions using Access and none have worked.

ID	Name	Part_Number	Description	Color
3	ABC	00258248	1	amber

[orange]

I'd like to suggest that trial and error is not the only, nor the preferred way to approach database. Note, that I didn't say it couldn't be done that way -- and a lot of posters with 3 -4 posts usually with Acc2010 and Win7 (latest Software) are attempting to do that very thing.

Do yourself a major favour --- research Normalization and Entity relationship Diagramming, then spend some time on Youtube (or some books) watch/read some related tutorials. Get an understanding of what is involved in Database design; watch some Youtube on Access in general and some samples; then do some practical experimenting with your own subject matter. You'll learn so many techniques you'll amaze yourself.

It's a little bit like learning to be a pilot; some training and theory often win out over the trial and error methods.

You can do as you please, but here are some links to tutorials that will really lighten your learning.


Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening
rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

A standard list of helpful items for new developers:
A good data model is the first step to realizing any database and Database Answers (free models) has several free models on which to base a business concept for getting some ideas to match what may closely compliment your business model.

The concepts of database building are best conceived when thoroughly planned . . .

• Database planning tutorial Part I
• Database planning tutorial Part II
• Argeedblu's FAQ on Design Strategies

And the following links on normalization cannot be stressed enough . . .

• Noah's FAQ on Normalization
• Roger's Blog on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• MSKB 283878: Description of the database normalization basics

Tutorials, KB Articles and Demos that can assist a new Access developer are found here:

• UA Access Tutorials
• Crystal's Access Basics for Programming
• Access Tutorial
• Access VBA Tutorial
• UA Access Knowledgebase / FAQ
• UA Code Archive

Good luck with your project

[alansidman]

Why? Tables are used for storage of data only. If you need to see the color then you do that through a query. Also, why would you have the Description and Color both showing in your table?

It might be helpful for you to read this white paper and then you will have a better understanding of how relational data bases work.

I am not familiar with FileMaker and cannot discuss the differences. I do know that when using Access or any RDBMS you will see the result as you have it so long you have a normalized environment.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Edit: I concur emphatically with Orange's comments above. You will do yourself a great service in doing some research.

[Ben@AccessForums]

Ok guys, I guess I have come to the wrong place to get my questions answered. I think it would have been easier to tell me how to do this than tell me where to go to find out for myself. I have several books about Access and other database systems. Plus I know how to Google. I only go to them when I can’t figure something out myself. And as I’ve said before, I’ve talked with other database teachers and coworkers making their living working with databases and some of them could not help me with whatever problem I was having. Even with this problem, I’ve been told the expression should work.
I understand the purpose of normalization to reduce problems with data consistency by reducing redundancy. And this is what I believe am doing by building this the way I am building it.
I have 5800+ item descriptions that have over 30+ different ways to describe those items from color, size, length, and so on. I have made a table for each one of these 30+ ways to describe a part. I’ve only had to type these in once in each table. For every time I need to use one of those descriptions from those 30+ tables, I am able to choose it from the list. I only have to type in the first few letters and it appears in the field. The end goal is to get to a point where I can export the Name, Part Number, and Description into any other program I choose. Plus all of the descriptions will be consistent.
So I guess you can close this as resolved. Thanks

[alansidman]

I have re-read your original thread. Where are you attempting to write this description? If in a query or Form or Report, then you will need to add the table reference to the field name and it should work. If you are attempting this in a table, then it will never work. Expressions are calculations and they do not belong in tables.

[Ben@AccessForums]

I have tried to do this in the Table, Query, and Form. In Access 2010, I am told you can create a calculated field in a table. This eliminates the need for a separate query to do calculations. In FileMakerPro I've been able to do them in the table or form.



As I have said before, it is almost doing what I want, except it is returning the ID_Number of the color I am choosing rather than the text. If I chose a different color the corresponding ID_Number appears in the description field. So there must be some way to fix this. I did it once before with Access, but I can’t remember how. I’m still searching my files because I thought I had saved the expression somewhere on my computer.



I’ll post this while I keep searching for an answer.

[alansidman]

As a general rule, just because the folks at MS have allowed calculated fields in tables, it is not a good idea for a good database design. In your query, in a new field type.

Description1: [tablename].[Size] & " " & [tablename].[Length] & " " & [tablename].[Name] & " " & [tablename].[Style] & " " & [tablename].[Color]

This should work. BTW, the terms Name and Description are reserved words in Access and that may be part of the issue. Ensure if it is used, that it is surrounded in square brackets. Here is a partial list of reserved words.

http://office.microsoft.com/en-us/ac...010030643.aspx

[orange]

Want to post the query you tried?

[Ben@AccessForums]

[Ben@AccessForums]

I’m sorry this is not working out as I planned.

OK, based on what you have said, I renamed “description” to “part” just as a test. But when I’m finished I can call it Part_Description and Access will accept that, correct?


So I did as you said and tried entering Part: [Master_Material_Table].[Color] (without the other tables) in the Part field of the Query on the Criteria line and Access wants to place quotes around it when I save it and then gives me an error message of “Data type mismatch in criteria expression” when I try to open it in Datasheet View.

I am wondering about the colon after "part". Was this a mistype? Because I can get the expression to work, but with the same issue as before using the = sign there. This is what I have used [Part]=[Master_Material_Table].[Color]

ID	Name_Manufacturer	Part_Number_Manufacturer	Part	Color
1	3-M	050	3	beige
2	AC	01884	1	amber
3	ABC	00258248	2	aqua
4			1	amber

[alansidman]

You do not put the expression in the criteria line. Here is what I suggested

In your query, in a new field type.

Description1: [tablename].[Size] & " " & [tablename].[Length] & " " & [tablename].[Name] & " " & [tablename].[Style] & " " & [tablename].[Color]

This goes in the area of a new column where you would put the field name. You are creating a new field that has an expression. The colon is not a typo. It tells access to create a new field with the information after the colon as the expression (formula).

I know you really don't want to look at other instructions, but this is a clear example of what I am trying to show you.

http://www.techonthenet.com/access/queries/concatenate2007.php

Alan

[orange]

Ben,

How's it going? Have you resolved the issue?
Perhaps you can post a copy of your database, or post the SQL for the query(s) involved along with your table structures and some sample data.

[Ben@AccessForums]

I’m sorry I have not been working on this for several days. After “ alansidman’s” post about having to do this in a query, I realized that I don’t know how to work with a query. I think I’ve only used them to check for duplicates. Everything I’ve worked with before has been done in the tables and forms. And it may have been just by chance that the form I had been using even worked. So it looks like I’m going to have to go back to the books to understand all of this. I’m not opposed to looking at new instructions or learning something new, but at my age some things are harder to get through my thick skull. As I’ve said, I am doing this already in the database I am using right now, but wanted to start using Access.


I have used this data for years using Lotus Symphony when I started my electrical contracting business years ago. I was able to create estimates and invoiced from it for years. I later moved to an Access database that was called a “glorified spreadsheet” by one guy that looked at it for me. But it worked for me. I wish I had the knowledge to put it all together like many of you have, but I just do the best I can.


My goal right now is to get all the descriptions uniform throughout the database. I am doing that now using the expressions in FilemakerPro. Each description might have as many as 7 different manufacturers and their part numbers that would go along with them. I guess you can say that I am trying to take the information from 7 catalogs and place it into one database for the items I would sell/use.


The bottom line is that I want to thank you all for your help and input with this problem. I’ll be reading up on all this information hoping I’ll understand what I am doing. Thank you all.