Sum Fields In Query
Apr 12, 2006
Basically i want to sum up the total of fields with a currency value in them from a query! how do i do this
e.g. I run a parameter query at present which shows me all mortgage applications by lender for year 2003 (I enter the lenders name) and the loan of each application.
I want it to sum up the value of all loans from this lender. e.g
CaseIDLenderNamePRODUCTDateRecievedLoan required
3058Freedom 820 02/02/2003 £160,000.00
3120Freedom 820 17/03/2003 £80,000.00
3130Freedom 828 22/03/2003 £135,000.00
3153Freedom 838 31/03/2003 £210,000.00
How do i get it to create a new field or basically give me the total of this loan???
View Replies
ADVERTISEMENT
Apr 30, 2013
Any way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???
View 5 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Aug 22, 2006
I have this query:
SELECT A.*, AppointmentCost - Nz((SELECT SUM(PaymentAmount) FROM tblPayment P WHERE A.AppointmentId = P.AppointmentId),0) AS AppBalance, Date()-AppointmentDate AS DaysOutstanding
FROM tblAppointment AS A;
When I run it shows:
AppointmentID
PaymentID
AppointmentCost
AppBalance
DaysOutStanding
I would like the first and last names of patients to show up in this query
I have a table called tblPatient which has these two fields in it.
Colud you please show how to do this, if it is even possible?
View 1 Replies
View Related
Jan 24, 2008
There are 2 types of paths a project can follow: ELC and SDLC. In my database, the user must choose which path they are following for their project.
Once they choose a path, they must then fill in one of the two appropriate grid with dates.
The two grids have around 40 fields each.
I am trying to write a query that looks at the response entered for Path and then looks to see if the user entered dates in the wrong grid. (If they choose ELC, they shouldn't have values in the SDLC grid).
For example: iif(Path="ELC" and SDLCDate1<>Null or SDLCDate2<>Null or ...,SDLCDate40<>Null,"Red",Null)
But typing SDLCDate1...SDLCDate40 <> Null is time consuming, messy and Access probably won't allow such a big query.
Is there any workaround?
Thanks,
View 3 Replies
View Related
May 10, 2007
I need to know if there is a way to change the actual field that is displayed in a query through criteria. There is a DB table at work that has been set up very poorly. The field names are months Jan - Dec. I need to change the field in the query every month based on whatever month I am running the report for. It sucks and I need to find a way for the query to automatically select the proper month field.
Is this possible?
View 3 Replies
View Related
Nov 10, 2005
My company has a microsoft access table with about 1200 IDs containing names, addresses etc.
Now I want to print this out in an excel sheet because I want to create address stickers, but I want every cell in the excell sheet to add information about the columns in the database together. I might be very unclear, let me demonstrate what I mean:
The Acess file might look like this:
id | name | address
1 | john | Street 01
2 | sara | Street 02
Now I want to have 2 excell cells which contain the information like this:
name_address
name_address
In my example, 2 cells with the following information:
john Street 01
sara Street 02
So I dont want 4 cells, I want to add the information together from 1 Id to 1 cell, but I also want to add linebreaks to it, It must eventually become an address sticker
How can this be done?
View 3 Replies
View Related
Feb 7, 2007
I'm trying to build a form based on a query to use for data editing/entry purposes.
The only way I can think of to do this is to use a query to get data that is particular to my specified customer.
The first problem I have run into is that one query does not contain enough fields to retreive all the fields in my table.
I figure, perhaps I have made a flaw in my design and shouldn't go further with this until I verify it to be true. I don't think I've made a flaw, but the fact that a query has limited maximum number of fields leads me to think I might have.
Each record in the table I'm querying contains several pieces of numerical data that might be recorded during a service visit.
Obviously I could query the table twice but that doesn't feel like the right way to go about it.
View 3 Replies
View Related
May 18, 2005
I have four fields in a Table. They are all four date fields. I want to do a query and see information in all four fields, but I only want to see the information if it is before a certain date.
So far I have tried this:
<Date().
This works but it shows all four fields even if it is after this date. How can I suppress the fields to appear blank if the date is not before today's date?
View 2 Replies
View Related
Sep 29, 2005
Can someone tell me what the maximum amount of fields that you can have in a query is please?
View 1 Replies
View Related
Oct 28, 2005
Hello, I have a table (Projects) with original projects and amended projects. All information regarding each project is inputted in the fields for original projects. Only limited fields are inputted in amended projects;new loan amounts or a new closing date may arise infrequently for amended projects. When there is a new closing date for an amended project, and it becomes part of a query all other fields are blank for that record, How do I populate the original project data into the blank fields of an amended project. I frequently have to create queries based on the fiscal year of closingdate field and I need to know the commitmentdate (field) and other dates that are in the original projects records. I hope this is not confusing thanks in advance. :confused:
View 1 Replies
View Related
Dec 7, 2005
My boss asked me to do something today that stumped me. He wanted me to make a query, where when you're selecting the parameters you could chose whether or not you wanted certain fields to be visible.
On top of that I need to be able to chose whether I want it to be shown on screen in a query, printed as a report, or exported to excel. This is what makes it tricky. I could easily do a bunch of If...then statements to generate the SQL query and only show the fields necessary, however the report and the query view both have the fields on them in design view. If I cut the fields out of the query at runtime, then I get the nasty "Input parameter" popup box.
So I've been bashing my head off the keyboard trying to figure out how I'm going to do this. One other theory I tried to put into use was having them left on the form/report, but if the checkboxes were not selected then it would set them to visible = false at runtime. Sounds good in theory, however Access decided to ignore this block of code and just show the fields regardless of what I told it to do.
So if anyone could provide me with some insight or suggestions, I would certainly appreciate it.
Thanks in advance!
View 1 Replies
View Related
Feb 15, 2006
Hi!
I'm very new to access, but eager to learn.
I have created two tables. When I try to create a query using the query wizard, or when I try to create a report also using the wizard I don't see any available fields. I click on the table, but I see no available fields.
However, when I go to design view, I'm able to see all the fields.
Does anybody know a sloution to this problem.
Nille
View 14 Replies
View Related
Mar 30, 2006
I have a database that I run a few queries on to do some simple calculations for me using IIf statements. I did not need these fields in the datasheet view so I unclicked the Show button and everything looked great. Now when I go back into my query, those feilds are gone, how can I re-show them to make some changes? Thank you for any help.
Jimmy
View 4 Replies
View Related
Sep 15, 2006
I've made a form with list boxes on, the user's selections from which are taken as parameters for a query, created by VBA code.
I want to add a final list box to the form from which the user can select which of the available fields they want to see in the query output (i.e. has the same effect as checking/unchecking the "Show" checkbox for each field on the standard query design grid).
I have written the following code to try and do this, but it isn't working. I'm very new to VBA and wonder if anyone can help me out by showing me what's wrong?
'Build Field List
If Me!lstFieldList.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstFieldList.ItemsSelected
strFieldList = strFieldList & "[" & Me!lstFieldList.ItemData(varItem) & "], "
Next varItem
strFieldList = Left(strCriteria, Len(strCriteria) - 2)
Else
strFieldList = "'*'"
End If
'Create Query String
strSQL = "SELECT Centres." & strFieldList & " " & FROM Centres " & _
"Where " & strCriteria & _
" And " & strCriteriaCtr & strSortOrder & ";"
Running the above gives an error, and the section under "'Create Query String" is highlighted in debug mode so I guess the error must lie there? Either that, or the earlier part where strFieldList is being create is generating something which strSQL cannot interpret.
Many thanks in advance if you can help me.
Gary
View 12 Replies
View Related
Nov 13, 2006
Hello Everyone,
I need help with this unmatching query. I'm trying to unmatch records from two tables, but base on two key fields. I have one table call storage where I accumulate data and the key fields are [Action Date] and [ID]. On my other table "New Data" I also have many records and the two key fields are also [Action Date] and [ID]. I need to get records from the New Data table where the same Action Date and ID are not in the Storage table. I don't one to get duplicated records with same ID and action date. Can anyone help me with this?
thanks,
Pablo
View 1 Replies
View Related
Dec 27, 2006
I am trying to create a query that capture all the possible choice of a fields and do some math on them. let me make it clearer.
I have two fields that i need to create a query from forecast type(backup, hardcore) and win percent(76 to 100, 50 to 75, 26 to 49 and 0 to 25).
Forecast type is a drop down that has the following choice hardcore, backup or could be null. Win percent is just a number fields holds value between 0 and 100. One of the criteria for the range of number is when either backup or hardcore is null.i need them to line up as rows or columns so they either look like this
row view
Hardcore
Backup
76 - 100
50 - 75
26 - 49
0 - 25
column view
[Hardcore] [Backup] [76-100] [50-75] [26-49] [0-25]
I am currently using six queries to get the values for each group then joining them to get the final results. the problem with this is the values wont come out correctly. I know there is an easier solution to what i am doing right now.
I need some guidance and help to approach this issue. This will be use in a report so hopefully this provide more alternative to solve this problem.
thanks in advance
View 1 Replies
View Related
Jan 10, 2007
I have two table Client_details and Release_details in a database,were client field is the primary keyin both the tabels.I want to update records of Airtel-India in Release_details,for that i have wrriten a query
UPDATE Client_detail, Release_detail SET Client_detail.Client = "Airtel-India", Release_detail.Client = "Airtel-India"
WHERE (((Client_detail.Client)="Bharati Chennai") AND ((Release_detail.Client)="Bharati Chennai"));
but after running an error message is shown like
Microsoft access didnt update 0 fileds due to a type conversion failure,16 records due to key violations,0 records due to validation rule violations
:confused:
View 1 Replies
View Related
Feb 5, 2007
Hey guys,
I'm in need of some help again. I have two tables, Table1 and Table2. I have one field in each table that I want to have the same data. I don't want blanks to be merged, but I want all the data to be the same
for example
Table1
---------
1
3
5
Table2
---------
1
2
4
6
I would like both tables to be merged to show
Table1
-------
1
2
3
4
5
6
Table2
------
1
2
3
4
5
6
But I want to just update both tables and not create anything new.
Sorry if this is unclear
View 2 Replies
View Related
Mar 17, 2007
Is it possible to create a query which selects selects fields in a table, not just data in a field? If so, how?
TIA
View 1 Replies
View Related
Jun 18, 2007
I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?
Any help would be greatly appreciated.
View 14 Replies
View Related
Jul 17, 2007
I have 2 fields, Accounts, Accounts CMO. I need those two fields to show in the same table in a new field called Total Accounts. I am summing them, but getting irregular results, some are right, and some are 4 times more than they should be? How would you go about doing this, maybe I am missing something.
View 1 Replies
View Related
Nov 28, 2007
Could anyone tell me if there's a hardcoded maximum number of fields/columns in a query?
I ask, because through some experimentation and the urging of others on the boards, I've finally taken a look at the SQL view of a database and found it rather easy to understand. There, I found I had more flexibility filtering a Query via SQL in comparison to the query design window. (unlimited "Or" vs. 8).
So, even though there's a definite number of columns in the Query Design window, can I ignore than and just enter what I need in the SQL view?
View 6 Replies
View Related
Feb 11, 2008
Hi,
I have a table that has 3 columns.
Issue, Review, and Information.
These 3 fields get their data from the same lookup table that holds some code values.
So I'm trying to create a query to export to excel that shows the data for this table, but for the lookup fields, I just get the ID's from the lookup table.
So the table is like this now
ID...Issue...Information....Review
1.......2..........5...............1
2.......1..........12..............6
So I would like to be able to display what the ID numbers actually are, but having trouble getting a query to do this for all 3 fields.
I can run a query that shows 1 field's but not all 3.
what I would like to see
ID.....Issue...Information....Review
1.......2a.........4c.............e5
2.......1a.........3c.............2F
all 3 columns use code_ID from the lookup table.
Is there a way to do this? or should I have used 3 different lookup tables for these?
View 13 Replies
View Related
May 1, 2008
I have a one-to-many query with two tables. I'm using the query as a record source for a subform.
The subform displays the correct data I'm after however I can't update any fields on the it due to the underlying query having a one to many relationship. I understand that and thats fine.
Problem is I need users to be able to update one field on this subform as required. Is there a way to allow this while keeping the underlying query the same?
View 1 Replies
View Related
