Over 1 million tech questions and answers.

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

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

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


Preferred Solution: Trying to look at design of query based on another 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: Trying to look at design of query based on another query...

Bounce in case of new eyes to the forum!

Read other 1 answers

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

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


A:Calculated field in a query based on a query

Read other 7 answers

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.


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.
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


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


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


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


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

I have several related tables that store information about projects. The primary table is tblProjects.

The end goal is to have all of the individual records in tblProjects displayed in a subform that I can then filter based on criteria. For example, I have drop-down that lets the user select different types of projects. For the purpose of keeping this simple, let's assume the only two kinds of projects are "health clinic" projects and "community center" projects, which each provide those buildings to their communities. So I want to be able to filter and show only the "health center" projects, or only the "community center" projects.

The problem is that there are several projects that fit into both categories, and it's recorded in a separate table. The columns in this separate table include the primary key of the project, and the type of category (so each project could have potentially infinite categories.

Now, is there any way to initially show just one row per project, but then filter down the projects to show only the "health clinic" projects, or only the "community center" projects using filters based on the user's input? The issue I'm having now is that projects with multiple categories are showing up multiple times in the initial list: once to show the record with the "community center" category and once to show the "health clinic" category. How do I get rid of the do... Read more

A:Solved: Access - Query Design

Read other 16 answers


I'm trying to design an Access database to store details about projects and related resources. My idea was to have a projects table (Project) and a resources table (Resource). Project has a field called TechnicalLeadID and another called ProjectManagerID. Both of these fields are related to the primary key in Resource i.e. Resource.ID .

Any of the resources can be assigned as a Project Manager or a Technical Lead. I can't figure out how to write a query that selects fields from the Project table as well as the names of the Technical Lead and Project Manager from the Resource table.

I've been trying something like this:

SELECT Project.WorkReqeustID, Resource.FirstName AS TLFN, Resource.LastName AS TLLN, Resource.ResourceID AS TLID, Resource.FirstName AS PMFN, Resource.LastName AS PMLN, Resource.ResourceID AS PMID

WHERE TLID = Project.TechnicalLeadID AND PMID = Project.ProjectManagerID

FROM Project, Resource;

but it's not returning any results.

I'm open to changing the design of the table to something better if anyone has any suggestions.

Any help would be much appreciated.

A:Access Database and Query Design

Read other 10 answers

I'm in the process of creating a db and appear to have thought I knew more than I really do. The db currently has one table:


I need a second table (yet to be correctly designed!) which will contain equipment info:

Table2.????? - this should match Table1.DptKey

How I foresaw this working for me was to have a form where the enduser could enter in the equipment information, the employee info is already entered. I tried getting fancy and creating a query to allow for a drop down box that only showed all employees in Table1 that were NOT already in Table2. Within this form the I want(ed) to show the employee name and location, which are informational and not part of Table2, as this will be more intuitive to the end user than the Table1/Table2.DptKey.

I've tried to get this working a few different ways and can't seem to get it to function correctly. I don't know if it's because want I want to do can't be done or if it can and I'm doing some piece of it incorrectly? And I've now tried so many different things I don't know what I'm doing anymore.

My SQL is stronger than my VBA and I've been trying to solve this via queries/forms/web resources/chicken bones etc.

Can anyone tell me if what I want to do is doable or not? if so, how? I know that I want to ... Read more

A:MS Access03 design/form/query question

I didn't read your entire post yet (welcome to the site ), but I wanted to mention that the foreign key in your second table should have the same name as the primary key in the first table. In other words,

Should be

You'd then join the two tables in a 1-to-many relationship on that field by opening the Relationships window for the database, displaying both tables, dragging the foreign key from the second table onto the primary key of the first, and clicking Enforce Referential Integrity in the Edit Relationships window.

I'm going to say, never put spaces in any object names (not that you have). You should adopt a naming convention. Here's a pretty common convention.

Also check out The Ten Commandments of Access.

Some good resources:

The Access Web
AccessVBA (in particular, check out the FAQ forum)​
I feel like I ran right over that. If you have more questions, post back please.



Read other 2 answers

I am using MS Aceess. My problem is opening up a query in design mode, not working with forms. I'm not even trying to make a change - it's just opening up the query.This problem is only seems to happen when other people are using the datafile that the query is also looking at.

The query is using the linked table but I didnt find any access permision problem on the table beacuse the query actually do open finally but just after a long delay.

I really apricate any help on this issue


Read other answers

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

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

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

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

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 ?

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

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

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.


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

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.


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

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

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

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?

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.


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!


Read other 2 answers

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


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?



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

Read other 16 answers

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?

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

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

I need to get all the records for two different criteria

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


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

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,

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

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.

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

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...

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

Read other 2 answers

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

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

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

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

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

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.


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

