Queries :: Specifying Records Instead Of Fields
Aug 11, 2015
where each number corresponds to the primary key in another table, each number being a company name (not really relevant). What I want to be able to do is:
1. Produce a query table result with the Total Project Value (sum of all values) each company (305-9) is involved in. For example, in Project A and C, 308 are not involved, so the 308's Total Project value would be B+D+E+F Valuation.
2. Produce a query table result with the Average Project value. Similar to above, but taking the total and dividing it by the number of projects they were involved in. For example, 308 had a total of 259 over 4 projects, so an average of 64.75
3. Produce a query table result where it takes the most common role for each company ID. For example, Company 308 was Civil 3 times and Structural 1 time, giving 308 with Civil as their most common role.
View Replies
ADVERTISEMENT
Dec 18, 2013
It have several fields, and the fields that I want to filter is ItemNo and SellingPrice.The problem is ItemNo has many different SellingPrice. I put a parameter in SellingPrice field as >=75 only for ItemNo "49565", but still i want to show other record in ItemNo and do not have filter in SellingPrice field.
View 3 Replies
View Related
Jul 18, 2013
I have a table called tblAuthorizations. It appears as follows
AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102....... ...4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788...... ....4/22/13......7/21/13....0..............520
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520
I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.
What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.
I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
View 2 Replies
View Related
Nov 21, 2013
I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.
Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.
I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.
How I can keep all records but simply display info relating to work we did in the 2013 period?
View 8 Replies
View Related
Mar 17, 2014
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
View 2 Replies
View Related
Sep 5, 2014
I have the following source data:
Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
View 4 Replies
View Related
Aug 14, 2013
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
View 5 Replies
View Related
Sep 24, 2014
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
View 1 Replies
View Related
Aug 29, 2013
I want query to find duplicate records, i have two field in one table
Cusip and category
cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used
like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A
result should be
CusipCategory
123R
123R
123A
View 6 Replies
View Related
Jan 27, 2014
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
View 1 Replies
View Related
Aug 6, 2015
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
View 2 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Mar 23, 2015
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
View 5 Replies
View Related
Feb 5, 2014
I would like to populate form fields with records from a table/query so that they are bound to the recordsource and the record to be displayed can be selected from a combobox. I am hoping that in this way, any changes made are propagated to the original table.
I have created a combobox from which the primary key can be selected and added all the fields from the table appearing on this form as values.
In my Change event for the combobox I have this code:
Private Sub cboID_Change()
Me.RecordSource = "SELECT b01_Participants.*FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(1) & "));"
Me.Refresh
End Sub
However, I receive an 3075 error when this code runs which seems to relate to my syntax or an extra ")".
View 14 Replies
View Related
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
Jun 15, 2013
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
View 1 Replies
View Related
May 29, 2014
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
View 5 Replies
View Related
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
Jun 25, 2015
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
View 1 Replies
View Related
Nov 1, 2005
Access user,
:confused:
I am looking how to generate a relation in one database between records of the same field type.
f.i. you have a database where you try to keep track of your parts.
You order a part1 but they deliver an identical part but from another brand so it has another part number.
You will store it under his correct partnumber being part2.
Due to the fact you still have stock of part1 and just recieved part2, now you will run out of stock when part1 + part2 reach zero stock.
In order to be able to monitor this you must be able to generate a relation between part1 and part2 showing that that parts are fully interchangeble.
How can you realise this in access?? Or is this impossible?
Also would it be possible to have for a certain part number multiple relations?
f.i. parts are fully interchangeble
restricted interchangeable
one way interchangeable
Thanks in advance
View 1 Replies
View Related
Jul 14, 2015
I have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.
In my head it should work something like this
1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)
2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.
Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?
View 3 Replies
View Related
Dec 16, 2014
I have a sign up form where people select their name for the time slot they want, there are 2 spots open per time slot, so I have Interviewer A and Interviewer B. Records can then either just be a name in A or a name in A and B.
I would like to run a report that shows how many times all the interviewers have signed up in a date range.
I can do this individually by looking up one name at a time with this query:
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interviewer_B)=[Forms]![Main]![NavigationSubform]![cboSup])) OR (((Interview_Schedule.Interviewer_A)=[Forms]![Main]![NavigationSubform]![cboSup]) AND ((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]));
My question is how do I get a report to show all agents at once with their own interview count?
I can make 2 separate queries to look up each field, for interviewer A and Interviewer B, then run a join query, but this results in 2 counts for each agent and I can't sum the two totals together....
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted, Interview_Schedule.Interviewer_A
FROM Interview_Schedule
[Code] ....
View 7 Replies
View Related
Mar 27, 2006
Not sure if this is a Table question, a General question or what exactly. I'm working with a lot of records (millions of them). Assuming each record/field contains identical data, I'm wondering which would result in a larger overall DB size:
- 10 million records, 4 fields each
- 30 million records, 2 fields each
What I have is a table something like this:
Record No./Attrib1/Attrib2/Attrib3 (~10 million records).
I could make it Record No./Attrib, which would have roughly 3 times the number of records. I say roughly because not all Attrib have values (currently zero filled but would be eliminated).
I'm hitting the maximum size for an MS 97 database (1 GB), so I'm looking to do whatever possible to stay small.
Any input/suggestions would be appreciated.
Thanks!
View 1 Replies
View Related
Jul 13, 2005
We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records.
The other system stores the whole note in one record.
We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record).
I have a table with the notes from the first sytem which looks something like
Note1 - Line1 - Text
Note1 - Line2 - Text
Note2 - Line1 - Text
Note3 - Line1 - Text
Note3 - Line2 - Text
Note3 - Line3 - Text
What I want to do is, for each note, combine the separate lines into one record.
I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?
View 8 Replies
View Related
May 30, 2007
i'm relatively advanced Access user but this problem has got me stumped....i've been given a table that's basically a log of operating rooms with fields designating the date, the room number, "patient in room" time and "patient out of room" time. basically, i want to calculate the turnaround time which is the "patient in room" time" minus the "patient out of room" time from the previous record (if records are sorted in sequential order).
any thoughts?
View 10 Replies
View Related
Mar 11, 2008
Another problem has thrust its ugly head into my personal universe.
I have a report to make that is to show a break down of employees hired in each month since Oct. '05 and to show what percentage of the total for that month remain.
I have created one query that grabs each of the relevant personnel;
SELECT Right(Str(Year([dbo_personnel]![originalapptdate])),4)+" -"+Str(Month([dbo_personnel]![originalapptdate]))+" : "+MonthName(Month([dbo_personnel]![originalapptdate]),True)+" '"+Right(Str(Year([dbo_personnel]![originalapptdate])),2)+" Hires" AS hire_month, [dbo_personnel]![lname]+"; "+[dbo_personnel]![fname]+IIf(IsNull([dbo_personnel]![mname]),""," "+Left([dbo_personnel]![mname],1)+".") AS name, dbo_personnel.badge, dbo_personnel.originalapptdate, IIf(IsNull([dbo_personnel]![dateoftermination]),[dbo_personnel]![assignedorg],"EOS'd") AS EOS
FROM dbo_personnel
WHERE ((Not ((dbo_personnel.badge) Like "R*" Or (dbo_personnel.badge) Like "9*")) AND ((dbo_personnel.originalapptdate)>=#10/1/2005#))
ORDER BY dbo_personnel.originalapptdate;
I've saved this query as "2-yr Service Check Baseline."
And then this query that does the count;
SELECT [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") AS EOS, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS
FROM [2-yr Service Check Baseline]
GROUP BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active")
ORDER BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active");
The output from the second query stores the Active count on odd numbered records and the terminated ones on the even numbered records.
So, to get the percent left, I'd need to sum the two count fields from the two relevant records and then divide this into the Active count.
But, in the report, how do I reference a field from a record other than the current one?
Thanx in advance for any help!
View 3 Replies
View Related
