Hi! My question is similar to Hondasteed earlier today but a little different. I have a database that has 6 date fields. I would like to develop a query where the user enters a start and end date and the query checks all six of those fields for the appropriate dates and shows only those records who have dates in that range. The dates would change each month as we ran the query again. I would appreciate any help as this has really had me stuck and I'm guessing it's a simple solution. Thanks!
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name Start Date Midpoint Date Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1) Table: Default as only one table Total: Expression Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate] Table: Default as only one table Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM! Show: Checked or Unchecked makes no difference Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate] FROM tblCommsLog WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID Employee Name Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7 ....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
Is it possible to select 1 MAX date from multiple date fields for a record?
For example, on an employee record there are 10 date fields, each for a performance review date. Some employees may have date fields 1-3 with values, some with just 1, others 1-5, etc.....depending on how many performance reviews they've had.
Is there a way to pull the MAX review date for an employee, knowing that the MAX date could reside in Field 1 for a certain employee, and could be from Field 7 for another?
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
I have a table where I use 3 date fields in, all with different views.
Field 1: Date as dd/mm/yyyy Field 2: Quarter: q (shows the same date, but then as quarter of the year. Field 3: Year: YYYY
Now I have a form that fills all of my fields, but is there a simple way () to populate the other two fields Quarter and Year from the value I entered in Date? So if I change Field "Date", Fields "Quarter" and "Year" updates as well.
Maybe I don't need to do it in the form, but in the table?
I'm not that good with VB- scripts, and I'm not sure If it is possible to auto populate the table field Quarter and Year directly in the table itself.
I'm trying to create a query that has a calculated field that shows a maximum value from multiple fields.
As far as I can find, this is not built into Access, so I've used this code from a Microsoft page:
Code: Function Maximum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable currentVal equal to the array of values.
[code]....
The problem I'm having is, well this doesn't work for me.I'm a bit of an beginner VBA coder, but I understand this code and don't know why it won't work.It only displays the value from the field within the brackets.The values used are Dates, so I need to display the latest date from multiple fields.
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
What I have: A table with multiple Date/Time columns. Here's an example:
What I would like: A month-by-month summary counting the number of entries in each of the three columns, like this:
I'm indifferent as to what happens with the months zeros across all three fields (whether they show up in the report with zeros or are omitted altogether).
Smaller Pieces: I am able to create such a summary for one column at a time:
Not what I'm looking for: I can not figure out how to create a query that does this for all three columns. My best attempt was a disaster:
I have attached the example file for your convenience: Database1.accdb
To clarify, my issue isn't the format of the date. I know about the Month(...) function. My question is regarding how to count this data and split it into a month-by-month summary (see the picture above in the "What I would like:" section)
I am terrible with databases, but have designed a database that uses two text field types and 48 Yes/No check boxes. The two text fields describe documents (name and location), and all the check boxes indicate what types of documents they are.
What I need to do is to be able to run queries or reports where I can select CERTAIN of the Yes/No field types. For example, some of those check box fields are named 1A, 1B, 1C, 2A, 2B, 3A, 3B, 3C, 3D, 3E, 3F, 3G, (up through 8E) etc.
How can I get the query or report to bring up all of the records that have a check mark in either 1A, 1B, "or" 1C plus list the Field 1 and Field 2 that are text fields? I don't want it to list every record in the database, which is what is happening, because I want to then be able to sort the results.
I know it USED to be in my head how to do this, but I just can't seem to get it to work!
My task is to compare our master membership table with a second table, removing matching names in the second table from the master table. (The second table are those individuals who chose not to receive a paper newsletter, so we want to save money and trees :) and not send them one.)
So to remove those names I tried the find unmatched query wizard but that only allows me to chose on one field it seems. There is no single unique identified field that would do this for both tables.
I thought if I built an unmatched query using the fields 'Last_Name' and 'Address1' and 'PostalCode' that should be sufficient for the task.
Here are portions the individual SQL statements that I need to get into one query.
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON tblAllMembers.Last_Name = qrySeekbyGroup.[Last Name] WHERE (((qrySeekbyGroup.[Last Name]) Is Null));
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Address1] = [qrySeekbyGroup].[Mailing Address Line 1] WHERE ([qrySeekbyGroup].[Mailing Address Line 1] Is Null);
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Postal_Code] = [qrySeekbyGroup].[Postal Code] WHERE ([qrySeekbyGroup].[Postal Code] Is Null);
How is the SQL written so the find unmatched query will use all three of these sections in on query.
I've imported an Excel file into a table and now I've created a Query from it. I now need to Add Fields (names are not in the table) and calculate totals for these renamed fields some of the answers are going to be the result of two or three fields. Any help would be greatly appreciated. thanks
Quick questions and I'm sure this is easy enough to warrant a quick response:
I have a query. I have 12 different fields that may or may not have the value "Sabbatical". I want to view only the records that have the value "Sabbatical" in AT LEAST one of those 12 fields.
I am new to MS Access. I am creating a small database for motor vehicle clearance. I have a SEARCH form where i want to put the fields for searching query table:
VEHICLE MAKE:
REF No.:
BEGIN DATE: END DATE:
I need it in such a way that even when only one of the above fields is supplied with data it should still return correct data from the query table.
For dates (BEGIN DATE, END DATE) i tried: Between [forms]![search]![begin date] and [forms]![search]![end date]
This worked well when placed alone of the SEARCH form. But when i added more fields (VEHICLE MAKE, REF No.) the query returned nothing but a blank query table.
Im trying to do the following query work. I have a form, with combos , text fields which are filled in with parameters. The query I want to make is complex. For example we shall use field1, field2, combo1 , combo2 for explanation. I want the field1 param AND the field2 AND combo1 AND combo2 to be evaluated for a result. At the same time, I want the user to fill in only field1 and combo1 or like that and the result to be right. Is there any possibility to make them all work at the same time, individually, or in combinations ?
Sounds too Complicated ?
I ve managed to make it work only by filling in individually the fields or combos and have the right results. The other ways didnt.
Firstly, I'm an ex-fulltime access developer who has found himself doing access work again 6 years later; I can't actually believe how much I've forgotten :s
Anyway, I've done a search and havn't found anything that can help me so wondered if anyone could give some advice.
I have an access form with 3 combo boxes and a checkbox next to each of them. these are accessed using a query with 3 iif statements in it stating; (iif checkbox is null, "*", combo_box_value). the whole query looks like this
SELECT Customers.* FROM Customers WHERE (((Customers.Partner)=IIf(Forms!frm_rpt_main!check _partner Is Null,"*",Forms!frm_rpt_main!partner)) AND ((Customers.[Type Business])=IIf(Forms!frm_rpt_main!check_type Is Null,"*",Forms!frm_rpt_main!type)) AND ((Customers.[Year End Month])=IIf(Forms!frm_rpt_main!check_month Is Null,"*",Forms!frm_rpt_main!month)));
However, the query only seems to want to pull data from all 3 combo boxes
My issue is that if a checkbox is not ticked, I want the values to be ignored for all 3 combo's.
I've attached a copy of the mdb file as my description probably doesnt make any sense, the specific query is "qry_select_month_partner_type_wname_frm_rpt"
Hello all. I'm having a problem running a query that compares one fields to 5 others. The code is below. I'm sure it's not correct because the results don't look like they should. I have these fields:
Account Number Transaction Type Code Bal unapplied code 1 Bal Unapplied code 2 Bal Unapplied code 3 Bal Unapplied code 4 Bal Unapplied code 5 field_result
I wrote the code to add "UF" before comparing any of the "Bal Unapplied code" fields to the "Transaction Type Code". I am trying to first see if the code in "Transaction Type Code" is located in "Bal Unapplied Code 1." If it finds it, then I want to put "Found" in the "field_result" field. If it doesn't find it, then move onto the next field and so forth until it goes through all 5 fields. If it can't find a match in any of the 5 bal fields, then I want to populate the "field result" with "Not Found"
If someone could help I'd be forever in your debt!
Here is my crazy code:
Iff("UF"+[unapplied balance_update].[bal Unapplied Code 1]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 2]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 3]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 4]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found",iff("UF"+[unapplied balance_update].[bal Unapplied Code 5]=[Unapplied balance_update].[Transaction Type Code],[Unapplied balance_update].[field_result]="Found","Not Found”)))))
Hey guys- I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')- xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this- Field1 Field2 Field3 xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow... Thanks!
Access2007- Building a query to handle future input...
I run an update query that does what it is supposed to do and gives the proper values.
THEN I add a one more field to the source with the builder using + that has no data in it yet, the query fails and gives blanks, even though one of the fields in the source has data in it.
I would think that the "no data" field would be considered 0.00 value and be added together to the field that has values giving at least the value of the field with values as a result of the query.
Is there a setting somewhere that I need to change so that the no value and the value would be combined? I am using the builder to combine the values in the query.
I have already made a query with all the information needed. What I am trying to do is create a form where a user can open the database and enter a unique number and have information from 10 fields associated with that number show.
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID
tblLocations LocationID 1 , 2 Location New York , Chicago
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
