Concatenate 4 fields

  • Thread starter Thread starter Broadbonian
  • Start date Start date
B

Broadbonian

I have Duane Hookam's generic module for concatenate, but SQL & I just don't
see eye to eye. I need to concantenate each field (EmpName, Equip, trucks,
subs) by themselves, not into one field. Can someone help?
SELECT tsch1.JOB, tsch1.BEGDT, tsch1.ENDDT, temp.EmpName, teqpcpconca.EQUIP,
tsch1.TRUCKS, tsch1.subs INTO tsch2
FROM (tsch1 INNER JOIN temp ON tsch1.EMPLOYEE = temp.NameClass) INNER JOIN
teqpcpconca ON tsch1.EQUIPMENT = teqpcpconca.Eqpcpcon;
 
I'm not exactly clear what you mean by "... concatenate each field ... by
themselves..."

It might help folks offer more specific suggestions if you'd provide more
specific description of your data -- after all, "how" depends on how you've
structured your data!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for the help, I will try to explain. My form has 9 unbound combo
boxes per employee, equip, truck, and sub. The user has a job, begin date,
and end date field. He can have any number of combination on the other 4
fields. I use a separate "make table" query for the 4 fields with their
combinations of jobs, and dates.
INSERT INTO CreateSchEmp ( JOB, EMPLOYEE, BEGDT, ENDDT )
SELECT Forms!SCHEDULE!JOBNAME AS JOB, Forms!SCHEDULE!Emp1 AS EMPLOYEE,
Forms!SCHEDULE!BEGINWEEK AS BEGDT, Forms!SCHEDULE!ENDWEEK AS ENDDT;

If I only use 3 employees out of the 9 combo boxes, how do you prevent it
from still updating the job and date fields. I have tried to use "not null"
when there is no entry, but it doesn't work.

I query on the "make table" to get my final query, but would like to list
the info in 1 row not multiple rows.

Employee Equip Trucks
Subs
Jeff, Jim, Fred 966, D5 Cat, Broom Trk1, Semi3 ABC Cons,
Harmon Pipe

This is the query for the report
SELECT CreateSchEmp.JOB, CreateSchEmp.EMPLOYEE, CreateSchEmp.BEGDT,
CreateSchEmp.ENDDT, CreateSchEqp.JOB, CreateSchEqp.EQUIPMENT,
CreateSchEqp.BEGDT, CreateSchEqp.ENDDT, CreateSchsub.JOB, CreateSchsub.subs,
CreateSchsub.BEGDT, CreateSchsub.ENDDT, CreateSchTrkCP.JOB,
CreateSchTrkCP.TRUCKS, CreateSchTrkCP.BEGDT, CreateSchTrkCP.ENDDT
FROM CreateSchsub, CreateSchTrkCP, CreateSchEmp INNER JOIN CreateSchEqp ON
(CreateSchEmp.ENDDT = CreateSchEqp.ENDDT) AND (CreateSchEmp.BEGDT =
CreateSchEqp.BEGDT) AND (CreateSchEmp.JOB = CreateSchEqp.JOB)
GROUP BY CreateSchEmp.JOB, CreateSchEmp.EMPLOYEE, CreateSchEmp.BEGDT,
CreateSchEmp.ENDDT, CreateSchEqp.JOB, CreateSchEqp.EQUIPMENT,
CreateSchEqp.BEGDT, CreateSchEqp.ENDDT, CreateSchsub.JOB, CreateSchsub.subs,
CreateSchsub.BEGDT, CreateSchsub.ENDDT, CreateSchTrkCP.JOB,
CreateSchTrkCP.TRUCKS, CreateSchTrkCP.BEGDT, CreateSchTrkCP.ENDDT;

Thank You
 
see comments in-line below...

Broadbonian said:
Thanks for the help, I will try to explain. My form has 9 unbound combo
boxes per employee, equip, truck, and sub.
Click to expand...

You say you have 9 unbound comboboxes and 4 categories (employee, equip,
....). Does that mean you have 36 unbound comboboxes?
The user has a job, begin date,
and end date field.
Click to expand...

How are these three related to the previous "fields"? I don't have a clear
idea of what data structure you're working from.
He can have any number of combination on the other 4
fields. I use a separate "make table" query for the 4 fields with their
combinations of jobs, and dates.
Click to expand...

I don't understand why you are creating a new table. If you can put the
combinations together in a query, why make a table out of that query?
INSERT INTO CreateSchEmp ( JOB, EMPLOYEE, BEGDT, ENDDT )
SELECT Forms!SCHEDULE!JOBNAME AS JOB, Forms!SCHEDULE!Emp1 AS EMPLOYEE,
Forms!SCHEDULE!BEGINWEEK AS BEGDT, Forms!SCHEDULE!ENDWEEK AS ENDDT;
Click to expand...
If I only use 3 employees out of the 9 combo boxes, how do you prevent it
from still updating the job and date fields. I have tried to use "not
null"
when there is no entry, but it doesn't work.
Click to expand...