Hi Access bods.
Can anyone tell me how to do an expression to query from DATATABLE (the name of table), containing

ID: Auto ID key for the table
SITEID: There are multiple sites
ITEMID: Each site will have multiple Items
QUALITYID: This is set to either F, or is left blank
Id like to find the Percentage of Non-F, to each SITEID and ITEMID combo. I hope this makes sense.

A:Solved: Access Query, Query

Read other 6 answers

...ookie crumbs?

Point 3. Clear out your temporary internet files and temp files.

What does this mean, is it....

Get rid of them?

Clean them of stuff?

What if there are things that i want to keep in these files, should i move them to a more permanant place?

orrrr, is this just a consequence i gotta put up with. Ok, so i lose lots of stuff i've saved (if there is lots, dont often check) but my computer will be clean and safe?

If there is something REALLY important in the temp file and clear means get rid of, is there a way of salvaging the contents that arnt bad?

A:Query ~ Instructions For Hijack This Log ~ Query

You should not have any files that you want to permanently save in Temporary Files. I have included directions for cleaning IE.
These instructions explain how to delete Temporary Internet Files. Microsoft Internet Explorer (IE) uses the Temporary Internet Files feature to store copies of Web content on the local hard drive. This feature improves network performance but can fill the hard drive with large amounts of unwanted data. IE allows many aspects of the Temporary Internet Files feature to be controlled, including the ability to delete temporary files as needed.
Difficulty: Easy
Time Required: < 1 minute
Here's How:

1. Open either Microsoft Internet Explorer or the Windows Control Panel.

2. In Microsoft Internet Explorer, open the Tools menu and choose the Internet Options... menu. In Control Panel. open the Internet Options list item. In either case, a new window titled "Internet Properties" appears.

3. In the Temporary Internet Files section of the Internet Properties window, click the "Delete Files..." button. A new "Delete Files" window appears.

4. In the "Delete Files" window, optionally check the "Delete all offline content" checkbox to also remove temporary files IE has saved for offline viewing. Click OK. A delay of up to several minutes may occur as IE proceeds to delete the temporary files it has cached. If the "Delete all offline content" checkbox is not checked, IE will not del... Read more

Read other 8 answers

I am pulling information from another program into Excel via Microsoft Query and one of the columns I want to pull in involves a date a payment is due. I am wondering how to write the query to have it always pull today's date +1 so essentially it would always pull tomorrow's payments due. Thank you!

I tried to add Where date = DateAdd("d",1,date()) but I am getting this error: "general odbc error." What does this mean and is there a way to fix it or alter query?

A:Help with date SQL query in ms query

Hello, in Access Queries you can just use
Date + 1
so perhaps it will work for MS Query.

Read other 1 answers


I have a worksheet with gets updated regularly. (Sheet 2 in the attached file)
Now, In Sheet1 I have two Drop Down Lists, on Selecting from first drop down, the corresponding values should be populated in the other Drop Down list.


A:Excel Query: Populate Drop Down List based on another Drop Down List

Made possible with link cell in dropdown 1

Read other 1 answers

I watch BBC I-Player on my Android Nexus 10 via an HDMI lead to my large screen TV. On trying to repeat this process with Sky GO I find that Sky have disabled HDMI acces to Sky Go applications.

Coukd I get round this using an MHL lead.

Any help would be appreciated.

Read other answers

hi guys, i have a Laptop that has 256mb of ram. now i have just downloaded the public release of windows vista and i am trying to install it.

the setup demands that i need 256mb of ram which i already have and exits. now when i go on system properties, it says i have 244mb of ram. why is this and is there any way i can make this 256mb?



A:RAM query

you may have memory allocated for video - although 12MB seems a little low - but that may be the min

Read other 3 answers
Q: Query


I was wondering whether it is possible to take a Intel Pentium 4 processor from one computer and put it into a another?
When I looked at the processor there did not seem to be anyway of taking it out?

Is it possible and if so is there a walkthrough as to how to do it?



Please provide the details of the processor. What socket does it use?

If both the PC has same motherboard configuration then it would be fine to transfer it to another. You can refer to the manual you may have got with your processor. Let me know if you have it.

Read other 4 answers
Q: Query

Does anyone know what this is ... "Se00bldsv" ?
I found it listed in services under Administrative tools. ( XP Home SP 2)
It is disabled ... but when I googled it I did not get any hits.



Definitely looks suspicious to me...I can't find anything about it either. What does it give for a description in "services"?

Read other 1 answers

I am testing SUS on an XP SP2 PC. The registry settings download the updates
to the PC from the server but they do not install. Why is this. The registry
settings I used are below. Thanks for any replies.



Read other answers
Q: Query

