Date Related Query- Help Please!

Dec 1, 2006

Hi there,

I have a table which shows telemarketing that has been completed on a weekly basis where the caller places the date they called in one field and a followup date (if needed) into another field if a followup call needs to take place.

I want to create a query which will show me all calls that have got a followup date put inplace but the followup call has not been made.

E.g i call mum on 29/11/06 i put down followup on 30/11/06. I call on the 30/11/06 so the followup has been completed but need to follow up again on the 01/12/06 but i do not complete this one.

Any ideas???

Thanks

View Replies


ADVERTISEMENT

Need Help For Date Related Query

Jun 11, 2007

Dear all, i have the following codes:

enter_date = Format(txt_date, "dd/mm/yyyy")

Set db = CurrentDb()

Set rs = db.OpenRecordset("select * from [entered_date] where Date = # " & enter_date & " # ")
If Not rs.RecordCount = 0 Then
DoCmd.Beep
Msgbox "Selected Date's report already been created", vbInformation + vbOKOnly, "Error"
Exit Sub
End If

rs.Close
Set rs = Nothing


suppose,i have one record in the table [entered_date] which matches the user input - enter_date = "08/06/2007",but whne i run the above coding,i get the rs.recordcount = 0. it suppose to be rs.recordcount = 1.
can anybody pls help? thanks

View 3 Replies View Related

Date Related Query Across Tables

Jun 13, 2006

Hi, Ive got another query I need to do which I just dont know where to start from.
I have two tables, membersdata and bankdata. These tables are linked in a relationship thou the field 'description' in both tables.
What I want to do is be able to input a date and have the fields I require from both tables displayed. the dates are stored in the bankdetail table. I have made a query that allows me to input the dates in the bankdata table and that works but I need the membersdata to be shown on this query also.

How can I get the query to show up all the data i requre from both tables?

This query will give me the information of people that have paid us in the dates selected. I also need to revese trhe query so it shows the people who have not paid. so Im basically showing the records with a null value in the description field.

This ones totally caved in my brain this time of the morning! Any help would be fantastic!

Cheers
Phill

View 1 Replies View Related

Date Related Problem

May 31, 2005

I have a date related problem. I have an IF statement, that verifyes if a certain date entered in a table is <=Now(). The problem is, that it's vorking great just for curent year, curent month. For example: today is 31.05.2005 and if the value in my table is 13.06.2005 than for the <=Now() testing, it's telling me that 13.06.2005<31.05.2005. Why? Please help!!!!!!!!!!!!

Thanks,
Attila

View 4 Replies View Related

Default Values For Columns (Date Related)

Jul 27, 2007

Hi,

I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.

For for example
mm/dd/yyyy

CurrentDate - OWStartDate = TimeonOW
07/01/2007 - 05/01/2005 = 26

I played around with the DateDiff function but to no avail.
=DateDiff("d",Now(),[OWStartDate])
=DateDiff("d",Now(),#2004-31-12#) etc


The reason behind this is because I need to count how many people fall into different month span categories, maybe theres a better way?

Thanks in advance.

View 4 Replies View Related

Help Required Again Pls(date/time Field Related)

Apr 2, 2005

Hi i have a field which is a date/time field and its format is short date which is xx/xx/xxxx. I want to ask if there is any way i can add a validation rule for only the year to be larger than 1980???

View 2 Replies View Related

Date Field That Shows Only Related Dates

Jan 27, 2013

I have a table that lists classes available (tblCourses); one that lists class dates (tblDates) and another that lists employees (tblEmployees). I have a fourth table that combines that data into classes taken by the employee (tblTraining). I have a form to input the employee's training with combobox fields for the COURSETITLE and CLASSDATE. I want the CLASSDATE field to only display the dates available for the specific class that was selected. How do I do that?

View 1 Replies View Related

Append Queries To Related ID And Its Related IDs?

Aug 30, 2006

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!

View 14 Replies View Related

Show All Records From One Query And The Sum Of A Field In A Related Query Record

Mar 8, 2005

I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.

That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.

I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.

What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.

Can someone see where I’m going wrong?

Slaughter
slaughter at mizzou dot edu

View 9 Replies View Related

Query Related To A Table?

May 29, 2005

Hi :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???

View 1 Replies View Related

AND Query, Across Related Tables

Jan 12, 2007

Hello, I apologize in advance if there have been lots of questions like this, but the search won't let me use AND as a search term!

I have two related 1 to many tables. The parent table contains clients, and the child table contains categories, each client can have many categories. (i originally intended to have the categories be columns in the client table, in which case what i want to do is easy, however, then it becomes a nightmare when the user wants to add a new category hence the related situation described.)

Is there an easy way to find all the clients who have both category 7 and category 10? I can do it writing a query to find all the 7s, then another to find all the 10s, and a third to find those which have both. I am hoping there is an easier way, as i need to give the user a way to search via categories in whatever combination they fancy. The OR's i can do easily it's the AND's that are causing the problem.

The only idea i have at the moment is to make a temp table with the the clientid, and a long field holding each of the category ids, seperated by commas, and then searching using like "*7*" and like "*10*".

Does anyone have any better ideas, i'm hoping i'm missing something really obvious......

Thanks, Anna

View 1 Replies View Related

Calculating Value From Non-related Query And Table

Aug 30, 2007

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!

View 5 Replies View Related

Lookup Query For Related Tables

Sep 9, 2005

I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?

View 3 Replies View Related

Related Tables Query Question

Jan 24, 2007

i have 2 tables set up with a one-to-many relationship; one table has demographic information for patients, and the second has test results for each patient. is there any way to make a query that will give me the date of the last test result for each patient?

in other words, i want to query the MAX(date), but for each individual patient ID.

Thanks in advance.

View 1 Replies View Related

Display Related Query Results Together

Aug 7, 2007

I have been doing a lot of searching on the forums, and I can't seem to find an answer to my problem.

I have a form that uses a query to create an e-mail. This e-mail lists all of the different tests to perform on a sample of a product. My table set up is as follows:

tblProducts
ProductID
ProductName

tblTests
TestID
TestName

tblSamples
SampleID
ProductID
TestID

The purpose of this is to send out a sample of a specified product and perform 1 to XX tests on it.

Currently, my query is giving these results:

Product Test
Product1 Test1
Product1 Test2
Product2 Test1
Product2 Test2
Product3 Test1

I would like for it to format the data like this:

Product Tests
Product1 Test1, Test2
Product2 Test1, Test2
Product3 Test1

Any suggestions on what I should do?

Thanks in advance!

View 2 Replies View Related

Problem With A Query (Memo Related)

Oct 7, 2005


OK, I have the following tables:

Code:TableA Date Date/Time Time Date/Time Param1 Memo Param2 Memo Param3 MemoTableB OrderNo Number LineNumber Number PONumber Text

(There are many more fields than these in each table, but these are the relevant ones.)

For the TableA entries I am concerned with, Param1 = WorkOrderNo and Param2 = LineNumber. PONumber = '1234567-89'. I am trying to reach the minimum Date value where these conditions are true.

Code:SELECT Min([Date]) as Result FROM TableA WHERE (CStr(Param1) in (SELECT CStr(OrderNo) FROM TableB WHERE PONumber = '1234567-89')) AND (CStr(Param2) in (SELECT CStr(LineNumber) FROM TableB WHERE PONumber = '1234567-89'))

Causes Access to start trying to process the query, but it doesn't finish in a reasonable amount of time (I usually end task it after several minutes). The query needs to run at least somewhat efficiently. Not CStr()ing Param1 and Param2 causes an error because OrderNo/LineNumber are not Memo fields themselves.

Any ideas as to another approach I could take to get at the data I want, or a way to clean this query up to improve it's runtime?

View 3 Replies View Related

Queries :: Query Using Criteria From Non-related Table?

May 15, 2015

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

View 3 Replies View Related

Queries :: Include A Field Not Related Into A Query

Jul 10, 2015

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];

