I am trying to produce a report or query that lists 11 attributes for several projects measured each month over the duration of each project. i.e. It is required to have the attributes for project A listed (horizontally) for months 1,2,3,...etc (vertically) followed by those for projects B for months 1,2,3,4,5,6... etc. Can anyone advice on how to do this please?
View RepliesI am trying to display five columns from three seperate tables. I thought the GROUP BY operator would fix this, but I have more than one column to select. The query is fine but I have redundant fields showing. Oh, I'm not worried about the calculation "[employee.salary]*0.1 AS Salary_After_Raise." I just want to concentrate on narrowing the fields down first.
SELECT ID, FirstName, LastName, Salary, [employee.salary]*0.1 AS Salary_After_Raise, City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
I am trying to display five columns from three seperate tables. The query is fine but I have redundant fields showing. I thought the GROUP BY operator would fix this, but I have more than one column to select.
SELECT ID, FirstName,LastName, Salary,City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store=store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
Thank you.
hey all, thanks to anyone that can help
i have 5 fields facid custid empid amount invdate.
i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year)
the date range totals, last yeardate range
current ytd, last ytd
i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query.
i've used dsum() but not quite getting the results i should.
i think one of the problems is the user has the option of filtering the data based on the 3 fields.
anyways if you can help i'd greatly appreciate it..
I have a report with the fields
Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.
On the database people can be entered multiple times so I may have
Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes
Now I now with the group function I could group by the firstname_ field but the results would group like the following
Bob Smith
Bob Smith
Bob Holmes
Jackson Smith
Jackson Smith
Jimmy Carter
Tim Jones
And if I do it by surname, well I am sure you get the drift.
So how can I sort so I would get the following
Bob Smith
Bob Smith
Bob Holmes
etc etc.....
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies View RelatedIs it possible to have more than 9 OR criteria in an update query?
Thanks,
SKK
hi. my name is tim. im having some problems with a query that I am writing. it should be rather simple and involves only one table.
the table is called tblCustomer. The query involves 2 fields: cResort and cWeek. Neither of these fields are unique. Together they make up the primary key.
cResort is populated with a textfield: PF, PV, and GS.
cWeek is populated with an integer: 1-52 (the number of weeks in a year)
This is what the query is supposed to do:
SELECT tblCustomer.*
FROM tblCustomer
WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10
here's where the problem lies, this filters out ALL "PF" and ALL cWeek < 10.
I want PF's with a corresponding cWeek from 1-10. I dont want to see PFs with corresponding cWeek 11-52.
Example:
Show me cResorts: PF with corresponding cWeek:1-10.
Do NOT show me cResorts: PF with corresponding cWeek:11-52.
Is this clear? Thank you for the help.
I am trying to construct a fairly complex query on the fly. I have it working except for the criteria side of the query which I am having trouble with. It doesn't really matter about the vb side of it because I can alter the construction easy enough. it is more a question of how I get the criteria right.
I'll show you the SQL below first:
SELECT DISTINCTROW tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.Status, tblCandidate.CandidateID AS [Count]
FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));
The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.
This query works fine to give me all Candidates that comply with these criteria. The only trouble is I want to only results where the Candidate(s) ALL three set of criteria. I have tried replacing the OR with AND and using extra brackets to enclose the whole WHERE statement to imply I need all criteria met but this reports back no results despite me having test data where a candidate meets all criteria.
I am sure this is not as hard as I am making it but I can't figure it out at all.
I also ideally wanted to show only one record (ie one candidate that meets all instead of three records showing the candidates). This is not so important as I can work this out easily enough, by probably hiding fields I don't want to see and showing only unique values, etc...
Can anyone help me with this?
Thanks in advance,
Daz
:eek: I am looking for a method that works in query criteria (NOT HARDCODING SQL) as i am quite new to this.:eek:
i have two fields for query to lookup, firstname and lastname.
THE FIRSTNAME FIELD:
IIf(IsNull([Forms]![frmSearch]![FirstName]),"",[Forms]![frmSearch]![FirstName])
THE LASTNAME FIELD:
IIf(IsNull([Forms]![frmSearch]![LastName]),"",[Forms]![frmSearch]![LastName])
Intended operation;
1) The value is seen on my search form (frmsearch) and returned to FIRSTNAME and LASTNAME query fields as NULL or VALUE.
2) If on the search form FIRSTNAME has value and LASTNAME is NULL then query should lookup a record with valid FIRSTNAME and nothing in LASTNAME.
3) If on the search form LASTNAME has value and FIRSTNAME is NULL then query should lookup a record with valid LASTNAME and nothing in FIRSTNAME.
4) If on the search form FIRSTNAME and LASTNAME have value (NOT NULL) then lookup record with valid FIRSTNAME and LASTNAME.
5) If no information is entered on the search form then "enter some information" as message box.
6) I would want wildcard using which also doesn't seem to work in IIF statement. e.g. "*" & [Forms]![frmSearch]![FirstName] & "*"
THERE FORE ---> IIf(IsNull("*" & [Forms]![frmSearch]![FirstName] & "*"),"","*" & [Forms]![frmSearch]![FirstName] & "*")
ONLY NUMBERS ABOVE (2) (3) AND (6) DO NOT WORK, IF ANYONE COULD GIVE SOME LIGHT ON THIS I WOULD BE VERY GRATEFUL, AS I AM IN THE BLUE ON THIS.:rolleyes:
TAKE IT EASY:D
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
Not sure if this belonged in reports or queries, so I chose general. I have looked at several DCount threads but haven't quite found my answer. I want to use Dcount in an unbound textbox in a report. It counts the number of records in another table - comparison
the first part of the statement works fine ( up to 'iss'"). When i added the between date part i'm not getting any # returned in my report. I want to addthe criteria of RecDate between the 2 dates on the open form.
Can anyone tell me where my problem lies? ( if this makes sense)
Thanks
Kevin
=DCount("[type]","comparison","[type] Like 'iss*'" And ("[comparison].[RecDate]" Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2))
Hello all:
I need your urgent help with the following:
I am setting up a query in access. I have about 4 criteria for one of the fields called Center, I want the query to do the following:
Return all rows if:
Center is Like 6101*
Center is Like 61HKS56800
Ignore row if Center is Like 6101D*
Ignore row if Center is like 6101SALM01
I put in the following and I am not getting the correct results:
Like "6101*" Or Like "61HKS56800*" And Not Like "6101D*" And Not Like "6101SALM01"
Thanks,
Odun
Hi all,
I'm working on a purchasing app in access. At this point i'm working on the reporting module. I want a user to be able to fill out a start date (text box), end date (text box), and select a code from a list, hit Run Query, and have it pull up a report listing the date that the selected code was used, between the start and end date, and display other info as well.
The problem i'm having is that i can get the date ranges to work, or the code to work, but not both of them. Here's what i have in my query:
http://content.imagesocket.com/images/gl_codes2ff.JPG
I'm really lost here. Any advice would be appreciated.
Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.
EX:
SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down]
FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID
WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]! And [Forms]![frmDTGraphs]![end]))
GROUP BY tblDownTime.dtDate, tblLine.lineName
HAVING (tblLine.lineName)="[B]name of line");
criteria being name of line. Choices being line 1 or line 2
What I am looking for is one query to somehow generate the info on the two different lines in two different reports.
Is there a way or do I actually have to write the different reports?
I have a tutorial on how to do this, but they use an SQL code query. Is there anyway to do this by adding some type of criteria to an object query?
View 3 Replies View RelatedHi,
I have 3 fields that I need to run a query on. Date_Image, IMAGE_SYSTEM and DATE_TO_BR. If there is no entry in either fields, it should be part of the query. If entry is in both Date_Image and Image_system, I do not need those results in the query. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. I hope I was able to explain this clear enough. I was trying to do this with the IS null and Is Not Null expressions, but I am not getting the results I want.
I have a form that has seven unbound fields named txtCityCriteria1 through txtCityCriteria7
The idea is to have the use type in up to seven cities and have this applied to the criteria of the city field in a query. This query is the datasource for a report. I know how to do a single text ....
[Forms]![frm_OpenReport]![txtCityCriteria1]
in the criteria row would do the job. But how can I do seven?
Oh and if all are left blank i need it to open another form that informs them of their mistake.
I have looked through these forums and have found several possible solutions but I can not get any to work. Any assistance would be greatly appreciated. Take it easy on me I am relatively new to this code thing. Used to do everything in macros.
I'm sure this one has been asked time and time again!
I currently have a set of staff questionairres each on a different table.
Currently when I want to export all the scores for one employee to excel (eventually) I have a macro which runs 7 queries, but each asks for the same criteria (Date To & From) and (Staff Name) in turn - very time consuming!
How do I create a query or macro which will ask once for a date range and an employee name and apply to all of my queries?
I am a novice on access and am trying to do this without SQL
Thanks for your help
I have a button with this code attached
Dim SearchStr2 As String
SearchStr = "[PROTECHNIC_NUMBER] = " & "'" & Forms![FRMPATIENT]![Frm_ICP_Select].Form![Protechnic_Number] & "'" _
And "[ICP_Code]" = Forms![FRMPATIENT]![Frm_ICP_Select].Form![ICP_Code]
DoCmd.OpenForm "FRMASSESMENTHEAD", acNormal
Forms!FRMASSESMENTHEAD.Filter = SearchStr
Forms!FRMASSESMENTHEAD.FilterOn = True
If the button is clicked it should open the FRMASSESMENTHEAD form where the Protechnic Number (which is a text field) and the ICP Code(which is numeric) is the same as the ones on the current subform.
It works great if just using one of the criteria but when i put the and command in and then add the second criteria i get an erroer message saying
Run-time error '13':
Type mismatch
hope this makes sense and can anyone help :confused:
I have a series of text boxes on my form. The user makes various selections in order to submit a query to the database. The default value for these boxes is *
When the user clicks on the submit button, I only want the code to run for the button as long as not all of the boxes still have a * in them.
What direction should I head off in when it comes to coding this. basically, as long as at least one of the 5 boxes does not contain a star then run blah blah blah code, else, display message (I know this bit).
Regards
Hi,
I have a form on which I have about 6 combo-boxes and a set of 3 radio buttons.
I'm to the point that all the querys that fill these combo-boxes are in place.
What I want now is a search button that updates a datagrid under it when clicked. The query in this datagrid needs to be parameterised with the selected values in the comboboxes or radio buttons.
It should be possible to only select one combobox before pressing update.
At this point I placed a subform in the form to bind this query to. ( the datagrid I need).
Is a subform the correct object for this or are there other possibilities?
For some boxes only a line to the where clausule of the SQL statement needs to be added. For some other (one) and the radio buttons a join needs to be made with another table.
So I will have to build my sql statement dynamicaly in some vba code, run it when the search button is clicked and refresh the datagrid.
Does ab has a sample database in which such a search form is being used?
I must have downloaded like 10 sample DB's now but nothing I can use...
all help or advise greetly appreciated.
Hi,
This is probably an easy one but for some reason I'm not finding it in any of my reference sources.
I'm trying to set up a query that calculates tax my company owes the gov't, and the tax varies based on year to date totals. The 1st $30,000 is taxed at 4% and everything over $30,000 is taxed at 5%.
tblOrders has fields for date, CustomerID, OrderID and OrderTotal. Can the query have an expression field ("UpTo30") for ([OrderTotal] <=$30000) and also a 2nd expression ("Over30") for ([OrderTotal]>$30000)? And in the same query is it possible to also include the calculated fields for ([Upto30]*0.04) and ([Over30]*0.05)?
My main question is where to put the criteria - all I'm seeing talks about multiple criteria being State=NH AND/OR Name=R*, not multiple criteria on the same field. When I try WHERE statements I just get error msg no matter what the syntax is.
Thanks!
K
I have multiple queries pulling data from multiple data tables but are all common by a date that I have to enter in the criteria field for each query each time I need to change the date. Is there a way for the criteria field to read data (the date I want) from somewhere so I only have to change the date in one place and is read into each query where specified? I'm having to go into many queries to change the date criteria which I change to the same date for all queries - I'd like to do it once and have the queries read that date.
View 1 Replies View RelatedHi!
I have two related tables, one containing human resource information, the other containing a weekly record of hours worked by employees.
Here's the dilemma:
I need to keep a running total of hours available based on hours worked.
Employees begin each 7 day work week with 70 hours of time available. Any hours worked in that week are subtracted from the available hours for that employee. If there are two days in a row not worked during any given week, the 70 hours needs to automatically reset.
These hours are tracked in the Hours Available field (a calculated control which needs the Dsum expression) which should examine the Hours Worked field. Presently, the Hours Worked control gives a total of all hours worked for the employee shown on screen, but I need to examine the current work week to see whether or not an employee has worked more than 70 hours (it's a legal requirement.) If during any 7 day period counting backwards from the present date the employee had two consecutive days off, the employee becomes eligible to work 70 hours and the countdown starts again. In short, the total hours worked should be subtracted from 70 until 0 is achieved in any given week, unless the employee has had two days off (Hours Worked sum for two consecutive days =0.) I have attached a paired down version of the database for review.
Any ideas?
Tom