Combining 4 fields from one table into another single field of another table.
I want you to know up front that I am not a programmer or any other expert with databases. I have been trying to do this in Access for months and was able to do it once using 8 fields into one, but I don’t know how I got it to work. Once I learned I could do it I wanted to use the procedure for a different (Item Number) field so I started over with my database. I thought I had the correct SQL statement figured out, but it would not work when I tried it again. File Maker was recommended to me as a better database program, so I downloaded the trial version to see if I can get it to work.
The reason I want to do it this way is that I have several manufacturers with different part numbers that make the same part with the same description. By normalizing like this I have saved a lot of space and data entry. I had all this information on one spreadsheet when I started years ago and have imported it into a database.
I have created a new database with 4 lookup fields that look up several dozen manufacturers, hundreds of part numbers, and 2 other fields that help describe an item (number). The 8 field table will be used to provide a (item) description once I get the Item (number) table working. Both of these will be used in another table that helps me keep track of other information about these items. I am trying to have drop down windows that allow me to choose one of these items (number) and one of the (item) descriptions from a list. The final table will be used to calculate the price and maintain information about when and where I purchased the item. Plus it should cross reference each of the items with other manufacturers and their part numbers.