Newbie Help With A Complicated Query
Sep 26, 2006
First off let me say that I'm VERY new to Access. I've been put in charge of administering a large (to me) database and I've just started working with the insides of the database for about 3 weeks now.
The most urgent thing though is I need to make an "Honour Roll" report for the employees, that is, they get pass/fail inspections and I'm trying to create a query that returns all employees that have 10 or more passes IN A ROW, with at least 2 of those passes being a personal evaluation vs. an after the fact quality verification. There's already a query in the database that returns the employee ID, total passes, total fails. I've managed to add the field that includes type of inspection, but I can't seem to find a way to count the number of PE's in the field.
The second issue is getting the query to ignore all inspections prior to their last fail. I think I can work out moving the date of the inspection into the query, but then using it is another story. If it requires a script of some sort, then I'm hopelessly lost. The last programming I did was BASIC back in school... many years ago.
If I were to break it down into a statement, it would be something like this:
If (employee passes) >= 10 since last (employee fail) AND (inspection type="PE") >=2, then send (employee ID) and info to a report.
Any help with this is appreciated, I'm starting to get desperate!
View Replies
ADVERTISEMENT
Aug 4, 2005
I have a query that calculates the 'amount of credit' depending on the draw down date (which is when they started circulating the loan docs b/w two parties). Anyways,, the question is how can I make two columns of 'amount of credit' instead of one. Because one of the team member is dealing with overseas loan, while others are working on domestic. I want to make a column that would seperate his works from other people's work. And, I am kind of confused, how will I do it on a report. I have tried cross-tab query and also simple. Nothing is working.
One column should carry the amount he has to give as a loan and the other should calculate the amount from domestic side.
I don't want to use list box as it is unprofessional and also, its looking bad. If I create two queries, how can I join them together. I tried that too, but didn't work.
I need help badly :(
TIA
View 12 Replies
View Related
Sep 27, 2006
This is a little complicated so I am not sure t can be done!!!
I have a query that lists 40,000 records with some being sent to label printing
The query breaks the records down so that any record with a Qty greater then 17 will be sent to a second query for a different label to be attached.
I then end up with two Queries
Query 1 is =<17
Query 2 is >17
Records less then or equal to 17 are sent for standard label printing.
Here is my problem:
Any record with a Qty field reading less the 3 or 4 I want them to print 3 or 4 of the same label. For example
Jane doe (Qty 6) this will print 1 Jane Doe label
Joe Bloggs (Qty 3) this will print 3 Joe Bloggs labels
Am I trying to do something that just can’t be done!
When this is complete all information will be exported to a CSV file.
View 2 Replies
View Related
Mar 20, 2007
Hi
I am trying to query and summarise the contents of a table. I am unable to reach the results that I want. The table contains the following data:
AuditNo AuditTeam TypeOfTeam pName pAddress
234 CENTRAL SPEC TRUE FALSE
235 CENTRAL SPEC TRUE FALSE
236 WEST FSTL FALSE TRUE
237 EAST JUPT TRUE FALSE
238 EAST JUPT TRUE TRUE
I will like the results of the query to look something like this:
AuditTeam TypeOfTeam pName pAddress
TRUE FALSE TRUE FALSE
CENTRAL SPEC 2 0 0 2
WEST FSTL 0 1 1 0
EAST JUPT 2 0 1 1
In the results the columns pName and pAddress have subheaders of TRUE and FALSE. If this presents more problems then perhaps the subheaders can be removed and instead we will have pNameTrue and pNameFalse. Note there are 116 columns similar to pName and pAddress in the original table.
I don't know whether this is possible. It may require several queries to get to this. I am really stuck with this one so any tips will be very welcomed.
thanks.
View 3 Replies
View Related
Nov 15, 2006
I want to build a form based on a query, but I don't seem to be structuring it properly. Please refer to attached picture to help explain what I'm trying to do. I have 4 essential fields in a table, tblInpatientLog. They are POSno, Primary, POS Code, and Length of Stay (calculated as difference between Admit Date and Discharge Date).
What I need to do is have the form display the POSno that are in the table as "21" and "61" ONLY. There are others, but I only need to see these two. I then need to split the form (visually) into two sections. On the right, I need to display the Primary field as ones that are in the table as "Healthy Plan" only and on the left, I need to display Primary as ones that are in the table as "Medicare" only.
Under these headings, I need to show the total number of days, under the Length of Stay field for each of the four hospitals, Hospital 1, Hospital 2, Hospital 3 and Hospital 4, which are identified in the table under the "POS Code" field as "16", "2", "5", and any number OTHER THAN 16, 2, or 5 (or, whatever is left), respectively.
The Length of Stay field is calculated as difference between Admit Date and Discharge Date fields in the form for each individual record. I need access to show me a SUM of all the number of days for all the records that are listed as "Healthy Plan" for the "Primary" field for Hospital 1, then for Hospital 2, then for Hospital 3 and then for Hospital 4.
Then I need Access to calculate the SUM of all the 4 calculate field results for the 4 hospitals and display in the "Total Hospital Days" field on the form. Finally, I need a count of the total number of records that meet the criteria of "POSno" = 21 and 61, "Primary" = Healthy Plan, "POS Code" = all.
I hope that makes some sort of sense for someone. I've tried to create a query, but it doesn't display any results. I've wondered if I need to create more than one query, and have tried various different options without success. Any alternate suggestions would be welcome at this point. Thanks in advance for anyone out there that might be able to help.
View 14 Replies
View Related
Oct 5, 2005
I'm totally new to access so please bare with me, what I want to do is create a query that takes the price from one column and takes 25% off it if there is 2 units in stock, 30% off if there is 3 units, 40% if there is 4 units in stock and 50% if ther is more than 5 units in stock, the results will then be created in a column called DiscountPrice and the percentage off will will be created in a column called SaleInfo. Is this possible?
View 1 Replies
View Related
Apr 5, 2006
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
I hope that's clear - any help appreciated.
Cheers,
Mark
View 1 Replies
View Related
Apr 5, 2007
I have 3 tables that i wish to pull information from:-
TBLProspects
LeadID (Primary Key)
CompanyName
RenewalDate
Status
TBLNotes
NoteDate
Note
Who
LeadID
TBLQuotes
QuotePremium
Income
Reason
OnCoverPremium
LeadID
I have created the query and at the moment, I am getting duplicated rows of "Note" because there are more than one "QuotePremium" for each CompanyName. Is there anyway of showing the CompanyName, the Note (just once) and then the 2 or 3 QuotePremiums records?
SELECT DISTINCT TBLNotes.NoteDate, TBLProspects.CompanyName, TBLProspects.RenewalDate, TBLNotes.Note, TBLProspects.Status, TBLQuotes.QuotePremium, TBLQuotes.Income, TBLQuotes.Reason, TBLQuotes.OnCoverPrem
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.LeadID = TBLNotes.LeadID) INNER JOIN TBLQuotes ON TBLProspects.LeadID = TBLQuotes.LeadID;
View 2 Replies
View Related
Jan 3, 2008
Hello all!
I find it difficult to build a query...
The scenerario is the following:
I have a table that keeps information for weeks about some files (e.g: if the file has been sent). Then I have another table with information for days in a month (the data for every day).
What I should do is:
Check wheter the week has been sent, if it has been sent, I have to show the data for every day of that week in a report. Only for one month... and I can't figure out how to build that query...
I guess I should do something like:
select week from weekTable where week=week and sent=TRUE
set rcset=db.openrecordset(sql)
if(not rcset.eof)then
'build in here the query to select the data I need from the month table
end if
but i can't figure out how the loop would be for 1 month...
Any help will be much appreciated!
View 2 Replies
View Related
Jul 25, 2005
Well Super Complicated to me. I have setup two queries as follows,
Query 1
The function of the query is basically as follows.
1. If Field 1 is blank do nothing.
2. If Field 2 is blank then Add 7 days to Field 2.
3. If this falls over a weekend then Add another two days.
The expression I have added to do this is,
IIf(IsNull([Field1]),"", IIf(IsNull([Field2]),DateAdd("d",DateDiff("ww",[Field1],DateAdd("d",7,[Field1]),7)*2,DateAdd("d",7,[Field1]))))
Query 2
This query check whether the result of query 1 is a Weekend and if so adds another two days to it,
IIf(Weekday([Field2]= 1 Or Weekday([Field2])=7,DateAdd("d",2,[Field2]),[Field2])
My biggest problem is if I try and do any filters on this information then I get "Expression to complex" errors.
Am I over complicating things here?
Any ideas would be greatly appreciated.
JC3
View 1 Replies
View Related
Apr 17, 2014
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code:
SELECT [tblBalances].[BalanceDate] AS AsOfDate,
(SELECT COUNT([tblIssues].[IssueID])
FROM [tblIssues]
WHERE [tblIssues].[Flag] = True
AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code:
(SELECT TOP 1 [tblComments].[UpdatedWhen]
FROM [tblComments]
WHERE [tblComments].[IssueID] = [tblIssues].[IssueID]
AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate]
ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
View 2 Replies
View Related
Jul 26, 2005
I have been reading this is how I am able to articulate my problem with some degree of knowledge.
I have 3 tables as Follows:
Table 1. [ID]pk, [Contestant Name],[Event],[Category],[Class]
Table 2. [ID]pk, [Judge Name],[Event],[Category]
Table 3. [ID]pk, [Contestant Name],[Event],[Category],[Class],[Score1],[Score2],[Remarks]
Table 3 is a record of score from a specific Judge to a specific contestant. There will be a record for each contestant from each judge that is judging that specific [Event] & [Category]. therefore if there are two judges and two contestants there will 4 records entered into the table.
Problems 1. (I can't get my tables related properly)
I should not have to enter the duplicate information each time I enter a record into the Table3. The common information should be available within the other tables using queries.
Why i think I have problem 1:
There is a many to many relationship here on the parent form what i want is the [Judge Name], [Event], [Category].
On the related Sub form i want to see a query that shows me parts of tables 1,2,3 for each Contestant that is entered in the matching [Event],[Category] combined with the query of 1,2,3 and be able to enter this judges scores against the contestant Name and then when I change to a new judge I may see the same contestants but be able to enter a new score record against that contestant with a different judge.
In English
Assume you are a judge for a contest you have a form in front of you
Parent form Select your Event, Name, Category from a pull down box in the sub form you are presented with a list of Contestants that are on stage. You enter Score1 & Score2 for each Contestant and the record is created.
Judge #2 is doing the same thing
He selects his Event,Name, Category from the pull down menu on the Parent form and walla the subform populates with the same contestants as Judge#1 and he enters his Score1 & Score2 into the subform
and
What we have is 2 Judges, 5 Contestants and 10 Records created into Table3
Sorry for the length but the last part helps me visualize what I am trying to explain.
End result is I can't get it to work is this a job for a union query ?
View 2 Replies
View Related
Jun 6, 2006
:( anyone pls help...
here's my setup i have 3 tables w/ same field(example Field name: PC)
i need to add all the values to another table because i need to get the quantity..
example Table1: 5 Table2:6 Table3: 7
Table4: 5+6+7 (18 should be in Table4 w/ field name Quantity)
can email me at icetumbler03@yahoo.com tnxx...
really appreciate and need asap.
View 1 Replies
View Related
Aug 8, 2006
I have two tables
Table1 fields: Date, MoneyIn, MoneyOut, (plus other fields...)
Table2 fields: Date (some <> from Table1, other match), MoneyIn, MoneyOut, (plus other different fields...)
Question: Can I run a query that creates a list with
field1: all dates from Table1 AND Table2
field2: MoneyIn
field3: MoneyOut
Thanks
View 1 Replies
View Related
Jan 16, 2008
Hi,
I have two tables.
1. Customer (Id)
2. Transaction (Customer1Id, Customer2Id)
I want to delete all records in Transaction where Transaction.Customer1Id or Transaction.Customer2Id are not matched against the Customer.Id value.
I realise this query is very simple but I cannot get it to work in Access. Any help would be greatly appreciated!!
Thanks,
Ben.
View 1 Replies
View Related
Oct 25, 2004
I work for a nonprofit. Everyone has 10 jobs. I am the only person who uses/has used access. I have a donor database listing names addresses, and different fields for donations made/not made for each year. I do simple queries to pull names from the list that our board members would like to personalize a letter to, and the rest goes to a mailing service for addressing and stuffing of envelopes. This year, we would like to send a different letter to those who have donated in any of the last 3 years. I can pull contact info and the 3 fields (donted 2003, 2002, and 2001) together in a query, but not sure what to make the criteria. If I put >1 (since some of my fields automatically have a zero, and are therefore not null), then I only get those names that have donated all three years. Some may have donated two years ago, and not again, some may have donated only last year, or 2 out of 3 years, etc. I know there must be a way to do this, but can't get my head there having only had basic interaction with access. I know by process of elimination how many there should be, so I will know if it runs correctly. Hope this makes some sense. Bear in mind, I am a novice, and certainly not a software person.
View 3 Replies
View Related
Apr 5, 2006
Hi all,
Another quick Q - am a newbie at Access!
I am performing the following query:
.Open "SELECT DISTINCT([Date]), SUM([Batch Qty]) As [Batch] FROM [" & TableName & "] " & _
"WHERE [Date] BETWEEN #" & sDate & "# AND #" & eDate & "# " & _
"GROUP BY [Date], [Batch Qty]", cn, , , adCmdText
Data Produced:
29/03/200610
29/03/200620
29/03/2006100
30/03/200660
30/03/200680
30/03/20065
30/03/20065
How do I Sum the Totals for the Dates? So it produces:
29/03/2006130
30/03/2006150
TIA
View 1 Replies
View Related
May 17, 2006
OK, I have two tables that have pretty much the same data in them, but, the first table has SOME data that the second table doesn't and I need to get that data into the table that does not have it.
Here's a description of what I want to do:
Table #1 has about 10,000 lines of data with the employee SSN as the ID for the records. In this table are two extra columns of data (HRContact)and(HR ContactCode) that are not always populated in Table #2.
Table #2 has about 300,000 lines of data with the SSN as the ID field. Some of the records that match the SSN's from Table #1 have the data HRContact and HRContactCode, but not all of the records have those fields populated.
So, what I need to happen is for the query to go through Table #1, find the SSN of a record. As it finds each SSN, it goes to Table #2, finds that same record with the same SSN, then looks in the HRContact field to see if there is data there, or if it is Null. If there is data in that field, then it goes on to the next SSN in Table #1 and repeats the preceeding process. If the data in HRContact is Null in Table #2, then it goes back to Table #1 and grabs the HRContact and HRContactCode data for that record and writes it into the HRContact and HRContactCode field for the record in Table #2. the query would repeat this process until it reaches the end of file in Table #1.
I hope this is clear and if you have any questions, please ask me...
Thanks for your help,
Dave
View 1 Replies
View Related
Jul 28, 2006
I have an asset table, an equipment table, and a customer table. The asset and customer table has a field DateOut and equipment table has a field OnHand. I would like the OnHand table to decrement by one each time the DateOut is set to current, Date(). Tried Iif, tried expression builder, now completely lost. Any help would be great.
View 1 Replies
View Related
Oct 5, 2006
In the code of a button, ive got....
Which should be pulling the value in cb1 in the last cat query, but im getting an error saying it cant find the field "|" refered too...
Basically there is a table called Last Cat, and I want to draw the value of cb1 out of it where the catpathid = 5 so i made the query to do that... but now im abit lost....
Me.combo1.Value = [Last Cat Query1]![cb1]
any help would be awesome.
View 1 Replies
View Related
Jul 19, 2006
Hi gurusI'm looking for query to extract records from one table linked to another table based on different ranges. Now here goes.SELECT Invoice.dbInvoiceNum, Invoice.dbInvoiceDate, Invoice.dbFirstName+' '+Invoice.dbFamilyName) AS dbPatient, Invoice.dbdob, Invoice.dbServiceProvider, InvoiceItem.dbItemNum, Invoice.dbPayerName, Invoice.dbTotal, Invoice.dbTotal-Invoice.dbBalance AS dbPaid, InvoiceItem.dbServiceTax, TransactionLog.dbTransCodeFROM ((Invoice INNER JOIN InvoiceItem ON Invoice.dbInvoiceId = InvoiceItem.dbInvoiceId) INNER JOIN LEFT JOIN TransactionLog ON Invoice.dbInvoiceId = TransactionLog.dbInvoiceIdWHERE (dbInvoiceDate Between #4/1/2006# And #4/30/2006#) AND (TransactionLog.dbTransCode <> "CRDC") AND (Invoice.dbActualTotal > 0) AND (InvoiceItem.dbItemCode Between '57506' AND '58115') AND (InvoiceItem.dbItemCredit = False)ORDER BY Invoice.dbInvoiceDate, Invoice.dbInvoiceNum, TransactionLog.dbTransCode DESC;Now problem from above query is that I not only want invoices with items from the range(InvoiceItem.dbItemCode Between '57506' AND '58115')also want invoices with items in range of(InvoiceItem.dbItemCode Between '1' AND '5267') AND/OR (InvoiceItem.dbItemCode Between '57506' AND '58115')Invoices with items could either be just in range of '1' AND '5267' AND '57506' AND '58115'.I am not interested in invoices in range '1' AND '5267' without the range '57506' AND '58115'. For every invoice in the range of '57506' AND '58115' it will have a range '1' to '5267'.The above query just rips out the invoices with items in the range '57506' AND '58115' and showing duplicates and not the items also contained in range '1' to '5267'. Writing separate queries and extracting and looping takes far too long especially if total database dump was involved. It could take 20 mins to create report due to volume of records could be somewhat over 300K. I have hope and done before in one query that takes little time, however this seems little complicated.Any help would be much appreciated.
View 13 Replies
View Related
Apr 7, 2008
Hi, I hope that some one can help. I have column that ends with (1232) how can i extract only the 1232 for example,moved car to blablabla(1232) I want to create a new column and insert only what's in Parentheses. thanks in advance.
View 12 Replies
View Related
Mar 15, 2006
Hi,
I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.
PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC
What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.
So if say the cartridge code '12345' returns a list of printers such as;
printer 1
printer 2
printer 3
I want to take this data and turn it into a line like this;
printer 1, printer 2, printer 3
I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.
table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |
AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3
I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?
Thank you
View 4 Replies
View Related
Mar 14, 2006
Hi, i'm a newbie so please be gentle :)
ok, i have 2 tables, one is for part number and product ID and the other has product ID and Vehicle details.
i know how to set up a parameter query but just can't quite get the result i want, which is to set up a query based on the result of the first query.
in short i want to enter a part number and it comes up with the vehicle details.
Many thanks in advance
Ty
View 5 Replies
View Related
May 28, 2006
Hello, Really hope someone can help me.
I have 2 lots of info in 1 table - nameley customer address & posting address
I need to print a form that always has the posting address on the same side. but in some cases if the postal address is the same as the customer address then it is not filled in. hope this makes sense:
Please Im Really stuck
View 2 Replies
View Related
Aug 24, 2005
Hi all. I am fairly new to access and am trying to do something that shouldn't be that hard. I have a simple form with one text box and a command button on it. I have the command button set to run an update query.
My problem is that when I enter data and press the button, I don't know how to get the entered data into the query?
I know this is simple to do, but since I am new to access, I can't figure it out. Does anyone have a simple example or step by step details that they can share with me?
Thanks
Chris
View 2 Replies
View Related
