Access :: Query Returns Results From One Table/query But Not From Other Table/query

Query Returns 2 Results Instead Of 1

Nov 26, 2006

I am creating a database for a hyperthetical car hire company. A customer hires a car from and until a certain date. If a new customer decides to hire a car i want to generate a list of cars that he can choose on depending on the other dates from which other cars are hire from and until. I have created a query that generates all of the cars that the new customer can not use.
I have also generated a list of all of the cars, in the database.

I have created a new query takes the numberplates of all the cars and subtracts the numberplates of the cars that are being used.

This is where i hit a problem. For some reason the query is generating the list of number plates twice and then subtracting the numberplates that are being used. This leaves me with with 2 values for every numberplate that can be used and 1value for every numberplate that cannot be used. Can you help me?

Please post your email address so i can send you the zip file as the file is too large to upload. The query that i am having problems with has the title:SEARCH FOR AVAILIABLE CARS.

Thank You

View 2 Replies View Related

Query Returns No Results

Feb 7, 2007

Hi all,

I think this could be another one of my stupid moments. Here is my query expression -

IIf([WHAT TEAM?]=1,[strCurrent_Team]="FINANCE",[strCurrent_Team]<>"FINANCE")

When run, the user is prompted to enter a team number. If they enter 1, then by my reckoning the query should return all records where [strCurrent_Team] = "Finance".

However, no records are returned.

strCurrent_Team is formatted as text.

Can anyone tell me what I am doing wrong here?

Cheers,

Rob

View 3 Replies View Related

Query Returns Unwanted Results: Please Help

Apr 9, 2007

Hi,

I'm trying to create a database project for college and have run into a problem with a query. I am trying to find all bookings that have taken place in the last month, but with added details from other tables.

I have taken the job details and date (with validation for the last month only) from my Jobs table, and this works perfectly. However, when i try to match customer IDs to their names (stored in a seperate table), Access returns the same job multiple times with every customer name possible.

Any help you can give me to return just the one result needed would be greatly appreciated.

Mompo

View 8 Replies View Related

Reports :: Query Which Returns Values From A Table

Jul 27, 2014

I have a query which returns values from a table, as follows:

Service_Date
Supervisor
EExpect
EAchieve
OExpect
OAchieve
IntLossOP
IntLossEN

This is used for a report for a 28-day period (the query selecting the Start Date and the End Date), and the intention is to show the information grouped by Supervisor, showing the information for as many days as they worked.However, I would like to have the overall 28-day averages of the six fields EExpect thru to IntLossEN shown underneath the detail for each individual. I know I could do this in Access Totals in the page footer if the report was confined to one supervisor, but with up to 20 supervisors I am unsure how to get these totals after the detail for each?

I would also like to have this summary information for each individual presented on a separate page at the end of the report (almost like a league table, if you like), so there is a single page that shows the data for each supervisor in comparison to all their colleagues.

View 7 Replies View Related

Queries :: Query With Instr Returns Unexpected Results

Aug 26, 2014

I have Access 2010. If I run this query

Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')

It returns 3 rows, with values in MyField of 0, 23 and 355.

Now I add 1 more where clause to filter on MyField and the query looks like this:

Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
AND
(
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0
)

So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?

View 10 Replies View Related

Make Table Query Returns Binary Data Types

Jan 31, 2008

This the first time I post in this forum and I tried looking for an answer to my question before posting, so I apologize if my question has been answered previously.

Is it possible to determine, in Access 2000, the data type in a Make-Table query of a new column with null value so that it does not default to binary.

Here is my sample sql:

SELECT tblAddressBook.Name, Null AS Email INTO tblTestTable
FROM tblAddressBook;

The output is tblTestTable with two columns: Name and Email. The data type of column Email defaults to Binary. How do I make it default to Text.

Thank you.

View 4 Replies View Related

Get Results From Query Into Table

Aug 30, 2005

This is a rael daft question, but how do I get the results of a query into a table?

View 5 Replies View Related

How Put Results Of The Query Into The Table

Oct 13, 2005

Hi!
I have query with one column results
How to put automatically results of this query into one column table
Thank's a lot

View 1 Replies View Related

UPDATE Table From Query Results?

Jun 9, 2006

Alright, i'm almost sure this isn't the best way of doing this,

But i wanna build a scoring system out of three criteria. cost, delivery, and qaulity. I have those tables built along with a contact table where there over all score will be tallied up. Each contact can have more than one entry in the criteria.

