Over 1 million tech questions and answers.

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

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

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.

Preferred Solution: Query Access 2007 (how to transfer data from a query to 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: 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

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


I'm a beginner in Acess 2007. I'm trying to use combo box to retrieve data from subform. But it doesn't seem to work. I have view the forum. But I do not understand the codes well.

Here are my tables.

Code & Result.

I 'm using code_ID from table code to query the result in the subform in my combo box which is call combo12. The query statement is [SELECT Code.Code_ID, Code.Name FROM Code] in the row source.

What must I do to ensure that the subform will display the data from table Result based on the code_ID select?

The relationships has been link up.

Pls advise what other data need to be furnish. I thank you in advance.

A:Access 2007 Using combo box to query data in subform

Hi Nov2009,


Are your combo box and subform on the same form? If they are on the same form, when you created your subform did you make sure that the Link Master Fields and Link Child Fields were Code_ID? You can find this out by checking your subform's property sheet.

So here's a step by step if the combo box and subform are on the same form. Open the main form that your subform is connected to. Open the main form in Design view. There are two ways to bring up the property sheet for your subform. You can either click on the outside border of the subform and the combo box at the top of the property sheet should list the name of your subform. Or you can select your subform from the listed items in the combo box at the top of the property of your main form.

Check the "Data" tab or the "All" tab to see what your Link Master Fields and Link Child Fields are set to. If they are blank, you can type in the field names that you want to filter on each other.

Hope this helps. Let me know if you have any more questions.


Read other 1 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

Hi, I have a Form created with a text box called "SoldTo_Text"; the user will input multiple SoldTo numbers; I then want the query that is pulling from the "SoldTo_Text" box to bring back data for those multiple SoldTo's. For Example, user inputs 3 soldTo's (1111,2222,3333) then the query takes those sold to's and returns the other values I have set in the query, we'll say they are percentages, so then it would look like (spaces indicate new columns):

1111 3% 5%
2222 6% 8%
3333 8% 3%

Currently, my query works great when they input 1 soldto, but as soon as I try to put in another soldto with it, it doesn't work.

Any help is appreciated. Thanks.

A:Access 2007 Query a list of data from a form user input

Read other 11 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

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

I am a Java newbie and am in the process of designing an application for Java enabled mobile phones.
I need to consider the best method of tranfering data between a phone and a central server.
I need to consider that not all phones are web enabled, and some web enabled phones are limited to only access the phone providers portal (I have a 3 phone and I can't get out to the web, I am only allowed access to the 3 portal).
One option may be to use the phone to dial a number and transfer data, but then this is harking back to the old modem style dialup process. The main problem I envsage with this is that if you have 1 modem, only 1 person can access at a time.
A really good option would be bluetooth, but that has a limited range (about 10 feet I think).
I am open to any suggestions or advice.

A:Java Data Transfer Query

Hi trev.h,

You should have a look at this page. They are talking about the J2ME Wireless Toolkit 2.0.

Read other 2 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

Is there any way to have query data (from SQL Server) pulled into Excel 2007 without being in a "table". This is garbage. If I try to get rid of the table setup it dumps the query. This was not the case with 2003. In 2003 It pulled in where I told it to but was not formatted into a table. If I run my query based spreadesheets in 2007 that were written while still using 2003 they function fine. If I write them while in 2007 I get the table BS associated with the return. Not many 2003 machines left in our company!! Thanks

Read other answers

Ok..........I'm having no luck trying to create a query that will do what I want. So here goes!

If you don't mind "getting your hands dirty", I've attached the DB, and through this post, I'll explain what I'm hoping to achieve. Hopefully you can help!!!

After the login page, the "Program Implementation list (form)" appears. On this page you can navigate through the various 'Open' projects/programs. When a projects/programs is selected, the "Program Details" form opens, which self navigates to the selected projects/programs from the "Program Implementation list".

As the various projects/programs are owned by a department, what I want to do is have a summary down the bottom of the "Program Details" form which shows me the progress to completing all tasks by each Department for the project which is displayed on the "Program Details" form.

What I'd also like (if possible) is to have a combined summary of progress to completing all tasks on the "Program Implementation list (form)" somewhere before the "go live date" column.

Am I trying to do the impossible?

A:MS Access 2007 Query Help

kvargordi, welcome to the forum.
There is no database attached, so it is a bit difficult to answer your last question.
But it may be possible, although datasheet and Continuous forms cannot have subforms of their own.

Read other 3 answers

I have an invoice report that I want to post customer payment history on. I only want to print the previous three months of history. I tried to search on the [Payment Date] field for dates: Between Now() and Now()-90

This doesn't work of course, but in a perfect world that is what I am trying to make happen.

Every time I do an invioce I want the previous three months of payments to show on it as recent activity, for what ever month I happen to be billing.

A:Access 2007 query

Between Date() and Date() - 90
Ensure that that the Table's date field does not contain a "Time" element, if it does you will have to "Format" the Date field to do this.

Read other 2 answers

I have a bail bonding program that is has a report of all bonds executed during the month. The state is now requiring that report to also list the initial payment on each bond. I have been able to query out all payments made on a bond during the given month but to isolate only the first payment on each bond is tricky.

If I show [BONDS] by [DATE EXECUTED] AND [PAYMENT] for each by [PAYMENT DATE], Is there anyway to query out first payment during a given month if there was more than one?

Hope that makes sense...



You could try using Group By using MIN([Payment_Date])

Read other 2 answers

I have a simple query to do a calculated field. One of the fields is a lookup column. So when I try to calculate Yearly Rent: [Monthly Rent]*[GRM], I come up with an error "The multi-valued field '[GRM]' is not valid in the expression '[RENTAL INFO].[MONTHLY RENT]*[GRM]'. Any suggestions?

A:Access 2007 error on a query

Read other 16 answers

Good Morning,

I am working on an Access database to gather information and contact information on our Employees. We use this database currently to upload all Phone and Email contact information to our Automated Emergency calling system. We have employees that would NOT like to be called. How can I build a query to take a "YES/NO" field, corresponding to a "Option Group", and only gather or query the information if the YES is chosen. Basically I would like to do the following:

[YES/NO] = 1 (yes)

Then Query all required information

Else [YES/NO] = 2 (no)

Then ignore and query rest
Any help will be greatly appreciated. Programming and especially Access programming is not my strong suit. Thank you again in advance.

A:Solved: Access 2007 Query Help

Welcome to the forum D_Shofkom33.

The Yes/No field should be in your table, not in the query.

You can then create queries as to whether the employee can be contacted or not.

See attached.

Read other 2 answers

Please could someone help me. I am creating a query in Access 2007 from a table. I am a relatively new user. What I am trying to do is:
Column 1 Column 2

I want B to be written in Col and and B. Where B is in Col 2 and preceded by an A, it will type the A that is in Col 1 and i do not want this A to be displayed. I have written: Not "BT" and ="PROPHECY" but it is not working. Please can someone help. Thank you very much.

A:Access 2007 Query Question

Danielle, welcome to the Forum.
You need something like this in a New Coulmn Heading
Output: iif([Field1] <> "BT", [Field1], [Field2])

Where Field1 is the name of the field in Column 1 and Field2 is the name of the field in Column 2

Read other 2 answers

I am trying to narrow fields by Month, Quarter, and Year for several reports. Month and quarter work, but for some reason, when I try to set my dateEntered criteria for Year I do not receive any results. Here is what I am using so far for the dateEntered field criteria:

Between [Forms]![Individual Time Card Report Form]![Combo15] & "/01/" & [Forms]![Individual Time Card Report Form]![Text19] And [Forms]![Individual Time Card Report Form]![Combo15] & "/31/" & [Forms]![Individual Time Card Report Form]![Text19]

Between [Forms]![Individual Time Card Report Form]![Text29] & "/01/" & [Forms]![Individual Time Card Report Form]![Text34] And [Forms]![Individual Time Card Report Form]![Text32] & "/31/" & [Forms]![Individual Time Card Report Form]![Text34]

Between "1/01/" & [Forms]![Individual Time Card Report Form]![Text25] And "12/31/" & [Forms]![Individual Time Card Report Form]![Text25]

The text boxes refer to user input from an already open form (which works fine for Month and Quarter) What am I doing wrong with year? Or is there an easier method for searching for only records from a specific year? Thanks

A:Access 2007 Report Query

Read other 15 answers


I am getting an error while executing the update query in MS access , Find the error description below;

Error Message: "The query cannot be completed. Either the size of the query
result is larger than the maximum size of a database (2GB) or there is not
enough temporary storage space on the disk to store the query result."
Please find below the SQL codes for your reference,

UPDATE CDHDR_Consolidated INNER JOIN CDPOS_UPD ON CDHDR_Consolidated.[Doc No] = CDPOS_UPD.[Doc no] SET CDPOS_UPD.Update_Date = IIf([CDHDR_Consolidated]![Doc No]=[CDPOS_UPD]![Doc no],[CDHDR_Consolidated]![CDHDRDate/Time],0)
WHERE (((CDPOS_UPD.Update_Date) Is Null));

Please let me know whats causing this issue and how i can resolve this.

A:Solved: Access 2007 query

Read other 6 answers

I am working in Windows 7, Access 2007.

I store grades in one table called "OverallResults" and student details are also in a table (StudentDetails).

I have created a series of six queries (aModule x Average) to calculate the results for modules 1 - 6. (Each of these queries works individually) Each module consists of many assignments and I have calculated the average per assignment and module in these queries. However, when I combine all these queries to get an overall average, it produces the error message "Query too complex".

The Sql code in the average query is:

SELECT StudentsDetails.Country, StudentsDetails.MentorGroupName, StudentsDetails.OrganizationName, StudentsDetails.Cycle, [aModule 1 Marks].M1Final1, [aModule 1 Marks].M1Final2, [aModule 1 Marks].M1Avg, [aModule 2 Marks].M2Final1, [aModule 2 Marks].M2Final2, [aModule 2 Marks].M2Avg, [aModule 3 Marks].M3Final1, [aModule 3 Marks].M3Final2, [aModule 3 Marks].M3Avg, [aModule 4 Marks].M4Final1, [aModule 4 Marks].M4Final2, [aModule 4 Marks].M4Avg, [aModule 5 Marks].M5Final1, [aModule 5 Marks].M5Final2, [aModule 5 Marks].M5Avg, [aModule 6 Marks].M6Final1, [aModule 6 Marks].M6Final2, [aModule 6 Marks].M6Avg, IIf([M6Avg]+[M5Avg]+[M4Avg]+[M3Avg]+[M2Avg]+[M1Avg] Is Null,Null,Round(([M1Avg]+[M2Avg]+[M3Avg]+[M4Avg]+[M5Avg]+[M6Avg])/6,2)) AS [Course Avg3]
FROM (((((StudentsDetails INNER JOIN [aModule 1 Marks] ON StudentsDetails.StudentNo = [aModule 1 Marks].StudentNo) INNER JOIN [aModule 2 Marks] ON... Read more

Read other answers

Hi, I am new here. I am trying to run a query that will give me the birthdays for the month I am in. I tried using this Between Date() And Date()+30 under the Birthday column criteria. But no luck. I am a beginner in Access 2007 but really need to know how to do this. Greatly appreciate feedback and perhaps screen shots

A:Access 2007 Query Birthdays

Read other 7 answers


I have this access database that someone else created, I have now this error "mis-match in data", I know it is because my data is not in the right format in one of the files, but how do I figure out where and which one it is???

I am hoping that one of you can help me....


A:Access query - mismatch in data - but where????

It is most likely to be any joins between tables.
The other possibility is if you have any criteria in the query.

Read other 1 answers

Hello, I have used the query wizard to create a simple query the will bring up a list of all the clients the food pantry has served "Today" I know the number should be X but the results are almost 1/2 that about? I am using the entry date (Today) 3/4/08 as the thing for the query to pull the data of the table. the rest of the fields are only holding the data. I am having the columns sum the results of most of the tables ( Kids, adults and depentents and etc.) but the info is not correct and it seems like it should be simple??? Am I missing a simple fact?


A:Query in Access 07 All data not included?

Read other 7 answers


I am using the Call Tracker template in Access 2007, and have got rid of the Call's List table and am just left with the Calls Table and the Call Details Form. I have created a query which finds information based on a set of numbers from two separate databases which I imported into my database. However, I want to be able to link that query to a form. In this form I have text boxes, each are labelled and linked to the table. So what I want the query to do is; when the number is entered into a text box, and the Enter Key is pressed, the query runs and finds the information and inputs it into the required fields below it.

If that doesn't make sense please let me know, any help will be appreciated.



A:Access 2007 Linking a query to a form

It does not make sense without a bit more information.
Are you trying to "Find" a particular record?
As your form should already display the data.

Read other 3 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

I have a report I run based on a query that works perfectly. I'm managing event registration for an association of school student councils. The report is a separate page for each school that summarizes the information I have for that school relative to its participation, i.e. school name, address, who is coming, how much they owe, etc.

It works fine when I want a page for all of the schools, but sometimes I just want a single school...not all of them. I copied my original query and set up a parameter in the copy [What school?] and it selects the school I want. However, when I run a copy of my original report I made changing the source to my new query, when I open the report, the parameter box opens, I put in the school name I want, and the box comes up again, and eventually my report opens with no data in it.

Help? I don't do VBA, but I get around Access pretty well.

A:Access 2007 - Report from Query with Parameter

Read other 9 answers


I believe this is a really easy thing to do but i can not get it to work,

I have a table that contains a name and the dates that they are on holiday. i am wanting to have a query that will show me everyone who is on holiday in February.

I thought this was simple but i have not been able to get it to work, i think i am missing something really really simple.

I have attached a copy of the table, the table is part of a college project and all data is fictional.

The attachment is zipped so it could be attached


A:Solved: Access 2007: date Query

James, to find all dates for February can be done in 2 ways, you can use from the 1st (startdate) to the 29th or 28th (eneddate) and then type in the Query Criteria Row
between 01/02/2008 and 29/02/2008
or you could use the Date part or date Format to extract February form you date and then compare it to February, but that only works if you have one years worth of data.
I can't help with your zipped file as I do bnot have Access 2007.

Read other 2 answers

Hi folks...

in my Access 2007 Database by the use of a combo box one can filter a query containing some projects by topic (of the project) or by country (where the project is being developed). Now I would like to add a check box: when it is checked the filtering by country follows the filtering by topic, otherwise they are independent...

How could I manage??

thanks for your help and time
have a good one


A:Solved: Access 2007: combined query

stefano, an Option Frame would actually be better, it can contain as many "Option" buttons or Check Boxes as you like. You can then include that as a Header for a new column in your query and include options in the Criteria rows.
For an example of Complex Searching see this thread and the Database that I posted in Post #3


Read other 1 answers

Hi All.

I hope you can help.
I have a simple table with 2 coloumns: Name and Date. What I want to do is when access is launched, I need it to run a query (ideally in the background) and if the date is within 1 month of the current date, then I want a pop-up window to appear saying "Joe has less than 1 month of contract." It can then either display a long list of names and dates or have different pop-ups for different people.

I'm sure its simple, but I cant find any help online.

Many thanks

A:Access 2007 - Alert on startup from query

Hiya, I think alot of this was covered in this topic


If it doesnt help or you need something further I would recommend posting again.

Read other 3 answers

I have a temp working in a copy of a database that is in a secure location that she does not have permission to access. I have exported the entries she did into Excel with the intention of appending them to the actual database using an append query. I have imported the spreadsheet as a table and have been trying to append the records to the main table but nothing is happening when I run the query.
NOTE: I am getting an error message that states: "An INSERT INTO query cannot contain a multi-valued field "

A:Append Query trouble in Access 2007

Margo, I would ensure that all the fields in the Import table are checked and ensure that they match the Field types that you are trying to append to.

Read other 2 answers

Hello all, and thanks in advance for the help!

Here is my problem:

I have an Access 2007 database using a Pass-Through query to pull data from an external source (OBDC/Sybase). Within this select query, I have a where clause. I'd like this where clause to pull records or data (for use as parameters) from a table WITHIN the Access database.

For example, my pass-through is pulling data from an "orders" table on an external server, but I want my criteria from the pass-through's where statement pulled from an INTERNAL "criterias" table. I plan on using a form to update this internal table. Is this possible? And if so, how? Thank you!

A:Access 2007 Pass-Through Query Question

Read other 6 answers

Hi There!

I am running a query to populate a report. I have some records in the table that indicate 'yes' and some that indicate 'no' in a JOINED? field.

I would like my report to show me those records who haven't joined ('no'), so I put criteria 0 in the query, which is fine.

However, I would like to keep a running tab of those records with 'yes' selected in JOINED? separately as just a statistics FYI in the report. I do not want to know anything else about these record besides how many have 'yes' under JOINED? field.

Is it possible to make a report generate this?

A:Solved: Access 2007 BOTH yes and no! Query Question

Read other 11 answers

I have a database that I built that has tables, forms. When the data is entered on the forms it populates the table. I want to make a query that takes all the questions on the form and gives me a yes or no. Example the query would be these three fields, if any of the fields say yes then in the fourth field [Were they home] would automatically say no and if all the fields say no then [Were they home] would say yes.

The IIf formula that I am using is this:
IIf([Inpatient during] = "Yes" OR [Resident of hospital]="Yes" OR [Resident nursing facility]= "Yes", "No","Yes) with the IIf formula in the [were they home] criteria line.

The problem that I am having is the information is in the form/table but when I run the query it is blank. Any suggestions?

A:Solved: Access 2007 If OR Statement Query

Read other 7 answers

I'm trying to create a cross tab that has employees as the row headers, and the available training courses as column headers. In the intersections, I've created an expression to display when their training expires based on when they took the course and how long that course is valid for. I've created the cross-tab without a problem, but the expiration dates are giving me an issue. For the employees that have taken a course multiple times (to keep their certification up to date), the query only displays the expiration date for the first time they took the course. In other words, the query will not use the most recent training record as the source for the expiration date. I'm running Access 2007 on Windows XP.


A:Solved: Cross-tab Query help, Access 2007

Base the crosstab query on a pre-query that only shows the employee's last course expiration date by using the Grouping and "Max" of expiration date in the pre-query.

Read other 3 answers

Hi there, i am relatively new to access and so a little naive with it all. I have created a database that purely stores details of what is in a candidates Cv. These details are things such as gender, nationality, and then others such as qualifications, spoken languages etc. Some of those are multi value combo boxes where the details have been entered in, there not looked up or anything.
Now the situation is that i am purely wanting to use the database to search the database, and want to do this using a form whioch contains a range of text boxes and combo boxes so that whenever i select the relevant data, it runs the query and produces the details that match that. Obviously if one of the combo boxes is left blank then i want it to disregard this field criteria. I have been told that the only way i would be able to do this is through VB code, which to be honest im not the best at. Ive tried small just to get it working first of all but im having issues. my code is dsplayed below:

Private Sub cmdSearchCriteria_Click()
Dim strWhere As String
strWhere = vbNullString
'Search for Gender
If Not IsNull(gendersearch) Then
If strWhere = vbNullString Then
strWhere = "(tblCandidateDetails.Gender] = " & gendersearch & ")"
strWhere = strWhere & " And ([tblCandidateDetails.Gender] = " & gendersearch & ")"
End If
End If
If strWhere = vbNullString Then
DoCmd.OpenTable "tblCandidateDetails"

'Finally,... Read more

A:Solved: Access 2007 Query By Form

Read other 16 answers

I was trying to create a report from scratch based on a parameter query in Access 2007. But my query did not show up in the "Fields List Box" where I could pick the fields I wanted for my report. I saw all the tables in my database with their associated fields in that box but no query. Is there anything I was doing wrong? Please help


A:How to base a report on a query in access 2007

The easiest way to accomplish this is to highlight the query and Hit report - you can then strip it all out and start from scratch again but it will link the correct fields

Its strange i know but it jsut doesnt like to work with queries.

The alternative is, if you really do want to start from scratch is create the Text Box (unbound) and link it in properties to the relevant field. Like in the picture

Read other 2 answers

I have a query where I need to count the the number of entries where the yes/no box is checked. The query is grouping on a field from the table and then simply supposed to "count" the field formatted as yes/no. There's something I'm missing about the nature of the yes/no field, I'm sure. Any help would be appreciated.

A:Solved: Access 2007-Count Yes/No in a query

Read other 13 answers

Hey guys,

I'm trying a QBE to get a paramater query to work in Access 2007... and am potentially being rather thick .

Basically, I'm trying to run a search through names in one of my tables (table colleague column name)

The query I have used so far to get the search box up is the standard [Enter Name], however, I need to enter the exact name... I have tried *[Enter Name]* to try wildcards on either end.. and it didn't work.

I want to be able to search by part of a name.


A:Solved: Parameter Query in Access 2007

Never mind.. sorted it with

Like "*" & [Enter Name] & "*"

Knew I was being thick

Read other 1 answers

Trying to finish my group project in my senior AIS class. I need to design a query that pulls the date value from an employee list if the current date is 6 months from the date when employment began.

This is the only part of the query that I cannot seem to get to work and is holding up the update function of the query to increase the employee's pay rate.

I followed what was in the help function of Access and thought I had corrected it look back over 6 months but it didn't work at all. Myself and the rest of my group would appreciate the help.

The spider web as we have nick named it.

Disclaimer: The above is a fictional company created for the project including fictional data for testing purposes. Just saw the disclaimer at the header of the business app forum and wanted to get that in there.

A:Access 2007 and date query functions

Read other 16 answers

I am just learning Access, and I have run across a problem I can't seem to get around. My queries are coming out incorrectly, and I'm not sure what I did wrong. For example, for "Category and title order information", it's giving me the same title for all but one record. Can someone help point me in the right direction? I'll be happy to forward the database - it wouldn't let me attach it here... (sigh - I'm such a newbie...)

A:Access query returning wrong data

Read other 12 answers

All I am trying to do is move a value from a Query, into a text box on a report.

I created a Query, which adds up all the numbers in another query, and provides me with a total. I am trying to add this to a footer of an existing report I already use. So as I mentioned, I just want to popualte the report text box with the value from the query, so that it happens automatically.

I have tried putting formulas into the "control source" on the "data" tab of the text box, but nothing seems to work right.

Can this be done??


A:Transferring Query Data to a Report in Access

Read other 6 answers

Access 2000:

I have data in one field that needs to be merged into another field (that already has data in it).

Is there a query I can run that can do this?

A:Access 2000: Query To Merge Data

Read other 16 answers

Hi, I'm having an issue with wanting to take data from an Access query, and plugging in the data into an excel file that I want to use as a template. Attached are the database file I'm working on (please forgive the crudeness of its functionality), and the template file that i want to plug data into from the query.

I'm experimenting with VBA code, and using CopyFromRecordset (seen in "Module1"), but the furthest that i've been able to figure out is importing data from the Access tables (not the query) and plugging that data into the spreadsheet. What i want is for users to input data into the form, click on a button, and the data in the queries will populate into the spreadsheet template.

Attached are my database, the excel template, and an example of how it should look.

The process is such:

User opens up "Discrepancy Form Master x1" to input data. For simplicity, the first record will be used as an example, and the date field on this record reads 3/1/08. From here, i would like the user to click on a button to run "Module1". Module1 should pull the data from the queries in the database, and plug the data from the queries into the spreadsheet in the defined cells. From there, the spreadsheet should look like the expected results.

If someone can help me with the VBA coding on this request, that would be great. I've only discovered the usefulness of VBA and have looked into it for only a couple days, but haven'... Read more

A:Access Query data to excel template

Read other 12 answers

The query needs to delete a record from a table based on input from a form.

I first made a select query, which found the exact records that needed to be deleted based on the criteria from the user inputs on an open form. Once I confirmed the query was returning the correct records based on that the user had entered, I converted it to the delete query.

When I run the query in design view (having the form open in the background, so it can pull the necessary values) it works fine. The correct record is deleted successfully. But I need this query to be executed by the user from the form where they actually enter the criteria. When I put a button on the form that would allow someone to run this query directly from the form itself, I get an error saying "There's no field named listSub in the current record." listSub is the name of a list box that's providing one of the criteria for the query.

Why does this query work perfectly well by itself, but the moment I try to make it part of a form it breaks?

Thanks in advance. Please let me know if you need any additional info.

A:Solved: Access 2007 query works alone but not in a form

Read other 6 answers

I have Access 2007

I have tried to find the answer to this, but i have not found a simple answer. I have created a family business database ( i must state i am new to access) which works ok. I now want to run some queries but when i do they come back as blank fields. I guess its something to do with Relationships and Join Types, which i admit i dont fully understand Join Types

So not to mess up my database i created a simple 4 table database Table1, Table2, Table3 & Call.

I want to create a query from Call Table and add a field from each table Table1(LastName), Table2 (property), Table3(status). I will then set criteria on Status "Completed"

I suppose i need help on the correct way to join these tables together so the query works. Or you may say its something else.

Its probably something simple to someone.


A:Access 2007 Query returns blank results

Read other 12 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