Over 1 million tech questions and answers.

Calculated field in a query based on a query

Q: Calculated field in a query based on a query

Hi there,

Once again I need your help in Access. I'm trying to do a calculated field in a query that based on a query. For example, query B is based on query A. I want to add a calculated field called Totals in query B. The field totals does not exist in any of my tables.

Here is an example.

Totals: [Field1]+[Field2]+[Field3]

In the totals line I clicked on Expression. But the totals do not appear when I view my query. I get no errors on the formula. What at I doing wrong??

Mario

RELEVANCY SCORE 200
Preferred Solution: Calculated field in a query based on a query

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Calculated field in a query based on a query

Read other 7 answers
RELEVANCY SCORE 147.2

I have a table with a field called "current fund balance" which is intended to be a data entry field. I have a query with multiple calculated fields. Both the table and query share a common field. I'd like to have a datasheet form to where data from the query and table are combined and have that field "current fund balance" be a data entry field. I'm having the hardest time figuring this out.

I've read a form/subform would work but I need to be able to see all the records at once. And so, datasheet with all fields would be best. Anyone have any thoughts?
 

A:Solved: Having a form field in Access to enter data based on a query with calculated

Read other 16 answers
RELEVANCY SCORE 123.6

I have a query that has event date (Event_Start) where I use total min and max to get the first event (First Event: Event_Start with Min selected in the total row) and last event (Last Event: Event_Start with Max selected in the total row). When I use another column to calculate the # of Days between these 2 expressions (Days Between: [First Event]-[Last Event]), I get a pop up boxes that ask for First Event and Last Event. I can just hit enter on both and still get the calculation in the "Days Between" field in the query. How do I get rid of the pop up boxes when I run the query.
 

A:Solved: Calculated Field in Query getting user input box

Read other 8 answers
RELEVANCY SCORE 118.8

NOTE: I could not post code because I must be really careful to protect proprietary information.

I work in an assembly plant and I've designed an Access 2003 program to deliver a set of 4 crucial parts to the floor all within a very narrow degree of tolerance. Further, the program integrates FIFO.

Ive done this by assigning each of the essential measurements codes and then running a series of queries:

Query 1 identifies the oldest Part A by sorting the available parts by the date they were inspected then by serial number (to break ties) and delivering the TOP 1.
Query 2 identifies the oldest Part B that is compatible with that Part A using the same method.
Query 3 identifies the oldest Part C that is compatible with that Part B.
Query 4 identifies the oldest Part D that is compatible with the other end of Part A. I have the parts in this order because we can begin assembly if we don't have a Part D on hand, but we must have Parts A, B, and C.
Finally, query 5 simply pulls the information from those queries into one location for a report.
As long as we have enough stock, life is good; but the moment a match cannot be made, it blows up. I cannot figure out how to make it go back to choose the next Part A. I need to figure out how and where to add code to each query to tell it that if no stock is compatible with the Part A produced by Query 1, to go back and pick the next one in line.

Can anyone help? Is the way I've gone about matching parts completely... Read more

A:Access 2003 – Query Based on Query – need code to rerun earlier query

Read other 11 answers
RELEVANCY SCORE 111.6

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

A:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

Read other 16 answers
RELEVANCY SCORE 98.8

hi there,

I have a large Access database and some of the queries are based on other queries (sql or other).

Now when I try and view the design mode of some of these queries, it takes an astonishingly long time as it appears that Access runs the child query contained before it allows the 'parent' query to show in design mode.

I can kind of see why it does it - but is there any other way of quickly viewing a query in design mode without Access first running the other queries it's based upon?

(NB: Someone said in the 'old days'(!) Access used to have this feature, but it is no longer there?!?!?!)

Thanks
 

A:Trying to look at design of query based on another query...

Bounce in case of new eyes to the forum!
 

Read other 1 answers
RELEVANCY SCORE 92

Hi there,

I'm very new to access and I hope I will get the simplest solution on my question below.

I have a table named "PurchaseOrder" and I'd want to create a query that will display the invoices that will due.

Eg:

Table1
PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
8697 D 888 $88 18-Oct-2010

I want to create 2 queries.

The first query will display all the invoices that will due 5 days before the exact due date.
Eg:
The invoice due date is 15-Oct-2010. The query will display information on 3241, 6589, and 2234.

