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
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???
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.
: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;
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?
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.
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.
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
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.
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?
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:
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.
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.
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.
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
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.
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?
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
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.
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:
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.
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?
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.
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?
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.
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?