We're not there. We can't see your 9 comboboxes (or is it 36?). More
specific description of the underlying data and "what" you want to
accomplish (rather than "how" you are trying to do it, whatever it is) might
help folks help you. We don't know how/where you are "updating the job and
date fields".

When you say "it doesn't work", what happens? Do you get an error message?
If so, what does it say?
I query on the "make table" to get my final query, but would like to list
the info in 1 row not multiple rows.
Click to expand...


Again, we're not there. Can you give an example of how (some query)
generates (some rows)?
Employee Equip Trucks
Subs
Jeff, Jim, Fred 966, D5 Cat, Broom Trk1, Semi3 ABC Cons,
Harmon Pipe
Click to expand...

This seems to be one row ... is this what you get or what you want?
This is the query for the report
SELECT CreateSchEmp.JOB, CreateSchEmp.EMPLOYEE, CreateSchEmp.BEGDT,
CreateSchEmp.ENDDT, CreateSchEqp.JOB, CreateSchEqp.EQUIPMENT,
CreateSchEqp.BEGDT, CreateSchEqp.ENDDT, CreateSchsub.JOB,
CreateSchsub.subs,
CreateSchsub.BEGDT, CreateSchsub.ENDDT, CreateSchTrkCP.JOB,
CreateSchTrkCP.TRUCKS, CreateSchTrkCP.BEGDT, CreateSchTrkCP.ENDDT
FROM CreateSchsub, CreateSchTrkCP, CreateSchEmp INNER JOIN CreateSchEqp ON
(CreateSchEmp.ENDDT = CreateSchEqp.ENDDT) AND (CreateSchEmp.BEGDT =
CreateSchEqp.BEGDT) AND (CreateSchEmp.JOB = CreateSchEqp.JOB)
GROUP BY CreateSchEmp.JOB, CreateSchEmp.EMPLOYEE, CreateSchEmp.BEGDT,
CreateSchEmp.ENDDT, CreateSchEqp.JOB, CreateSchEqp.EQUIPMENT,
CreateSchEqp.BEGDT, CreateSchEqp.ENDDT, CreateSchsub.JOB,
CreateSchsub.subs,
CreateSchsub.BEGDT, CreateSchsub.ENDDT, CreateSchTrkCP.JOB,
CreateSchTrkCP.TRUCKS, CreateSchTrkCP.BEGDT, CreateSchTrkCP.ENDDT;
Click to expand...

?!What report?

More info, please...!


Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I am probably going about this all wrong. Could I PDF you 2 pages of what I
am trying to do? I do have 36 unbound combo boxes.
 
No, thank you. Like many other folks who help out here, I am volunteering
my time.

If you want to send someone something to look at and to work directly with
you, consider hiring some help.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thank you. I appreciate the help. The excel schedule they use is a grid with
days of the week as column headers. The rows are job, employee, equipment,
truck, and subs. They format the column cells with different colors to
represent those fields on the days they are the job. You can see at a glance
that on Monday & tues. you need 3 employees(yellow). Monday thru Fri 8
trucks(green). and so on.
I thought I could do the same thing in access, so they would only have to
point & click.
 
Darc

It would appear that you are "committing spreadsheet" on Access.

Access doesn't have "row titles" like job, employee, equipment, ... and
using "days of the week" as column/field titles is a poor practice in
database design (what do you do when you need another 7 days ... add seven
more fields?!).

I guess I'm not clear on your underlying business need/requirement. What is
it that you want to be able to do that Excel isn't doing for you?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I was just trying to save them frustration/time. They don't have much
training in excel/typing. I have tables for the jobs, employees, trucks,
equip, and subs. My calendar table would only display Mon, Tue, Wed, ect. So
no need to add more fields. If they could just pick from those tables,
change the date/job, and like magic(to them)-their schedule would print out
for them.
 
In my experience, using Access to make something "easy" ... is HARD! You
have to do a lot of data analysis, user-proofing (graphical design) and
application development. That's three learning curves for you, vs. one for
the users.

Are you prepared to invest a fair amount of time to come up to speed on all
three to make their task simpler?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I agree, it is hard but fun too. We are a paving/construction company, and
with the recession our work load is almost half of the usual. I would be
sitting here staring at walls if I wasn't working on this. BORING....Thanks
for the help. I'll keep plugging away.
 
Back
Top