For the second query, I want to display all the invoices that have exceeded the due date. The information on the respective invoices will be transfered from the query as mentioned earlier to the new query.

The scenario is as below:

Date: 16-Oct-2010

Query1

PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
8697 D 888 $88 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
Date: 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
8697 D 888 $88 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
3241 A 421 $12 17-Oct-2010
Hope someone can help me with this. Really appreciate and hoping for ur help.

 

A:Query Access 2007 (how to transfer data from a query to another query)

The first query needs the following first Criteria Row of the InvoiceDueDate
between date() and date() + 5
The second query needs
< date()

I haven't tested it, just worked from memory. so let me know how it goes.
 

Read other 2 answers
RELEVANCY SCORE 86

Hi, not specifically a Windows 8 question but just a Google.com one..

Why is it that whenever I start typing a search query in the Google box at Google.com, it starts typing into the URL bar rather than the text box in the middle of the screen? This happens whether I use Chrome or Firefox..with Chrome it goes into the URL bar, and with Firefox it goes into a dynamic search box top left.

Just wondering if this is normal? I find it a bit annoying...but maybe thats how things are now?

A:Why does my Google query appear in URL field?

Hey mate.

Not sure what you mean entirely? you are able to type queries into the address bar, and it will use your search provider (often google) just like if you were to go to google.com and type it inside the webpage. Works both ways, and this way you don't have to waste time searching for google first.

Read other 6 answers
RELEVANCY SCORE 86

I've encountered one of the strangest things I've ever seen in Access. Wrote a query that supports a mail-merge letter. One of the fields in the query is an expression named "Expires". The formula is Month([DateJoined]) & "-" & Day([DateJoined]) & "-" & Year(Date())+1. What it produces (example) is 4-11-2003... on MY computer. But on (an old) computer in a client's office where it needs to be used, the field is always truncated at 6 characters; i.e., the above date shows only as "4-11-2". If the day is between 1 and 9, it would show "4-9-20".

I cannot find a setting or configuration for Access or Windows that controls the length of text expressions, and no other expressions behave this way. It is unaffected by changes in the Windows Regional Settings.

Why is this one field truncating only on one computer? Where's the setting to keep it from doing this?
 

A:Query field truncates

Read other 11 answers
RELEVANCY SCORE 86

what is that supposed to mean?
im trying to make an append query but this message comes up
 

A:query must have at least one destination field?

Read other 6 answers
RELEVANCY SCORE 84.8

i am creating a DB for all the software installed on PC's i have a table that if a certain software is installed i would enter the version number, and if it is not installed then i would just leave it blank. How can i create a query that will disregard all the null fields (meaning that software is not installed on that specific pc) and jsut show the records that have a version number in it. I am almost positive there is a setting for this, i just can't find it, don't think you have to put some kind of code behind this to create the query. Any help would be much appreciated.
 

A:Solved: null field in query

Read other 14 answers
RELEVANCY SCORE 84.8

Can someone help please?

I'm setting up a query in Excel from an SQL database. How do I (or what do I) do to ensure only todays data is shown - I suppose I'm looking for an"=TODAY" type thing but don't know enough to be able to do it.

Thanks in anticipation.

Moll )
 

A:Excel query on Date field

it can depend on how the date system is set up but the following page has pretty much every SQL date function going

http://www.connx.com/products/connx/Connx%208.5%20UserGuide/sql_date.htm
 

Read other 1 answers
RELEVANCY SCORE 84.8

Long time no see, took a bit of a break... time sure fly by huh?

My Query's Y/N collum result in -1/0 when I run the query. I want it to be Yes/No.
Is there a ... better/quicker way to do this other than using IIF() function in the sql?

I use this field in a combobox on a form, interesting thing is some of the combo's display Yes/No whilst some display it as -1/0

Thx
 

A:Solved: Access Query Y/N Field

Read other 7 answers
RELEVANCY SCORE 84.8

Please help. I'm running Access 2007. I have a Form that's running off a query. The query has a parameter that requests two user inputs. This works fine except when I open my Form it automatically asks for those two inputs. It still works but I'd like to be able to open the form, enter data and when I get to those fields have the parameter questions populate at that time. Any help is greatly appreciated
 

A:Query Parameter Field in a Form

Read other 7 answers
RELEVANCY SCORE 84.8

I want to do a query linking 2 tables. In one I will know exactly what text is in the requisite field. I then want to query the other table to find the field with a name that matches the text in the first table, so I can edit the value it contains. Can I do this?
 

A:Access--query searching for field name??

Read other 12 answers
RELEVANCY SCORE 84

Greetings

It's been a long time since I last posted here, but hopefully all you wonderful people will be able to help...

Here is my problem:

A customer of mine has a SQL database where one of the fields is a date/time field that looks like this:

13/02/2003 14:32:58
14/02/2003 08:59:10
15/02/2003 17:09:37

Now, I need to delete all the records for a particular day (One of their HD's failed, and they had to restore the data and lost a day, but the entries are still in the database, and can not be re-entered until the old entries are deleted).

How do I structure a query to delete all the records for a particular day? Preferably, I would like a SELECT statement first, just to check that the records found correspond to the delete criteria, and then the DELETE statement.

Thanks

Reuel Miller

The Ex-NT moderator on this board

Every morning is the dawn of a new error
 

A:SQL Query help required with date/time field

Greetings

Not to worry chaps, I figured out the answer all by my lonesome

the statements I used were:

select * from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

delete from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'
cheers

Reuel Miller
 

Read other 1 answers
RELEVANCY SCORE 84

I am using a query in Access to clean up some unneeded text from a table, then create a new table with the cleaned up text. In addition, I need to add a column to this new table that is boolean format defaulting to a "no" value. I have successfully created this query except for the new boolean column. I can get the new column to default to a value, either no or 0, but it is in text format. If anyone knows how to do this I would appreciate the help.
 

A:Access-Adding a field to a table with a query

This query will do it:

ALTER TABLE TableName ADD COLUMN NewColName bit
 

Read other 3 answers
RELEVANCY SCORE 84

Hi all,

Right I have a table, which is the following:
ID - Autonumber
ID2 - Number
ID3 - Number
Duplicate? - Yes/No
Name - Text

Sample Data:
Code:

ID ID2 ID3 Dup? Name
1 1 1 - Alan Smith
2 2 2 - Bob Smith
3 3 3 - Bob Smith

Data I want:

Code:

ID ID2 ID3 Dup? Name
1 1 1 N Alan Smith
2 2 2 Y Bob Smith
3 3 3 Y Bob Smith

You can ignore the ID's - they are not all the same as in the sample data, but they are irrelevant for what I need.

As you will see from the data above, "Alan Smith" is a unique name, and therefore Duplicate? is set to NO; whereas "Bob Smith" has two occurrences, so the Duplicate? is set to YES.

I know I need an append to go through each line to check if it is a duplicate. Any thoughts on how I can go about this???
 

A:Query to tick a yes/no box if a field contains a duplicate value [Access2010]

Read other 15 answers
RELEVANCY SCORE 84

First off I am new to this forum and somewhat new to access2010. We are trying to do a query basedon two tables. The tables are joined bya field on both sides named jobno. In tableone we are using a field totalcost. Intable two we are using a field named totalcost. Totalcost in table one has 6 records, totalcost in table two has 9 records. When werun the query we get 54 records. We havetried all possible join selection with the same results. Thanks in advance for any help.
 

A:query based on 2 tables

paulbol, welcome to the Forum.

There are also 2 other properties that you can try, they are in the Query's properties (click below the Criteria rows to see them)
One is "Unique Values" and the other is "Unique Records".
 

Read other 1 answers
RELEVANCY SCORE 83.2

Hello,

I have a problem with an access database report I am working on. how do I get the memo field of the report to show more than 255 characters? The data is there in the table but when I try to run the report, it cuts off or truncates the data after 255 characters. I went into the query and "ungrouped" the fields which worked to display all the data in the table, but now the query report won't display it all.... any ideas on how to remedy this?

---- Business Analyst

Read other answers
RELEVANCY SCORE 83.2

I am using Windows XP and Access 2003. I have a community database with a field that has street number, street name, unit number to make it a unique field. There are times I want to sort this field by street name in alphabetical order. Is there a way to write the criteria in the query for that field so that it ignores or strips out the street number? If so, in the case of striping out the street number, can I put the same field in the same query so that it will show up in a report, but keep all the streets with the same name together? I know I do not have to include the field without street numbers in the report.

I know I probably should have kept the street number separate from the street name, but I am too far down the road to change that at this point.

I would really appreciate the help. I do not write code, but use all the onscreen Access functions.

Thanks,
OceanAndy
 

A:Access 2003 Query - remove part of field

Read other 15 answers
RELEVANCY SCORE 83.2

Hi guys, just wondering if you could give me a little advice. i have a report that is generated from a query and in the fields for example bank account number i need to put in a "," before it. As well as that i have a label that displays the date, i need to be able to do the same for this as well, for example ",2010-10-21". Any guidance would be greatly appreciated, thanks for your time.

,5015105015 ,2010-10-21
,6464561564 ,2010-09-14
,5464151555 ,2010-09-13
 

A:Solved: putting a comma in front of my query field

Which application is it?
 

Read other 1 answers
RELEVANCY SCORE 83.2

I have a field [name] that can have up to 6 words within the field ex |hello my name is Miranda|
i am trying to get a query to split into different fields by the space between the words to look like:

|Hello| |MY| |NAME| |IS| |MIRANDA|

Any Ideas i would really appreciate it!?
 

A:Solved: Access: spliting a field into multiple through a query

Read other 11 answers
RELEVANCY SCORE 82.8

is there a way to write sql query based on two sets of criteria ?

for example , my table is as follows :

Pkey --- column1 -- column2
1--A -- 1
2--A -- 2
3.--A--3
4--B--1
5--B--2
6--B--3

Now I want a query to retrieve A,1 and B,2 , that means rows 1 and 5. So here my criteria is in two differnt sets. If column1 is A, then I need column2 =1 , and if column1 is B, then I need column2=2. Likewise, my sets can include a lot of values like (A,1) , (B,2) , (B,3) etc..

Is there a way to handle this in a simple query, or do I need to go for a procedure ?
Thanks
Rajendra
 

A:sql query based on two sets of criteria

You should be able to handle it with ordinary select queries.
You can have the Criteria on different rows or you can use the "Or" with one Row.
Can you post an example Zipped database?
 

Read other 1 answers
RELEVANCY SCORE 82.8

Hello! I am now trying to set up a "Keyword Search". I created a report from that query (qryKeyword) and a form (frmKeyword) that contains only an unbound textbox (Keyword) and a command button (with no command associated yet). I then created a query using the main table and put the "OR" criteria as

Like "*" & [Forms]![frmKeyword]![Keyword] & "*"

And, surprise, surprise, it's not working!!

What I want to be able to do, is open up the frmKeyword from a main switchboard (that much I can do!! ), click a "Search" button once the criteria is entered that will automatically requery qryKeyword and show the rptKeyword with the correct information. I would also like the keyword to be shown at the top of the report. I feel so needy!!
 

A:Run Report Based on Keyword Query

Ka, sorry I missed this when you posted it, I nearly missed it today as well.

Do you still need help on this?

I have posted a couple of "Search" databases that do what you want.

I am not sure what you mean by the "OR" criteria, but the "Like "*" & [Forms]![frmKeyword]![Keyword] & "*" should work Ok.
 

Read other 2 answers
RELEVANCY SCORE 82.8

i have a form based on a query so that entering an ID number in a field on the form will bring up other details of that record,

however, when i type a ID number not associated with any record the form still accepts the value but changes it to the next highest number not currently being used as an ID number and obviously no details are shown in the fields(because the record hasnt been saved)

how can i make sure that this doesnt happen and that when an ID number is entered it should only be accepted if it has related records
 

A:query based form confusing me

Read other 8 answers
RELEVANCY SCORE 82.8

I have a form that has 4 combo boxes (Department, SiteLocation, Account, VendorName). The values in these boxes are based on a single table (tblInvoices). The tblInvoices has drop-down list for the same fields that are based on smaller tables that hold the value list for each of the fields. I would like to create a query that has the above listed fields along with other fields from the tblInvoices, so that when a report is run it will show the results based on the user selections on the form. I named my combo boxes Filter1, Filter2, Filter3 and Filter4 on their property sheet and then in the query grid criteria for the above fields I typed in Forms!frmInvoice![cboDepartment] for each field changing the last part to match the field name. I even tried changing the last part to match the textbox name of Filter1 and neither of these worked. I get all types of errors that I don't understand. I am a beginner (1 year) with Access and very little exposure to VBA. Can someone help me please?
 

A:Query Parameters Based on Combo Box

Read other 12 answers
RELEVANCY SCORE 82.4

I created an update query and when I run it I get the following message "An INSERT INTO query cannot contain a multi-valued field." I believe that problem is that I have two look up values in the Append to table. How do I solve this problem? How do I use an Assigned.To statement?
 

A:An INSERT INTO query cannot contain a multi-value field error message Append Qry

Is this is SQL? Anyway, please copy and paste the query
 

Read other 2 answers
RELEVANCY SCORE 82.4

Access 2007 Update Query For Updating A Date Field

Hi I have been asked to write a query for an Access 2007 db

I need to update a date field in a table based on another date field in the same table

1st date field is the person's start date (StartDate)
2nd date field is the person's project date (ProjectDate)
Without going into too much detail, the Project Start date always begins on the 1st of the month

If the person's StartDate is between the 1st - 14th the project start date is counted as on the first day of the StartDate month/year

If the person's StartDate is between the 15th - end of month the project start date is counted on the 1st day of the NEXT month

Example:
StartDate = 7/1/2011
ProjectDate = 7/1/2011

StartDate = 7/14/2011
ProjectDate = 7/1/2011

StartDate = 7/15/2011
ProjectDate = 8/1/2011

StartDate = 7/30/2011
ProjectDate = 8/1/2011

I would like to update the ProjectDate in an update query

Any suggestions? Thanks
 

A:Access 2007 Update Query For Updating A Date Field

Create a Column heading like this
numdays: format([StartDate], "dd")
or
numdays: datepart("dd", [StartDate])
this will give you the day of the month on it's own
add a second Column
NewProjdate: IIf([numdays]>14,Format([StartDate],"mm")+1 & "/01/" & Format([StartDate],"yyyy"),Format([StartDate],"mm") & "/01/" & Format([StartDate],"yyyy"))

This will give you the date to use in the update query.
 

Read other 3 answers
RELEVANCY SCORE 82

I have 2 separate queries that indicate the last date an inspection was conducted of each type (routine & complaint) for all records in my database.

Is it possible set a query that will choose the record from the query that contains the most recent date entry for that record?

For Example:
Query 1(Routine Inspections)
Record #: 123
Inspection Purpose: Routine
Inspecation Date: 1/11/13

Query 2(Complaint Inspections)
Record #: 123
Inspection Purpose: Complaint
Inspecation Date: 1/12/13

I want the result:

Record #: 123
Inspection Purpose: Complaint
Inspection Date: 1/12/13

Any help would be greatly appreciated.

Thanks!
 

A:Access 2007: Query based on 2 Queries?

The best way to do this is as follows
1. Create a 3rd query using the Max function based on the same table(s) but do not include the Inspection purpose, this query will provide the last date for each Record # regardless of Inspection purpose.
2. Then create a 4th query that is based on the same table AND the new query, join the 2 together using both the Record # and the Inspection Date and include the Inspection purpose.
 

Read other 1 answers
RELEVANCY SCORE 82

Hi there i wonder if you can help. I am trying to create a query by form which i have mostly working, apart from one control on the criteria form. In the database i have stored a candidates date of Birth, and on this form instead of using date or birth as a critieria i wanted to use a range in the form of a combo box, so the user can select one of the following.

18-30
31-40
41-50
51-60
61-65
66+

I have created the QBF so that it creates a new query everytime. The code for this is displayed below. Any ideas how i can implement this range into the code. Any guidance or advice would be greatly appreciated. Thanks

Option Compare Database
Private Sub cmdSearchStudent_Click()
Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([Student ID])"
If Not IsNull(Me.txtStudentID) Then
swhere = swhere & " AND [Student ID] = " & Me.txtStudentID
End If
If Not IsNull(Me.txtNameEngSearch) Then
swhere = swhere & " AND [Student Name (English)] LIKE ""*" & Me![txtNameEngSearch] & "*"""
End If

If Not IsNull(Me.txtNameArabicSearch) Then
swhere = swhere & " AND [Student Name (Arabic)] LIKE ""*" & Me![txtNameArabicSearch] & "*"""
End If

If Not IsNull(Me.txtDOBSearch) Then
swhere = swhe... Read more

Read other answers
RELEVANCY SCORE 82

I have set up two queries in access. One lists all the time spent working on client files, in weekly totals. The other lists all the fees charged weekly. I have built a 3rd select query based on these first two, that pulls the information together, so I can build a report based on the third one. (I know, I could have done it in one query... but I already have the report built this way, and it works the same).

Each separate query works properly. But on the 3rd combo query, if there are weeks where no time is recorded for that client, it also drops the fees. In other words, it needs to see BOTH fee records AND time records. I would like it to still show the week, but with "0" as time spent. Right now, I'm having to go in and record a time entry with 0 minutes just so that it will work.

Perhaps it's a problem with the relationships? In the 3rd query, I have established a relationship between client #'s, and "week" (the week ending date) where it includes rows where joined fields are equal. If I try any of the other options, I get a message about ambiquous outer joins..... If I delete the join, I get a zillion records..

Oh why do I struggle so much with Access? Thanks for any help.
 

A:Access Select Query based on other Queries

Read other 14 answers
RELEVANCY SCORE 81.2

I have a table containing dates, product type, no. of units sold.

The date field contains duplicate dates.
ex:
DATE | PRODUCT | UNITS SOLD
1-JUN | A | 10
1-JUN | B | 23
2-JUN | A | 20
3-JUN | A | 30
4-JUN | A | 10
4-JUN | B | 30

Now, I would like to get the average of UNITS SOLD, based on number of unique dates.
How to calculate/count number of unique dates from the date field.
In the above example the number of unique dates should be 4 and average should be (123/4) = 30.75

Thanks
 

A:Access Query: Count occurances of unique dates in a field and use it to get average

Does this do it?

=SUM(C1:C7)/SUM(IF(FREQUENCY(A1:A7,A1:A7)>0,1))
 

Read other 3 answers
RELEVANCY SCORE 81.2

Hi all,

I've been struggling with this for a while, so I hope someone can help.

I'm using Access 2007 and have am using a TOTALS query which has a column that returns the SUM of HoursWorked

My problem is that I want to add a column which calculates two columns in this query and gives me the result of Total_Invoice: Rate * HoursWorked

Obviously I need the SUMMED value of hours worked in this calculation.

Is this possible?

Note:
I've tried changing it to a MAKE TABLE query and trying to do this calculation in an update query, but I can't get an update query to update the Total_Invoice column based on Rate * HoursWorked

Any and all help greatly appreciated.

Rob
 

A:Solved: Access 2007 Query : Calc based on Sum

Changed the query to a Select query (as it was a Make Table query).
Then ran another query based on the first one and was able to perform the required calculations that way.

If it is possible to do it within a single query I'd be happy to know how!

Rob
 

Read other 2 answers
RELEVANCY SCORE 81.2

Hey there again, I've come across another issue with my program:
I have a search form that allows the user to select a piece of equipment. A macro passes a value (EquipmentID) to a new form, and all of the fields are suppose to draw info from the table based on EquipmentID.
My problem is getting the fields to automatically update once you change the value. I started with using the ResourceSource of the forum, but users can cycle through enteries and once the Equipment ID changes the form doesn't update. So I'm down to several options:

Somehow use the ResourceSource correctly
Use a macro/code to fill the info in
Fill in a invisible field and pull the info from there

If you've got a suggestion on how this should be done, or you could let me know more on what's actually happening in Access, please let me know.
 

A:Access 2003: Autofilling fields from a Query based on a Value

The form, if it remains open does not get the new Value unless you "Requery".
To test if this is the problem you should be able to use Main Menu>Records>Refresh,
if that updates the form with your new value then that is the problem.
To me it would make more senese to have the New forma a Subform to the first form, even if it is as a Tabbed Subform.
 

Read other 1 answers
RELEVANCY SCORE 81.2

For MS Access
is there a function that exists or can someone build me one, so that i can assign an incremental number to a record based on the order that the records are sorted. such that the 1st record would be 1, 2nd=2, 3rd=3, etc.
 

Read other answers
RELEVANCY SCORE 80.4

Hi

I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?

Thanks

Garry
 

A:Solved: Excel Pivot Table (based on query with formula)

Read other 16 answers
RELEVANCY SCORE 80

I have written a macro to make a table from data in an existing table. Then I use the data in the new table to select information in another table. That select query does not allow me to update the information in the 2nd table. It is like the records are locked. Even if I create a new table myself the select queries are locked but all my pre-existing queries work fine. Is there some type of setting I am missing that causes the information to be locked in select queries? I am thinking somehow the setting may have been changed. Any thoughts would be appreciated.
 

A:Solved: Access Make Table Query and Select Query

Read other 7 answers
RELEVANCY SCORE 80

I've got a lending table in which we lend items out, now they can be loan or permanent.

I can run a report with the query parameter on the ReqDate_Fld criteria Between [Report Start Date] And [Report End Date]

The report prints and counts everything borrowed.

Now I want to add more records to the report to include everything borrowed that was returned during that period specified by the criteria Paramenter set in the query. But using the Return Date (RetDate_Fld)

How can I select rows from the query based on the parameter date filled in when the query ran?
[Report Start Date] and [Report End Date].

I can build a separate report just for the returns using the current parameter but on the RetDate_Fld Between [Report Start Date] And [Report End Date] set by the query.

I've got it grouping at 3 levels to get it to break by Borrower, ReqID, and Request Date

Does this make any sense?
Thanks
 

A:Solved: Access query report query paramater use the param

You would have to have another group for whether or not the RetDate_Fld was filled in or not.
Add a new column to the query with the following Heading

RetDatefilled: iif(not isnull(RetDate_Fld), 2, 1)

That should place a 2 in the new column when there is a date and a 1 when there isn't.
You can now group on that data.
 

Read other 2 answers
RELEVANCY SCORE 78.4

I need to get all the records for two different criteria

ReqDate_Fld between [Report Start Date] and [Report End Date]

and

RetDate_Fld Between the same dates above

putting it in the query twice doesn't work.

The RetDate_Fld can include records where the request date ReqDate_Fld is outside the start and end date criteria.

Example item criteria for ReqDate is between 2013-04-01[Report Start Date] and 2014-03-31 [Report End Date] and I have an item returned 2014-03-28 but it was originally requested 2011-03-21. The row doesn't get selected as it's outside the start/end date.

It requires AND or is it OR but danged if I know how to get it to select everything the was requested or returned between two dates.
 

A:Solved: Access 2010 selecting data from query based on two different sets of criteria

Read other 16 answers
RELEVANCY SCORE 78.4

I have a mainform and subform with a M/C link via two fields: Country, Sector.

In the mainform, the sector field is a combobox with a rowsource assigned to a paramater query that pulls the associated sector based on the specific value selected in another combobox (business) selected in the mainform.

I've put code in the AfterUpdate event of the business combobox to requery the sector combobox (to get it to update - which works) and then set the focus on and off the combobox, and then requery the subform. While the sector comobox requeries, it does not actually fill the sector box with the new value, and thus the subform doesn't change either. The only thing that works is clicking inside the sector box on the mainform after I change the business. Then the correct value appears and the subform updates.

Is there a way to get the mainform to recognize the requery as a change in the value of the sector field which will then fire the subreport refresh based on the M/C relations?
 

A:Solved: master child link based on combobox populated by query result

You should be able to force a record save, which would put the data in the Combo, but that might upset your Undo code.
 

Read other 2 answers
RELEVANCY SCORE 78.4

Help please. The Situation: Eight identical Dell computers (1 yr old) with Windows 7 Pro SP1 connected via an HP switch (all wired).  Very basic computer setups with only basic software... MS Office, Chrome, etc. There is one shared folder on "Computer
S" containing a commonly queried file, no person sits at or works on "Computer S", and the query software prevents any 2 people from querying the shared file on "Computer S" at the same time (ie. no overlapping queries).  All
computers have received all available Windows and Dell updates. The following problem occurs whether Homegroup sharing is used, or whether password-protected direct shares are used.

The Problem: If no-one queries the shared file from their computer for 15 seconds or more, then the next person to query it from any computer will receive their results back fast (<2 sec), and a Wireshark capture indicates that most packets are like this:

TCP   1514        [TCP segment of a reassembled PDU]

However, in the 15 seconds after someone queries the file, one of two thing can happen:


1) If the same person (same computer) re-queries the database (with the same or different query) inside 15 seconds, they will get fast results again (<2 sec) with "TCP" and "1514" packet lengths again, just as above. But...


