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
I have a list of coded transactions in column A, Amounts in B and Dates in C.
I want to show subtotal for all amounts with the same codes in D
I can do this simply without the date parameters, but I need to give timeframes.
When I add the [From] and [Until] questions it then shows the amounts next to each date even though the date column is hidden.
I'm aiming for the subtotal of amounts for same codes between given dates:
Example
ABB 100 1/6/11
ABB 100 6/6/11
ABB 150 1/8/11
ABC 50 1/7/11
ABR 50 2/8/11
ABJ 50 1/5/11
If I enter 1/6/11 and 30/6/11 I would expect ABB to show 200 total as 150 is after that date and so not counted.
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies View RelatedI have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies View RelatedI 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
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.
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.
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.
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 RelatedFirst 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!
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;
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!
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
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.
Not sure if the question belongs in queries or reports. I've a list of product codes that we produce daily, weekly, monthly. Some repeat several times within a week or month. I want to list them in order by cost. I can do that, but when they repeat, I wanted to combine them into one subtotal, then get sorted by cost with the other product codes.
Right now, I'm dumping the table into Excel and doing it manually. I sometimes have 7 lines of the same product code. I insert a line and subtotal them by their product code. Then, I delete the data leaving the subtotal line. I then can sort them with the other subtotal and other once product codes.
:-[ I'm hoping this is an easy answer. I'm not good with Access. It's turned out to more of a trial and error thing for me.
Thanks
Laura
Hi,
I'm trying to produce a subtotal in a query to compare it to another field to determine whether to include the record or not.
For example, in my table I'd have,
ProductA 3(units) Lot1
ProductA 4(units) Lot2
ProductB 2(units) Lot3
ProductA would also have a minimum inventory level of 6 in another table. So the parameter I would like to create would sum all the different lots of ProductA and compare it to the MinLevel entry.
Any ideas?
I have a report that has the following categories:
Parent--Unit--Month--Spend--Expected--Rate of Expected
Now i have it summing the spend by Parent. I would like it to sum by Parent by month, is this at all possible?
I am creating a Sales History Report. It displays products sold by customer by Sales Rep. The groupings are Sales Rep, Customer, and Product. I am hiding the detail section which contains invoice line item records. So the report shows the product subtotal by Company. The problem I have is I need to see the product subtotals in the Sales Rep and Report Total sections also. So the report would look something like this:
Sales Rep A
Customer A
Product A
Product B
Product C
Customer B
Product A
Product B
Product C
Sales Rep A Total
Product A
Product B
Product C
Sales Rep B
Customer R
Product A
Product B
Product C
Customer S
Product A
Product B
Product C
Sales Rep B Total
Product A
Product B
Product C
Report Total
Product A
Product B
Product C
Is there a way to accomplish this without doing sub-reports?
I am looking to do some calculations in a form that can be output to a report.
Basically within the form i have 4 controls as well as the others
Tables
Benches
Chairs
Other Equipment
These controls are bound to a table.
The first 3 controls need to have have fixed costs 2.00, 1.50 and 1.00 respectively. The last needs to be manual input as it can be different dependant on the items in the memo box.
I then need each one to calculate separately to give me a sub total then add all together to give a total
This total needs output to a report for sending out a bill.
I currently have the 4 controls with 4 unbound textboxes beside them for the subtotals:
TablesTotal
BenchesTotal
ChairsTotal
OtherTotal
4 unbound textboxes for the fixed amount and manual input amount (maybe this is where i'm going wrong)
TableCost
BenchCost
ChairCost
OtherCost
and then one more unbound textbox for the total:
TotalTotal
Do i need to create a table for the fixed costs? If so whats the best way to set this up. What should i be binding textboxes too if anything?
Hope this makes sense.
Thanks in advance
Hi,
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers]
but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
Thanks very much!
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 RelatedHi, 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 RelatedHi, I have a couple questions about a project I am trying to work on right now.
I am very very new to Access but am learning quickly. I just wanted to run this by everyone and see if any of you had any ideas.
The goal of my database is to track patients from our clinic. I have it set up as follows:
1 table containing basic patient info: Med record #, name, date of birth, age, gender, family history of cancer, etc etc etc.
1 table listing all the possible diagnosis of cancer that we treat in our clinic
1 table listing all the procedures that are performed by our surgeon
--------
Here is the issue. I can enter all the patient info with NO problem. All my lists populate nicely and I have dropdown boxes to make it easy to choose items, etc. I cannot, however, figure out exactly what I should do if a patient has multiple surgeries, multiple diagnosis, and even multiple testing types done on tissues from each of those surgeries.
How on earth can I set up a form so that I can enter all the basic patient info. Then enter a procedure and a diagnosis - then enter a second procedure and a second diagnosis (though the diagnosis would most likely be exactly the same as the first procedure). I don't want to have 3 records for each patient if we perform 3 procedures during one surgery visit. If the patient comes BACK for another surgery, that is a different matter.
Sorry for the long post but I appreciate ANY help anyone can provide.
Hi ,
say I have a table:
video: id, title, artistname, countryid
that has the values
1, 'VideoA','Artsit123' ,4
2, 'VideoB', 'Artsit567' , 5
3, 'VideoC', 'Artsit167' ,6
4, 'VideoD', 'Artsit890' , 7
5, 'VideoE', 'Artsit468' , 8
6, 'VideoF', 'Artsit752' , 9
And another table country: id
that has the values:
1,4,5
Basically is there a query that can be written for me to check if ALL the countryids from the country table will return an artist, and if not can it let me know. For example obviously there is no countryid with 1 in the video table so Im not interested in that batch in the country table. I know I can just execute the query 3 times to see if a result is produced but what if the country table is massive (btw the country table is generated dynamically using xml). Any help on this would be very mch appreciated
(Hope this is in the right place)
Hiya Guys,
I am not new to Access, but have only before created basic databases with simple structures and I am having problems with my latest project.
The idea is to have two tables, “tblStudents” and “tblClasses”. I also want to have a form called “frmClasses” and this is proving a bit of a problem. I am trying to display the class data for each class record on the form (Time, date, details), but below that want a table containing all of the students which are registered to that class, which at the moment is just causing me to go round and round in endless circles.
Sometimes, the form just won’t load and after I change it, it will just put in duplicate records containing the same class data but a separate record for every student in that class.
I have a relationship set up in the database which links the field “ID” in students to the field “student_ID” in Classes. But I think that the problem may be there. I can post a copy of the database if you want.
Any help appreciated,
Chris Farrants
im am making a database to keep track of the soldiers in my company. right now i have come across a problem and am having troubles with entering the phone numbers. i have three tables for this purpose;
soldier(SoldierID, SSN, LastName, FirstName.....)
phone(PhoneID, Number, Type (cell or home))
soldier_phone(SoldierID, PhoneID)
in my main soldier form which uses the soldier tbl for its data i embeded a form to enter in phone numbers, which doesnt work. long story short, i can only get the phone table to be filled out, but the soldier_phone table never gets any values so the phone number never gets linked to the soldier.
any help would be great, thanx.