Concantenating Fields (1 Viewer)

browninaz

Ugly Data Hoarder
Local time
Today, 04:52
Joined
Oct 26, 2012
Messages
88
Hello all,

This has been a struggle for me to try and figure out for awhile. I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile
Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20
Area2FloorPrep (tblFloorPrep)
Area2Size (tblInstallationAreas)....

All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concantenate the data in the 20 fields and concantenate the size of the areas next to the appropriate concantenated floor prep description. Is there a way to do this without coding?

Hopefully someone understands what I'm saying. If not, whats the right way to ask?

Thanks to all who will help this poor novice...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:52
Joined
Feb 19, 2002
Messages
42,970
You really need to go back and redesign the tables. Whenever you have more than one of something you have "many" and "many" implies a second table. Once you do that, you can have from 0 to infinity "areas". You will use a subform to add additional areas. One area per row.

I don't understand why you need to concatenate the columns. Try making a report with a subreport once you restructure the table.
 

browninaz

Ugly Data Hoarder
Local time
Today, 04:52
Joined
Oct 26, 2012
Messages
88
Thanks for your input Pat, but I can't figure out why I need to redesign my tables for the question at hand. I have one table that is strictly for floor prep, which includes everything from the floors, steps, patching, floating, etc., and one table for installation areas which includes the floors, steps, walls, or where ever you can install flooring (or tile, etc.). I tried to group different categories of the whole sales process while thinking of structure and design, but where I fell short is my lack of experience in querying a database, and well, lack of an education in this field (developing). If I may ask, how would you do it? I'll attach a copy of the structure that I have as of now, see what you think.
 

Attachments

  • Structure.jpg
    Structure.jpg
    90.8 KB · Views: 110

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:52
Joined
Feb 19, 2002
Messages
42,970
The tables mentioned in your original post contain a repeating group. Instead of having 20 instances in one row, the table should be designed to hold one instance per row and so you will have a different question once you correct the tables. I still don't understand what you are trying to concatenate. Invoices typically have one line per item.
 

Users who are viewing this thread

Top Bottom