2) If anyone else (a second person at a second computer) queries the file in less than 15 seconds, they ... Read more

Read other answers
RELEVANCY SCORE 78.4

Hi All,

I've got a query that's based on the value of a combo box on a form, Criteria:

[Forms]![frmSearchTenants]![cmbTenantSurname]Click to expand...

How do I adapt this to say if the cmbTenantSurname IsNull Then Show All, I'm guessing it's "*", but not sure how to write it.

Many thanks,
MRdNk
 

A:Solved: Access : Query based on ComboBox Selection, if combo box is null show all

Update:
I've gone for a new tack, I've programmatically run the query via a module, however now the requery doesn't update the subform.

ComboBox:
Private Sub cmbTenantSurname_AfterUpdate()
Call basDBQueries.QueryFindTenants(cmbTenantSurname.Value)
Me.subSearchTenants.Requery​End SubClick to expand...

Module:
Public Sub QueryFindTenants(sWhere)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sSQL As String
'Dim sWhere As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySubSearchTenants")

'sWhere = Me.cmbTenantSurname.Value

sSQL = "SELECT tblTenants.TenantID, " & _
"tblTenants.PropertyID," & _
"tblTenants.TenantForename, " & _
"tblTenants.TenantSurname, " & _
"tblTenants.MobileNo, " & _
"tblTenants.HomeTelNo, " & _
"tblTenants.Email, " & _
"tblTenants.StartDate, " & _
"tblTenants.EndDate " & _
"FROM tblTenants " & _
"WHERE tblTenants.TenantSurname='" & sWhere & "' ;"​
qdf.SQL = sSQL
End Sub​Click to expand...



Just solved it, I changed the:
Me.subSearchTenants.RequeryClick to expand...

To:
Me.subSearchTenants.SourceObject = "Query.qrySubSearchTenants"Click to expand...
 

Read other 2 answers
RELEVANCY SCORE 78

For a report I have a customer table, an invoice table and a revenue or payment table.

I need to produce a report that shows all the invoices created during a given month and year - that part is easy.

But then I also need to show the first payment given on that invoice. This is a situation where there is always a down payment made at the issuance of the invoice - but sometimes people will make more than one payment during a month.

Example:

I want the customer listed for each new invoice, the total amount of that invoice and what the inital payment on that invoice was: [CustomerID][Customer Name] [Invoice No][Total Invoice Amount] [Payment Amt]

So if there was an invoice issued for $100 and they paid 50 and 25 during the month it was issued and the balance on the next month, I still want to show it on this report as a $100 invoice with a $50 downpayment only.

(The tables are established and populated so it is too late to change the payment table to allow for a unique downpayment field. They all go in as payments.)

My problem is that if they made more than one payment during the month, the whole line with the customer name and invoice number shows up for each payment made. I only wnat the FIRST payment. I need a payment query that shows only the first payment in the month of invoice issuance.

Does that make sense?
 

A:SQL Query question for Access 2010 query