So what I did was i built a query to make a new table for each contact to generate the score and the contact id, then using that ID, i update it to the contact table using the UPDATE function...however wheni have 3 criteria, and 400 contacts..this obviously becomes painfully slow.

The question is, is there a way to directly pull the sql query results (summing the total of each contacts score for each criteria), then storing it into the contact table without making a new table in the process?

View 1 Replies View Related

Use Query Results From Report To Make Table

Aug 2, 2006

Hello,

I have a query that prompts the user for input to generate a report.
I would like to in essence copy that same record set and append it to a different table (archive table).

Not quite sure how to go about it.

Thank you

View 5 Replies View Related

Locking A Table Based On Query Results

Aug 24, 2006

I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field? Something like, if field1 = "expired" then lock the table? I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!

View 1 Replies View Related

Queries :: Use Query Results To Fill In Records In Another Table

Aug 18, 2015

View 1 Replies View Related

Forms :: Binding Results Of Query To Textbox Then Writing Value To Table?

May 19, 2015

Basically, I originally wanted to create a form which I can input data into Access with by using a button (I was unable to get Access to append the information from the forms to the table, so it didn't work out!). I did not want the fields on the form to be linked to a table, as then a record (and most importantly an auto-number) is created as soon as somebody starts typing. Should somebody stop typing halfway through and quit the form an autonumber will have been generated, which makes the number of "users" seem higher than it actually is. I got around this by changing the field from autonumber to number and then creating a query that selects the maximum value of ID in the table, then adds 1 to it (which is essentially the lowest unique number. I tend to call this newID).

The problem I now have is setting this to be written to the table alongside the data from the form (the rest of the fields on the form are now connected to the table, as I am no longer using an autonumber). I have tried the following: Setting the form to run the expression "[ID] = [qryMaximumUserID]![NewID] " on load. This returns the error "The object doesn't contain the Automation object 'qryMaximumUserID.' Setting the control source of the text box to be dLookup. This fills the textbox with the correct value, but then it doesn't write it to the table! Setting the default value of the field to be 0, then running an update query to update any ID of 0 (criteria "0" to the value of newID "update to: [qryMaximumUserID].[NewID]. "). However this doesn't work as whenever the query is run it asks for a parameter to be entered, rather than just taking the value from the other query. Writing a macro that is run on load to SetValue of item:[ID] to expression: [Forms]![qryMaximumUserID]![NewID]. However this returns an error "Microsoft Access cannot find the referenced form 'qryMaximumUserID' you entered in the expression.

View 4 Replies View Related

Tables :: Summation Of Fields In A Table Based On Query Results

Nov 5, 2013

I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.

I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.

View 13 Replies View Related

Modules & VBA :: ADO With SQL To Add Append Query Results To Excel 2007 Table

Oct 16, 2014

I have successfully used VBA to populate select query results into an excel worksheet on open, for a co-worker. Now I am trying to populate the records from an append query to the bottom of those results, which are now in a table on an excel spreadsheet, Contractor EIF. I am trying to make this work because my co-worker modifies the results in the table, deleting rows, adding fill color etc. and as new projects begin he would like those added to the projects already in the table (without rewriting the entire table).

Private Sub Window_Open()
Dim strDB As String
Dim strMyPath As String
Dim strDBName As String

[code]....

View 2 Replies View Related

Modules & VBA :: Send Query Results In Table In Body Of Email

May 24, 2014

I have created some code to get the records of a query and it puts them in the body of the email but it doesn't format it well for example the email looks like

James | halliwell | 31
Leanne | smith |27
Alexis | smith |8

I would like it to be in a table is this possible,

Code:
Public Function SendEmail()
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Set MyDB = CurrentDb

[Code] .....

View 3 Replies View Related

Queries :: Update Table With Values From Field Using Results From Select Query

Sep 23, 2014

Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.

How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?

View 1 Replies View Related

Show Current Record Value From Table In Combobox In Addition To Query Results

May 3, 2015

I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.

I have a form to enter how many of these items has been used and from which batch number they belong.

The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.

All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :

Code:

SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName
FROM ItemBatch
WHERE (((ItemBatch.Finished)=False)
AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));

What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.

Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.

View 3 Replies View Related

Changing Data In Query Results Changes Data In Source Table

Dec 17, 2012

I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.

View 2 Replies View Related

UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.

Oct 28, 2005

Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)

View 9 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 Replies View Related

Queries :: Query To Insert Missing Rows From Another Query / Table

Oct 8, 2013

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15

What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15

View 2 Replies View Related

Query Results Minus Query Results = New Query?

Apr 1, 2008

I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance

View 7 Replies View Related