Last Attempt On This One

Sep 30, 2005

I have a form that opens after the user selects a "trade" from the combobox list on the "Trade parameter panel" form and clicks on a button. The text in the combobox becomes the parameter of the query that the "Trade" form opens from.
Once the form opens there is a textbox which is part of the heading i.e. "all employees listed as Welders" but instead of having all the trades in the list with an "s" on the end, i want to dynamically add the "s" to the end of say "welder" or whatever trade is selected.

The usual & "s" doesnt work because record source is dynamic and i dont want to have a seperate "s" textbox floating about due to the varying lengths of the trade string.

Below is the main part of the query

WHERE(((Trade.TradeID)=[Forms]![Trade parameter panel]![Combo4])AND((EmployeeCert.Level)=[Forms]![Trade parameter panel]![Level]))ORDER BY Employee.Surname;

Thank you in advance

View Replies


ADVERTISEMENT

Form Help, Not Sure How To Attempt

Aug 2, 2006

I am building a database with prices for different services.

Now for example i have a drop down box with "service 1, service 2, service 3" that can be selected.
Service 1 - $4
Service 2 - $8
Service 3 - $10
There are different prices attached to each service like down below. Now in my form, say that i change the "service 1" to "service 2". Now the prices are in different fields in the table.

So now my question is how will i get the prices to automatically change over? without them havnig to manually change the text box?

and if anyone wants a test DB to see what im talking about let me know and ill send one. This is really important and i need to get this solved asap. Any help possible would help greatly.

Thanks again,
Jon

View 2 Replies View Related

UNION Query - 1st Attempt

Jul 2, 2007

To try to get over some of my previous problems, I have tried using a Union Query - I understand this might help me get the results I need. Below is my SQL, which is meant to combine the results of the 10 individual queries. Have I misunderstood something, or not done something right? I am getting error messages.

The data is drawn from 2 tables - PROJECTS and PERSONNEL.


SELECT Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="1"))
GROUP BY Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date
HAVING (((Personnel.[1st Fix Men Plan])=True) AND ((Personnel.[1st Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))

UNION

SELECT Personnel.[C/Q Number], Personnel.[2nd Fix Men Plan], Personnel.[2nd Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="2"))
GROUP BY Personnel.[C/Q Number], Personnel.[2nd Fix Men Plan], Personnel.[2nd Fix Men ACT], Projects.Date
HAVING (((Personnel.[2nd Fix Men Plan])=True) AND ((Personnel.[2nd Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))

UNION

SELECT Personnel.[C/Q Number], Personnel.[3rd Fix Men Plan], Personnel.[3rd Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="3"))
GROUP BY Personnel.[C/Q Number], Personnel.[3rd Fix Men Plan], Personnel.[3rd Fix Men ACT], Projects.Date
HAVING (((Personnel.[3rd Fix Men Plan])=True) AND ((Personnel.[3rd Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))

UNION

SELECT Personnel.[C/Q Number], Personnel.[4th Fix Men Plan], Personnel.[4th Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[4th Fix Men Plan], Personnel.[4th Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[4th Fix Men Plan])=True) AND ((Personnel.[4th Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="4"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[5th Fix Men Plan], Personnel.[5th Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[5th Fix Men Plan], Personnel.[5th Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[5th Fix Men Plan])=True) AND ((Personnel.[5th Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="5"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 1 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 1 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 1 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 2 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 2 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 2 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 3 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 3 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 3 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION

SELECT Personnel.[C/Q Number], Personnel.[Snag 4 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 4 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 4 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))

UNION SELECT Personnel.[C/Q Number], Personnel.[Snag 5 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 5 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 5 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
ORDER BY Projects.Date

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved