Over 1 million tech questions and answers.

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

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

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

Thank you,
LDW

RELEVANCY SCORE 200
Preferred Solution: Access 2003 – Query Based on Query – need code to rerun earlier 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: Access 2003 – Query Based on Query – need code to rerun earlier query

Read other 11 answers
RELEVANCY SCORE 151.2

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 144.8

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 141.6

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
 

A:Calculated field in a query based on a query

Read other 7 answers
RELEVANCY SCORE 136.4

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 134.8

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 134.8

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 131.2

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 131.2

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 126.4

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
RELEVANCY SCORE 125.2

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 125.2

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 124

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 120.4

Dear all,

I'm using Access 2003. In my query panel, I've 1 query that shows the total number of courses that each person took.

Problem:
1) How can I find the number of people who have taken only a sequential number of courses? For instance, using the query I've generated, there're 42 person taking 1 course, 51 person taking 2 courses, 7 person taking 3 courses and so on.

2) I need to generate another query that shows for each student, the number of basic and intermediate courses taken and the total number. For example, in my query, I've already stated the total number of courses taken per person. How do I have this field separated into number of Basic course taken & number of Intermediate course taken?
For the above 2 queries, I am only using 1 table named Stud_Attend_Courses. When the Course ID starts with B, it means basic course. When the Course ID starts with I, it means intermediate course.

Thank you.
 

A:Access 2003 Query

Hello again Cinbar, I am not sure that you can do it with just One query, so will you be outputting this to a Report or Form?
You could have one Query to do the "Grouping" and either a second query to do the totalling or use a Report or Form's "Sum" expression to do it.
 

Read other 1 answers
RELEVANCY SCORE 120.4

I am fairly new to access 2003 and self taught, but i have been asked to write a
query that has me stumped and i need some help.

I need to write a query that will return only the details ie name and address,
of clients who have not paid their renewal fee - the amounts will vary from client to
client as they are based on sales discounts and loyalty awards etc.

At first, I thought that this should be quite simple however, any one client will have a number of payments made throughout the year, with different amounts being paid/refunded. this will make it impossible for me to use the 'amount paid' to find all those who have not paid a renewal fee. It seems the only way to see if they have paid their renewal fee is to use the data held in the 'payment description' which will describe everything from their 'initial fee', 'refund of overpayment', 'discount applied' as well as 'renewal fee'

I only want to see those clients who do not have 'renewal fee' in the payment description fields and i'm finding it difficult to work out how i can do this. I have tried 'not like renewal fee', but this returns all the clients.

Any Ideas or suggestions??

the database has two tables, client and payments
 

A:'not like' query - access 2003

Read other 16 answers
RELEVANCY SCORE 120.4

How can you make a query NOT display duplicates without creating additional queries?

Example: one of my columns is "Equipment numbers" I am having the query return 20 records and I dont want to have any duplicate equipment numbers.

It is queryng from and excel spreadsheet.

Thanks, Joe
 

A:Access 2003 query

Joe, try setting the Query's Unique Values or Unique Records to "Yes
 

Read other 3 answers
RELEVANCY SCORE 120.4

Hi, I am having a couple of problems setting up queries in Access 2003. I built a database for our community group to administer registrations in childrens sports program. what I need to do is sort the kids into age divisions ie: bantams are 15 & 16 yrs old. We have a field with their birthday. How would one go about placing a criteria in a query? Second I need to run an append query to change the value in one check box to "unchecked".
thanks for your help
 

A:access 2003 query

Read other 7 answers
RELEVANCY SCORE 120.4

Hello,
I am a beginner to access 2003 and have setup a membership database in access.
I assume that search and query are one in the same.
If so I need to setup a query so I can search my membership database by LAST NAME to give me the members details or file.

I want to be able to search by the first letter or first few letters of the members last name.

Can you help me out? How do I setup this type of search?

Paul
 

A:access 2003 query

Paul, Search and Query aren'y actually the same thing, but you can have a Search Query and it best when based on the Input from a Search Form.
However to do something simple the way that you want using a "Lastname" you can use a Combo Box on the Data Input Form (assuming that you have one).
When you use the Toolbox Wizard for creating Combos, do not select the default option number one, which is "I want the combo to look up records in a table or Query", instead choose the last option "Find a record on my Form based on the value I selected in my Combo" and then choose the lastname field and the First name field as well. You will see that as you type in the lastname the Combo jumps to the names with the letters appear.
 

Read other 1 answers
RELEVANCY SCORE 120.4

Hi All,

I am needing to run a query which will identify records which are today going backwoods so basiclaly anything that i havent done which is already taken care of by a tick box.

Thanks in advance
 

A:Access 2003 Query

Use the Query Wizard to create a normal "Select" Query based on your table.
In the Date Column you can sort the date either Ascending or Descending.
In the Tick Box Column enter a -1 in the first Criteria Row.
 

Read other 1 answers
RELEVANCY SCORE 120.4

Anyone good with access 2003? Im stuck at one aspect of a a query I'm trying to make. Lets say you have a field labeld Cost and another labeled Paid. If Cost is $5 and Paid is $5 it should not display in my query, but if Cost is $10 and Paid is $5 I would like this to show when I go to data view instead of design view. Can anyone help? I can't seem to make it work with a simple cost>paid statment. I'm designing this in the design view.

A:Access 2003 Query

Are the Cost & Paid fields numeric? Wont work well if they are strings with symbols.

Read other 2 answers
RELEVANCY SCORE 120

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 120

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 120

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 119.2

I am running Access 2003 in windows 7 pro. I recently taught myself the basics of this powerful database program, but I've run into query problem that I can't resolve.

In one database I created a query with four fields, and the last one is called "commissions." when I run this query it returns the dollar amount in the "commissions" field for each record.

However, I would like the query to also show a "total" of all entries in that field. How do I do this without creating yet another query? Can this be done in design view by using a certain formula or syntax?

Thanks for your help.
Greb
 

A:Access 2003 -query issue

Read other 6 answers
RELEVANCY SCORE 119.2

Hi,

I have an append query that pulls data from 2 tables joined by the "CustNum" If the order has a seperate delivery address the program saves the the delivery address in the Customers table so then i need to join the tables by "CustNum" in the customers table and the "ShipNum" in the orders table. I was thinking is it possible to use an IIF statement?

for example if the "ShipNum" = 0 then the join would be "CustNum" in the customers table and "CustNum" in the orders table and if the "ShipNum" is greater than 0 the join would be "CustNum" in the customers table and "ShipNum" in the orders table.

Ihope this makes sense and someone can help me.

Thanks.
 

A:Help With Append Query Ms Access 2003

Read other 7 answers
RELEVANCY SCORE 119.2

Hi,

I have just created a query to feed data to my report. The query consists of;

Orderdate Between [start date] and [End Date]
Orderid
Ordertypeid [Enter Order Type ID]
Goodstotal

This works fine but i need all 3 order types to be in 1 report but each id seperate. The ID's are:
1= Postal
2= Telephone
3= Web

Could anyone help me with this please? Any help will be appreciated.
 

A:Access 2003 Report/Query Help

Read other 9 answers
RELEVANCY SCORE 119.2

Is there any way to insert an "if" satatement for the number of records to display?

Right now I have to manually change SELECT TOP ## to display the proper number of records based on overall number of records on a spread sheet

Is there any way to use an "if" statement to automatically display the proper number of records?

Here is my existing SQL view:

INSERT INTO tblCurrentCM ( ACTFINISH, ISTASK, STATUS, WONUM, DESCRIPTION, EQNUM, LOCATION, TARGSTARTDATE, REPORTDATE, SUPERVISOR, WORKTYPE, JPNUM, ACTSTART, ACTLABHRS )
SELECT TOP 13 tblMaximo.ACTFINISH, tblMaximo.ISTASK, tblMaximo.STATUS, tblMaximo.WONUM, tblMaximo.DESCRIPTION, IIf([EQNUM]="<Null>"," ",[EQNUM]) AS Expr1, tblMaximo.LOCATION, tblMaximo.TARGSTARTDATE, tblMaximo.REPORTDATE, tblMaximo.SUPERVISOR, tblMaximo.WORKTYPE, tblMaximo.JPNUM, tblMaximo.ACTSTART, tblMaximo.ACTLABHRS
FROM tblMaximo
WHERE (((tblMaximo.ISTASK) Not Like "Y") AND ((tblMaximo.DESCRIPTION) Not Like "*CGA*" And (tblMaximo.DESCRIPTION) Not Like "*MEO*" And (tblMaximo.DESCRIPTION) Not Like "*MATER*") AND ((tblMaximo.WORKTYPE)="CM") AND ((tblMaximo.ACTLABHRS) Not Like "0.00"))
ORDER BY Rnd(Abs([WONUM]));

And here are my parameters for the record count:

Sample Sizes
Lot Size Sample Size
91-150 = 8
151-280 = 13
281-500 = 13
501-1,200 = 20
1,201-3,200 = 32
3,200-10,000 = 32
 

A:Solved: Access 2003 query

Read other 16 answers
RELEVANCY SCORE 119.2

Hello,
I'm new to this forum, I have some formal education with access but i do not use it a great deal. I have built a database that keeps records of work done on a particular street. Each street can have many records with types of work, exact locations and dates. I want to query for all work done on a particular street. My criteria cell on the query was filled using the expression builder, [Forms]![Flushing Records]![Streetname]. I run the query from a macros button. When I run the query I get my expression coming up as the parameter instead of the form i want to use to select the street. From what I have read on the forum so far is that the form has to be opened first. Is that correct? It seems backwards. If this is true then the same macro button that opens the query can also open the form?

thanks in advance
 

A:access 2003 query by form

Northernont, welcome to the Forum.
You are corrrect, the form should be open with a "street" selected, then the query (or form/Report based on the query) opened.
If you use a Main Menu or switchboard to open your form you can add a Command button to the Form to open the query.
Or as you say you can open the form with the Macro, I do not normally use Macros other than an Autoexec macro to open the very first Form in the database.
 

Read other 2 answers
RELEVANCY SCORE 119.2

I am trying to build an expression that will display only sales that are more than 30 days prior to the current date. I have tried using the DateAdd function like this:
DateAdd("d",30,Date())
and
DateAdd("d",-30,Date())

I have also tried using the expression <Date()+30. Nothing seems to work. Any help would be greatly appreciated!

Jrod
 

A:Access 2003 query question

Well, using the expression <DateAdd("d",-30,Date()) in the SQL WHERE clause should find all records with dates more than 30 days ago. In SQL the WHERE clause should look something like: WHERE Mytable.Mydate < DateAdd("d",-30,Date())

In the query builder grid (Query by Example), or in 'Filter by Form' this is the equivalent of typing: < DateAdd("d",-30,Date()) in the criteria cell for field Mydate.

Obviously substitute your table and field names for Mytable and Mydate.

All this assumes that your field is defined in the table design as a date/time data type. If it is defined as data type text then you must also use the CVDate() function - eg: < DateAdd("d",-30,CVDate(Date())) - be aware thought that this will return an error if the field does not hold text that is a true date in a valid date format. To protect against this you can use an expression that uses IsDate() to check for valid dates before using CVDate(), for example:

In SQL: WHERE IIf(IsDate([Mytable].[Mydate])=False,False,[Mytable].[Mydate]<DateAdd("d",-30,Date()))=True

In the QBE grid type: IIf(IsDate([Mytable].[Mydate])=False,False,[Mytable].[Mydate]<DateAdd("d",-30,Date())) as an expression in the 'Field' cell and type: True in the 'Criteria' cell.

I don't have Access 2003 so my some of my terminology may be out of date, but the SQL should work.

Hope this all makes sense to you!

Deej
 

Read other 3 answers
RELEVANCY SCORE 119.2

If I can identify fields in a simple select query that need to have data flipped is there an easy way to do that?
 

A:Access 2003 Query Question

Read other 11 answers
RELEVANCY SCORE 119.2

Guys,

I'm having difficulty getting the query to show what i want.

on my client cards i have to show which employee is the sales person, demo guy, consultant etc, and the problem i am having is because one employee can be more than one thing - like can be sales for one guy and be demo for another, when i go to do a query to show who they are working on its only showing up records on which the employee was all 2/3 things.

is there a way i can do a query to show me all people they are working on?

Also, emailing. Is there a way to make a button that you click and it emails a form to a contact?

And again, does anyone know of a simple way to make a search form? ive tried several different ways and cannot get it. if it helps i need to search: client id, surname, first name. i also need to do a search thats a client id again, but its a from-to, to show multiple clients between whatever id's i pick

