Is it possible to have a query that uses criteria to pull data from a specific table?
For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].
The tables are just text, but the query would be too long if it was used.
Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?
View 5 Replies View RelatedI want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
This works for the records with fields that have data.
Code:
Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
Code:
"" Or Is Null
I am developing a database for incoming inspection. I have set up two tables, one with spec callouts per product and the other table contains fields where I want the specs from the tblSpec table to be Append into the tblMeasurement table. Now, within the tblMeasurement table (where I am trying to append specs from the tblSpecs table into), I also have additional fields for actual measurements from received goods.
My plan is as I go through and select the Vendor, it populates the associated products to that vendor (no problem there). When I want to select the product, I would like the specs from the tblSpecs to dumped into the tblmeasurement table that correlates to that product on the form.
How can I have this automatically append after selecting the product? I am not sure if I have my relationships set up correctly or not, but I cannot see to append when I am trying to execute this function while in Query view.
I have the db attached in a zipped format.
Query4 is the result from left joining of Query3 and Query1 and I would like to delete some of the null data in query 4
while I execute the following code
DELETE DISTINCTROW Query4.*, [working hour] AS Expr1
FROM Query4
WHERE (((query4.[working hour])=0));
I get the warning of "Could not delete from specific table"
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile
Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20
Area2FloorPrep (tblFloorPrep)
Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
I have 9 seperate tables - each of the tables has similar headers
Claim #
Agent
Pass/Fail
Request type
Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
I have a table that contains the following:
Code BrandName Lead Free Nickel Free
001 AAAA Yes Yes
002 AAAA Yes No
On a form the user selects the code field and in this table the field is called BRAND. On the same form, I need to display the value in the Nickel Free field so if selects 001, the field on the form needs to be "Yes", if the user selects "002", the value needs to be "No"
Hi,
Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear.
Thanks upfront for any suggestions!
BJS
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code:
DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
Hello Everyone,
I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out.
Thanks so much for any assistance!
Amy (monet1369);)
v sorry for the basic question, but ive been banging away at access and i cant my head around this..
i need to run a simple query. the query/ search will ask users to enter in the number of a document. i want, when this code is entered, for 2 controls on the form be updated with codes that are stored in a table based on the code they searched for originally.
heres my table structure...
Table A
Doc ID (PK)
Doc No (manually input and is the search item that users enter)
Table B
Unique ID (PK)
Doc No (FK)
Info (to populate field 1)
Info (to populate field 2)
how do i perform this in the query section? do i need to manually code the SQL required, or is this query basic enough that i can just select the fields required in the design view of the query?
thanks guys
I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.
as i already have data in my DB that i need i cannot just reset any fields
having a table with just one field - the numeric part of the RO number so first one would be 129036 - so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database
I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.
i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.
I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.
I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.
Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.
How do I do this?
How to sort a field of data in a table that contains the letter S that is always in a specific place in the sequence? For Example:
13123S0-BAX
13124E1-ARZ
13122X2-THX
12134S0-GAB
I have 3 tables invoice, customer and items. I need to create a query that will get the next invoice number from the invoice table and mark that invoice number in the items table for a set customer where the invoice field in the items table is blank.
The invoice table is now set up as a autonumber, therefore I need to append to get the last new number.
The items in the item table are unique and this is why I want to mark the invoice number in the item table field invoice.
I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.
My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.
Is it possible to write a query from this table that will enable this?
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
Code:
Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
'Update value list with user input.
On Error GoTo ErrHandler
Dim bytUpdate As Byte
[Code] ....
It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.
Code:
strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
& "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
objRs.Open strSQL, objConn, adLockReadOnly
I have built a nice database that has a form to enter data which logs in product received, there is a combo box on the same form that is linked through the query builder to auto populate the names from the contacts info table (the receivers of the product received) the contacts info table also contains information that is specific to each name such as locations.
As of now I have created a command button that brings me to the form that shows the information fields I need specific to a name, once I get that I have another command button to bring me back to the main form. How to create an additional list box on the main data entry form so as when the name is entered the new list box or text box (which is best?) will auto populate the information I need on one form instead of going back and forth.
Example:
Requester Name [ auto populate name ] currently linked to contacts info table (working)
(New field) Preferred Location [ need to auto populate location ] from the contacts info table (how do you pull locations specific to a contact name from the same table?
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View Related