Sir I have a problem with my built-in webcam and it doesn't work already, but before it does work. When I used it in Skype I can't see the person I'm talking to and that person can't see me as well, I tried using my webcam in facebook and the problem was I can see the person I'm talking to but the person ca't see me. I tried to check my software and webcam status at the system told me that all is working just fine and I have the latest driver installed for my webcam. Hope you can help me ASAP. . Thanks in advance.Here's the specs:

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Ultimate, Service Pack 1, 64 bit
Processor: Intel(R) Pentium(R) CPU B950 @ 2.10GHz, Intel64 Family 6 Model 42 Stepping 7
Processor Count: 2
RAM: 1961 Mb
Graphics Card: NVIDIA GeForce GT 520MX, 1023 Mb
Hard Drives: C: Total - 259999 MB, Free - 56484 MB; D: Total - 350377 MB, Free - 41507 MB;
Motherboard: SAMSUNG ELECTRONICS CO., LTD., 300V3Z/300V4Z/300V5Z
Antivirus: Avira Antivirus, Disabled


howdy and welcome, I've removed your email address as posting that in a public forum is an excellent way to get on spam lists.



Read other 2 answers

Looking for someone with significant knowledge of PXE boot OS installations. Trying to learn to perform this taskt with a spare laptop, Vista Ultimate desktop and a Linksys router. Information is difficult to find.

Read other answers

Hi, i'm about to upgrade my PSU and had a quick query. I'm probably going to go for a 450W PSU at the local computer shop. It is rated super quiet (apparently 35% less noise) and it only has the one fan - a 12cm, low RPM fan, that will face downwards into the case. Would this mean that all hot air from the PSU will be blown straight out over my motherboard and CPU and would this lead to overheating issues?

Also, once i've changed PSU is there any reason why i wouldn't be able to take the 8cm rear fan out of the old PSU and install it as a case fan?

Thanks in advance for your replies


A:PSU query

the fans blow air out not in. dont buy a power supply from a shop, go to http://www.newegg.com/app/manufact.asp?catalog=58&DEPA=0 and look for one there, u will get a way better deal.

Read other 2 answers

Greetings !

I am planning to build a new system, the configurations are quite clear with :

1.core2duo 6600
2.2gb 800 mhz ram (1gb ea)
3.sata2 160 gb etc.
4.nvidia 8600 gt or the 8800 gs

My machine will be used for medium gaming, movies etc

Now my question is about the motherboard, my dealer has recommended the Asus p5ne-sli, i am getting at a good price.
I have some questions about the sli board which i hope you will help me with :

1. I don't intend to buy a second graphics card and wish to have only a single hi performance card , will the board give me good performance with a single card ?

2. Is it better to buy a non sli board ie will it function better in games , since i am using only a single graphics card ?

3. Due to the sli I have to buy an expensive power supply is it worth it ?

4. Are the graphic chipset on the sli mboard only to enhance the performance of dual cards or even a single one ?

5.What other mb chipset would you recommend for my config ? Is the asus P5b dlx more superior than the P5ne sli for my present config. ?

6. Do you recommend an intel board over asus for core2duo in stability and performance?

Basically I just don't want to buy a board whose sli feature I will not be using and if u say so ................ !

Thank you


A:SLI query

Read other 12 answers

Guys I have [email protected] of ram + [email protected]

Because I have the 256mb sticks of ram installed and they can only go to 533mhz my computer puts my gig sticks at the same freq. I was wondering if i should take out the two 256mb sticks out and leave the two sticks of 1gig ram in so they can run at 677mhz. Just wondering if this would be faster than having them all running at 533.



A:RAM query

it probably will.

Read other 2 answers
Q: query

Respected sir,

Last week my pc was attacked by help_decrypt virus & i was using win xp sp3, now i hd already cleaned that virus & i hd manually removed help_decrypt files,then i hd install win 7 & i hd aleardy taken backup but the problem is that i cant open any files like doc ,docx video files, jpg files in win7 so is there any problem that it can be open in win 7 as it was earlier win xp sp3 operating system & now win 7?

So my doubt is this is the problem that i cant open winxp sp3 files in win 7 or help_decrypt virus has vanished my entire files & folder. i hd lost entire data.

Sir if u help me out to this problem ur action will be highly appreciating.

Thanking u & awaiting ur fav reply soon.

Read other answers

I have 512 Mb ddr ram but this registers on analysis as showing only 478 MB. Does this mean that some faulty areas have developed? The system still generally performs well.(XP)
The only other issue which I think might be connected is that occasionally the display goes haywire with icons changing places and the taskbar moving around. I sometimes cannot regain control at all as the mouse control is also lost.
This usually happens if I am trying to multi-task quite hard with several windows open at the same time. Am I overloading the system or does the loss of control mean the possible faulty RAM is causing the problem?
many thanks for any ideas.

A:RAM query

Read other 9 answers