thanks guys.
 

A:Access 2003 - Query Troubles

I have posted versions of databases with emailing and Search forms recently on here, so if you look for posts by OBP you should be able to find them, if not I will post them again.
The problem with your query may be the Joins between tables. Access automatically creates a Join "Only Include rows where joined fields for Both Tables are equal".
This means that it will only return records where all tables have the value like PersonID in them
To overcome this right click on the Join line between the tables and the choose the other Option that fits what you want to do.
 

Read other 1 answers
RELEVANCY SCORE 119.2

Hi

I have a few simple databases I've created for different users. Nothing too fancy.

I have many queries that require criteria input. Mostly date ranges. Several of the queries, but not all of them have a quirk.

You double click to run it, it asks for the beginning date you enter it. It asks for the ending date, you enter it. It will then ask for the beginning date again and you have to enter it again and then the ending date again. Then you get your data returned.

If I delete the query and recreate it, it runs fine for a while but eventually will have this problem again. None of them have this problem at the start.

Is there something I can check or is there maybe some function I'm doing incorrectly that's causing this?

Thanks
 

A:Access 2003 Query Problem

I know this doesn't answer your question, but I prefer to use a form to enter the Search/Filter criteria. You then set the queries criteria to the Form's fields.
 

Read other 2 answers
RELEVANCY SCORE 119.2

Hello all,

I am trying to create a query that sums one field based on the values in two other fields.
Field 1 contains the number of units
Field 2 contains the month those units were added
Field 3 contains the month those units were dropped (if applicable)

I want to create a query that lists the months as columns and sums the current unit count for each month.

For example, the 200901 column would sum Field1 if (Field 2 was <= 200901) and (Field3 was > 200901 or blank).

For example, the 200902 column would sum Field1 if (Field 2 was <= 200902) and (Field3 was > 200902 or blank).

Is this possible?

Thanks for the help!

Frezz
 

A:Sumif Query in Access 2003

Frezz, welcome to the Forum.
This should be possible, but it may take 2 or more queries.
Can you provide some data to work with?
 

Read other 1 answers
RELEVANCY SCORE 118

Hi all
Have started up a new machine and upgraded from Access 2000 to 2003 and transferred our database over. We have just over 5,000 records in the contact table which we update daily with the current date as various repeat contacts are made.
The relevant field is set to Long date.
Every evening we have a Word 2003 file that completes a mail merge to a database query that pulls records with the current date. That query obtains its data from the contacts table. Or at least, it used to.
Since the change something has gone wrong and I cannot sort out what. The query will not let me use the =Date() function in the criteria row. As a test I set up a dummy database with a simple table and a single query. In the query grid I added =Date() in the criteria row and low and behold it worked. So obviously there is a problem in the original table. Now I am lost. Does anyone have any pointers please?

BigAl
 

A:Using the =Date() function in Access 2003 query

That is odd I have just answered another poster with date problems.
can you open your New table and check the Actual dates in the field ensuring that you expand it>
Does it have the "Time" in the field with the date, you may have to change the Date/Time format to Short or long time to see it?
Has the Date field always been "Long" date rather than Short date?
Also try in the Query in a new Column
Format([Your Date Field],"dd/mm/yyyy") and put the date() criteria in that column instead.
I have just checked it and it works whereas Long date with a time element won't.
 

Read other 1 answers
RELEVANCY SCORE 118

I've got a database that exports queries via macros into Excel. Included in this is - for example - the name of a consultant and the number of hours he's worked.

Alas, while the query in Access displays the first and last name of the consultant for the ConsultantID field (which is the number field that's related to the ConsultantID unique number field in the Consultants table), it shows only the ConsultantID as a number after export.

To remedy this, I put in another column in the query, which is basically Expr1:[Consultants]![FirstName] & " " & [Consultants]![LastName]. This works fine for queries in which the table containing the name (be it consultant, customer, etc) is related to only one other table in the query. However, when the name table is related to two tables that are already used in the query, I get the error:

The SQL statement could not be run b/c it contains ambiguous outer joins. To force on the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement.

I gotta admit: I'm not sure what that would mean here. I tried creating a seperate query that combined the names, and then referenced that query in the query I was to export, but no dice: same error.

Thanks for any guidance!
 

A:Solved: Access 2003 query question

Read other 9 answers
RELEVANCY SCORE 118

I have created quite a few successful queries. However, one of my end users has asked for a select query that has me baffled. She showed me an example that my predecessor created, but I haven't been able to duplicate the design. See the attached jpg.

I offered her a select query using "enter parameter" boxes that pop up and ask for a query's criterion, but that isn't what she wants. She wants, in effect, a form that looks like the single table that she will query. She wants to be able to fill in any of the columns (with a parameter value) and use that for her query. It seems simple enough, but I'm missing something obvious (obvious to everyone but me).
 

A:Solved: Access 2003: customized query

The jpg doesn't show much, i.e. no criteria rows.
There are 2 ways to achieve what she wants, one using advanced VBA and the other Criteria in the Query based on
forms![formname]![fieldname]
 

Read other 3 answers
RELEVANCY SCORE 118

I have an inventory database set up with tables, forms, queries, and reports. I run reports from the tables through a date range parameter query. When the report is opened it prompts for a start and stop date, then generates the report based on that range. My question is: is there a way to have the date range entered inserted on the report?

Thanks in advance for any assistance.
 

A:Solved: Access 2003 parameter Query

I did a google search and found this:
http://www.databasedev.co.uk/printing_parameters.html

I think it's what you need.
 

Read other 2 answers
RELEVANCY SCORE 118

Hi All.

I have 1 main table that I must populate the departments based on a 2 numbers inside a document number (POM623857) where the number I need is the 5th & 6th that identifies the dept. There are over 30 depts. I wanted a query to update the table; however, the expression is too complex. I was using the following:

IIf([Cncl Doc Id] Like "????02*","Auditor",IIf([Cncl Doc Id] Like "????03*","Commissioners",IIf([Cncl Doc Id] Like "????04*","Clerk",IIf([Cncl Doc Id] Like "????05*","Election Board",IIf([Cncl Doc Id] Like "????06*","Voter&#8217;s Registration",IIf([Cncl Doc Id] Like "????07*","Coroner",IIf([Cncl Doc Id] Like "????08*","Recorder",IIf([Cncl Doc Id] Like "????09*","Treasurer", IIf([Cncl Doc Id] Like "????10*","Surveyor",IIf([Cncl Doc Id] Like "????12*","ISA"...)))...

I don't see the need to put the entire expression. What is another alternative....put it in a module??? Again, I need this to update the main table.

Also, I created 3 update queries thinking it to be a quick fix; it any previous update is erased by the currently run query.

Thanks to all!!!
 

A:Solved: Access 2003 Complex Query

Read other 6 answers
RELEVANCY SCORE 118

I have linked a contact folder from Outlook to my database. I have already written a make table query that has been the fields I want to work with into another table. I want to write a query that will update this table with any new entries in the outlook folder.

It has been a while since I worked with Access and not sure what criteria to set to only add the new data.

I appreciate the help.
 

A:Access 2003 - Append or Update Query

UTC, welcome to the Forum.
For appending new data I would add a "control" field which adds together fields like the contacts name and birth date or Social security number to create a Unique identity. Set that field to "Indexed - No Duplicates".
Also have this concatenation field in the Append Query, only new records can be added to the table.
 

Read other 1 answers
RELEVANCY SCORE 118

I have 1 table in Access(companies) - its big. I want to search on the postcode field. I want to use a dialogue box so that I can put in the beginning of the code followed by a * for wild card characters e.g. CM* This works in the query if I type it but not in the dialogue box. Ideally I would like 2 or 3 dialogue boxes so that I can have OR's e.g. CM9* OR CM1* or CM2*. I just need a query that will feed into a report but will give me flexibility in selecting the postcode area. It's the wild card characters that are causing the problem. I like using dialogue boxes for queries so that I can filter into a report. This is really annoying. If you have a solution it will mean I can finish the database. Yipee!
Mrs T
 

A:Access 2003 Query +wild cards

Read other 10 answers
RELEVANCY SCORE 118

Hiya,

I'm trying to update 1 column of my VendorProducts table with information from my Market table based on the type of metal the product is made of. Each record of the VendorProducts table is marked Gold, Silver, or Platinum. I want Access to update the Market column with the correct precious metal market for each record.

I hope the scenario makes sense! Does this seem like something I should be able to accomplish with an Update Query? If so, how would I do that?

Thanks in advance for taking the time to help me out!
 

A:Access 2003 Selective Update Query

Read other 7 answers
RELEVANCY SCORE 118

Hi

I have a Users DB that I cannot make any changes on it. One of the fields has a Date field. I need to run a query on that table to create reports, but the query creates multiple records for the same user with the only difference in the Date filed. I need to have only the records with the latest date in the Date field.

Is there a way to set a query that does this?

Thanks,
Barbos
 

A:Solved: access 2003 query by last date

You need 2 queries, the first has the userrecordId and date fields, set Totals>Grouping the userrecordid Grouped and the date set to Max.
Then in the second query you want the main table and the 1st query joined by the userrecordid and the date.
So the first query finds the last and the second show the data from just that record.
 

Read other 2 answers
RELEVANCY SCORE 118

I have a problem and have working on it for 2 days. I have one table (persons) and one form (People). The form (People) has the information about the person like first name, last name, gender, marital status, etc. I would like to add father and mother if it is listed. The table (persons) have:
personsID first name last name gender marital status kids index kids
1 John Smith male married 1 -1
2 Tom Jones male married 2 -1
3 Mary Smith female married 1 -1
4 Jane Jones female married 2 -1
5 George Smith male single 0 1
6 Pam Smith female single 0 1
7 Al Jones male single 0 2


The problem that the form (People) display the person but not the parents. I have a query that is:
Field: first name last name kids index gender
Table: persons persons persons persons
Show: X X
Criteria: [Forms]![People]![kids] male

which is supposed to display the father. Say, youre displaying the record of Pam Smith female single. Running the father query youre supposed to get John Smith but I get a blank listbox. Could somebody help me out with get the parents to display on this Form?

Thanks!

Scott
 

A:Solved: Access 2003 Form and Query

Read other 16 answers
RELEVANCY SCORE 118

Hello,

I've got an query in Access 2003, one of the fields calculates a sum by using the following code:

Code:
Amount: Sum([inv_ammount]*[costunits]*(1+[btwrate]/100)*IIf([buy_sell]=1,1,-1))
How do I make sure the value is always rounded to two decimals (currency amount)? For instance $ 4,30171 should become $ 4,31

It's giving me a headache

tnx in advance,
Wouter
 

A:Solved: rounding in access 2003 query

Read other 6 answers
RELEVANCY SCORE 118

Hi

Can you tell me please how can I create a new Query table using SQL in VBA mode?
For example: I have table "Employees" with "FirstName" and "LastName" fields. On the form user would choose to show "FirstName" only, "LastName" only, both and etc...., and the application would display the table with the chosen fields.

Thanks,
Barbos
 

A:Solved: Access 2003 - Creating Query in VBA with SQL

Read other 7 answers
RELEVANCY SCORE 118

I am trying to use an append query to add to a table named Fees. My query is as follows:
INSERT INTO Fees ( ID, TotalBill, [DATE] )
SELECT READINGS.ID (findloc([meterbk],[reading],[xreading]))+0 AS TotalBill,
READINGS.DATE
FROM READINGS INNER JOIN ACCOUNTS ON
READINGS.ParcelID=ACCOUNTS.ParcelId
WHERE (((READINGS.DATE)>=Forms![Process Rates]!BegDate And
(READINGS.DATE)<=Forms![Process Rates]!EndDate));

When I run the query, no records show in the table. This is my first time posting, so I don't know exactly what you need for information.

Module 1

Public Function FindLoc(MeterBK, reading, xreading)
Dim FEE as Currency
Select Case Meter BK
'Rutland Town Books
Case "901"
If reading <> vbEmpty And xreading <> vb Empty Then
Let FEE = ((((reading - xreading) / 100) * [Forms]![Process Rates]![TwnRate])
+[Forms]![Process Rates]![TwnMeter])
End if

This query deletes the old rates

DELETE Fees.ID, Fees, TotalBill, Fees.DATE
FROM Fees
WHERE (((Fees.Date)>=[Forms]![Process Rates]![BegDate] And
(Fees.DATE)<=[Forms]![Process Rates]![EndDate]));

Any help is appreciated.
 

A:Access 2003 Append Query Problem

Read other 9 answers