View 1 Replies View Related

Queries :: Query With 2 Names Related To Same Table

Aug 18, 2014

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?

View 9 Replies View Related

How Do I Update Data Related To A Read-only Query On A Form?

May 22, 2006

Hi all, I'm so tired of looking... please help me!!

I have an Access Data Access Page based on a query which is not updatable.

The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back.

(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)

I want the user to be able to update the Amount field, so e.g. if it is currently set to 50 to be able to change it to 40, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.

I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the form/DAP so that Amount shows the newly updated/inserted value.

I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!
(This is actually for a charity helping people suffering from emergencies in developing countries, not OfficeProducts, so your spot in heaven will be reserved if you can help ;) Thanks)

Jen

View 4 Replies View Related

Query Doesn't Affect Report... Related Problem

Nov 13, 2004

I've encountered a simple problem. =) I have a report based on a query. Of course due to the relations the query table should affect the report. But it does only with the filter parameters I put in the Query Design View, the quick ones ("filter by selection", etc.) that are in the result table view work there but doesn't affect the report. Still the query asks me to save it each time I apply any of these quick filters but make no changes at the end.

Any ideas how to make them work in the report? Or these filters are temporal and shouldn't work in that way? If so, what could I do to simplify customer's life?

(Access 2003, Access2000 based database)

View 2 Replies View Related

General :: Parameter Query / Report With Related Tables

Jun 13, 2013

I have a parameter query for looking up a specific lot number in my database. The lot number table has 4 related material tables that deal with material issued, reworked, and rejected for that lot number. They lot number table is in a one to many relationship with the related tables.

I have a report based on this parameter query with 4 subreports. I want the report to display the lot number data and sub reports to display the related data in the material tables (all linked by the lot number defined in the parameter)

View 1 Replies View Related

How To Split Up One Column With Multiple Rows Related To One Person Into A Query

Jul 1, 2013

I have an Access Database and I want to split up one column into multiple columns. The one column has multiple rows that relate to one person, so for instance I have the following:I am taking information from both tables and I want to create a query but I need to separate out the mother, father, and guardian with the names attached

So from the Child table :

Joe Smith Address Phone

The query that I created is from two tables, the child table and the family table: ( I used the dashes just for it to be easier to read)

------Child Table/PrimaryKey --------Family Table---------------------

First Name--- Last Name--- First Name--- Last Name--- Relationship
Joe ----------------Smith ----------Jane --------Doe-------------Mother
Joe ----------------Smith ----------John---------Smith---------- Father

So in this scenario I need to get the name of the second parent in another column....meaning have all the parents appear on the same line for this child, and each child there after..

So I need it to look like this:

------Child Table/PrimaryKey --------Family Table---------------------

First Name------ Last Name------ First Name------ Last Name------ Relationship------ 2First Name------2Last Name------2Relationship
Joe----------------Smith------------ Jane-------------- Doe------------- Mother-----------John---------------- Smith------------ Father

View 1 Replies View Related

Forms :: Search Query With Subform - Edit Records In Related Table

Nov 8, 2014

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?)

View 1 Replies View Related

Forms :: Link To Open Form With Certain Record Related To Individual In Query

Dec 5, 2013

I created a query that shows everyone who has a specific date field blank. Now want to make those names clickable so that it opens a specific form with a certain record related to that individual in the query. Each line with a different individual should open a different person on the form and their corresponding record.

View 3 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved