I have a table (tblrecords) with fields, entered by, recieved by and date and time,entered by and recieved by are both referenced in tblemployee.i want a query that will retrieve
fname, lname (of entered by from tblemployee), fname, lname (of recieved by from tbl employee) and the date and time from tblrecords)however i cant think how to do it with both names coming frm the same table?
I want a query selecting records from table1 based upon a value in table2 -
table1 contains thousands of records - fields include VendorNo and VendorName
table2 contains a single record - fields include VendorSearchName (which is a "short name")
I am trying to create a query which lists all VendorNo and VendorName entries where VendorName contains VendorSearchName
eg. VendorSearchName = "Fred" will list VendorNames including "Fred Flinstone", "Big Fred" etc
I use the following code to delete from a table all records except those meeting the WHERE criteria:
Code:
DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));
I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).
Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedI have created a database for storing, query and deleting data. Now the user wants to delete a row from a table and create two new rows. In my opinion this will need lots of work in order to replace all related queries forms and reports in the form. Is there any whay to do my job more easy in modifing a table and all related items be modified by themself? I still do not have any data in the table but if I did will I loose those data?
Thanks.
I have a table with associate names and rankings for each month. I need some way to find out which associates appear in 2 consecutive months with a certain rating. I have a query that will allow you to key in which month you want to look at, but I can't figure out how to write the query to return results only for those associates that appear twice in the months chosen. For example:
John Doe was rated 1.5 in May and 3 in June.
Jane Doe was rated a 1.5 in May and 1.5 in June.
I want a query that will allow me to put in May and June as the criteria as well as < 2 for the rating and only return those that show in both months...
I have 2 or 3 Queries which are run specifically export data to an Excel file required by external agencies.The queries contain column headings, required by the agencies, which are not derived from Table fields. Obviosly, no record data is produced for these columns.When the queries are run, the dreaded 'Enter Parameter Value' message appears for each to the additional column headings.
The following is a copy of the SQL Code for one such Query;
SELECT DISTINCTROW [Name1] AS Title, [Mail List].[First Name], [Mail List].Surname, [Mail List].[Address 1], [Mail List].PostCode, [Name2] AS [Aggregated Donations], [Name3] AS [Sponsored Event], [Mail List].SubDate, Sum([Mail List].[ Recd]) AS [SumOf Recd]
FROM [Mail List]
GROUP BY [Name1], [Mail List].[First Name], [Mail List].Surname, [Mail List].[Address 1], [Mail List].PostCode, [Name2], [Name3], [Mail List].SubDate, [Mail List].[Gift Aid]
HAVING ((([Mail List].SubDate)>#4/4/2014# And ([Mail List].SubDate)<#4/6/2015#) AND (([Mail List].[Gift Aid])=True));
Is there not some way in which these columns can be defined in Property Sheet such that this error message is prevented?
I am a beginner in access and I want to populate a new field (REPUN_1 which corresponds to SEGMT_ID in the other table) in my table and this field is coming from another table. The values of the fields need to correspond to the row of my actual table (JMTable) having the same CO_ID, MOVEPLANCD and TTY_NO as the table I want to extract the values from (DI_Treaty_Crosswalk).
Here is my query:
UPDATE JMTable SET [REPUN_1] = DI_Treaty_Crosswalk.SEGMT_ID WHERE JMTable.TTY_NO = DI_Treaty_Crosswalk.TTY_NO AND JMTable.CO_ID = DI_Treaty_Crosswalk.CO_ID AND JMTable.MOVEPLANCD = DI_Treaty_Crosswalk.MOVE_PLAN_CD
and it doesn't work since access ask me to enter a parameter value.
How can I include a field from a table in to a query - that has no relationship?
For example...
The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...
"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement". Here's the current SQL statement
Code:
SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];
I have a yes/no field as a book mark in a table with names. How can I query it to open to the last or the first check?
View 13 Replies View RelatedHi :confused:
I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields--
i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table)
e.g.Country CodeRegion Cost per minute
+1 United States, Canada, CaribbeanŁ0.20
+20Egypt Ł0.30
+212Morocco Ł0.30
How would i set up a query to solve this problem?#Can someone please help me???
I have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:
ClassID PreviousClassID
1 Null
2 1
3 2
4 Null
5 4
So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...
I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.
Thanks!
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things:
1. Displays the results of queries that sum data from a table (I think I have this figured) and
2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.)
Any help/advice that anyone could offer would be much appreciated!
Thanks!
Hi is there a query in access to return all of my table names.
to do what 'show tables' does in mysql
I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
Hi all,
I have a Make-Table Query that has five expressions. I have changed the properties of these columns so that, when you view the query, it shows the column names I have chosen. So, instead of Expr1, I get "Haggis", and instead of Expr2, I get "Cold Toast".
BUT, when this query creates a table, the column headings revert to Expr1 and Expr2. Is there any way to make my custom column headings stick in the new table?
I should add that I frequenty run this query and overwrite the table. Thus, even if I go into the table and change the field headings, as soon as it is overwritten these changes revert.
I've been developing an Access database to keep track of my company's ongoing projects. There's also a form to browse the history of users actions within the program. It's based on table tbHistory that stores actions and parameters as numbers (for example eventId = 1 is "logged in" and eventId = 2 is "logged out"). I've been using a query to translate those numbers to text with a syntax like:
Code:
IIf([tbEvents].[EventId]=1 Or [tbEvents].[EventId]=2;[tbEvents].[EventDesc];IIf([tbEvents].[EventId]=5 Or [tbEvents].[EventId]=6;...
It worked fine but eventually the expression within expression builder has grown to the level that exceeded allowed limit and I couldn't develop my statement any more.. I decided to develop a vba function that would take eventId as a parameter and would retrieve a string, here it is:
Code:
Public Function translateHistory(eventId As Long) As String
Select Case eventId
Case 1 To 2
translateHistory = ""[tbEvents].[EventDesc]""
Case 6
translateHistory = "[tbEvents].[EventDesc] & "" <b>"" & [tbFormDesc].[FormName] & ""</b>"""
[Code] ....
And in my query I replaced that extremely long expression with just translateHistory([tbHistory].[eventId]). It seems to work, but the result it brings is a pure string with table names and fields - in other words, the query doesn't recognize it should be replaced with appropriate value. Here's the output I get:
Of course I'd like "[tbEvents].[EventDesc]" to be replaced with appropriate value of field "EventDesc" from table "tbEvents" as it used to be.
I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.
Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?
:mad: :mad:
Hi folks,
I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.
Thanks.
I have a query which looks for like * surname*
in tblemployee fname lname dept active
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....
My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.
Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?
Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)
I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
I previously created 2 tables:
One lists all the stock symbols and company names = SYMBOL
The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
One to Many tables:
tbl_Doctors - Main Table : PK is doc_ID
tbl_Patients - Related Table : PK is pat_ID and 'related key' is docpat_ID
Dr. John has 3 Patients
Dr. Sam has 7 Patients
Dr. Andrew has "No Patients" IE. NULL
The (totals)Query:
Group by doc_ID (tbl_Doctors)
Count of pat_ID (tbl_Patients)
It works but ofcourse it does not include Dr. Andrew - as it shouldn't, because there is no related record in tbl_Patients.
What must the WHERE statement be to include Dr Andrews' number of Patients and display it as "0" (zero) ?
I have three tables: Event related on to many with Procedure Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.
I wish to have a query which outputs three columns:
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.
I have tried the Allen Browne module [URL] .... This gives me:
Event - Procedure1, Procedure2, etc using the expresion:
Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.
But I am struggling to get the final column! I have tried the following:
Expr2: ConcatRelated("[short description]","[tbl-procedure code]","[tbl-procedure code].[ProcedureCode]=""" & [Procedure Code] & """" And "[tbl-procedures].[Event number]=" & [Event no])
But this does not return what I want.