Yes it makes sense, I would create a pre-query that uses the Month & Year but is set to "Totals" and Date Minimum.
Then create the Report query using the pre-query linked to the new query's table via the InvoiceID this will limit both the month, year and minimum payment.
 

Read other 1 answers
RELEVANCY SCORE 78

I have a previously designed Access DB and there is a query that I am trying to understand. It has several tables that are queried more than once in a single query. First how do I determine the actual table name? I can only see the alias name. Also what is the purpose of using the same table twice in a query?
 

A:Solved: Access Query using tables more than once in same query

It is normally to do calculations or comparisons on prior or next records. It is the one weekness of Access Queries, the Totals function has some good arithmetic calculations like average, Count Sum etc but manipulating previous records like add or subtract values from the current record it can't do. So it requires more advanced SQl. Which is apperas you have.
Have you looked at the query in SQL view?
It should use the word "As" to designate the alias.
 

Read other 2 answers
RELEVANCY SCORE 78

I have created a search engine for a data base using Access 2007
The Form Basically Runs a query of multiple fields and loads the result into a Combo box.
The problem is the data base is not fully complete and the records with blanks in some of their fields do not appear in the search results.
For example lets say I am looking for a People with the same surname and in my database is
Name Surname
Mike Norris
Chuck Norris
Norris
The query will not find the record only containing Norris
Here is the query I have used.
Like "*" & [Forms]![Entry]![txtSurname] & "*"

 

A:Query, Query Searching Blank Matches

Read other 8 answers