Over 1 million tech questions and answers.

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

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

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?

RELEVANCY SCORE 200
Preferred Solution: Solved: Access Query using tables more than once in same 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: 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 109.6

Hi,
I have created the following tables:

Progress: Table
- Progress_Day
- Progress_Date

Expenditure Details: Table
- Code
- Expense Description

Planned Cost: Table:
- Progress_Day
- Code
- Total_Amount

Actual Cost: Table
- Progress_Day
- Code
- Total_Amount

The progress table and expenditure details table are linked to both planned and actual cost tables in a one-to-many relationship. In the progress table, it has records from 1 to 143 days.

In planned cost table, record is only entered up til 132 days, but in acutal cost, record is entered up til 143 days.

To generate an analysis between actual and planned cost, I created a query with the following fields
- Progress.Progress_Day
- Expenditure Details.Expense_Description
- Actual Cost.Total_Amount
- Planned Cost.Total_Amount
- Expr1: [Planned Cost.Total_Amount]-[Actual Cost.Total_Amount]

When I generate the query, I get only results up til 132 days. I then tried to replace Progress.Progress_Day witih Actual Cost.Progress_Day, but I got my data got messed up, and the Planned Cost.Total_Amount field returned with a single number for all records, in my case $1000.

Is there anyway I can generate all 143 days of data, and where planned cost do not contain the data, it will just return a zero value.

Thanks.
 

A:Solved: Access 2007: Query for 2 tables returned only common result

Read other 15 answers
RELEVANCY SCORE 108.8

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

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

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

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

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

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

Read other 11 answers
RELEVANCY SCORE 105.6

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 104

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 104

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 104

I know this may seem simple, but I am only starting to learn Visual Basic and I am encountering some problems.

I am linking to a table using the recordset function and was wondering if there was any code to link to a query in MS Access, as using the same code does not work.

Any help would be greatly appriciated,
Thanks.
 

A:Linking to query tables in MS Access from VB6

Read other 8 answers
RELEVANCY SCORE 102.8

Hello!
Please forgive my ignorance up front. I was brought into a small office and I have just enough knowledge to cause problems.

I recently converted my company's access database from 2007 to 2016. I don't believe that is the source of my problems but since I really have no clue....it could be. I created a simple table to keep track of current tenants because we didn't have a printable table that included the info I wanted. It was not connected to any other tables and I had no issue with that but the owner was not happy and so I began to snoop around the relationships section. I honestly didn't think I made any changes but when I went to run reports for the owner last week I was unable to pull up some simple ones. If anyone is willing to help I will provide more information.
 

A:ACCESS Query no longer pulling from tables

Read other 6 answers
RELEVANCY SCORE 102.8

hi guys

I have been struggling with this for a while, first I tried to find a solution in EXCEL, however after doing some online research I now think it may be better to create a query in Access (2016)
I have multiple tables and and all have a diversity of info, but also some common denominators...

I will explain what I am trying to achieve... I need to produce a report based on several spreadsheets (our company does home delivery of furniture) , historically we have been looking at the 'bad guys', but we really need to focus on the good guys as well.
Because all data is spread over a number of spreadsheets I think MS Access will be the way forward (the problem is that I haven't used Access since I retrained in 2000 #shameonme
Some of the spreadsheets don't contain drivers names, so we need to collect the names from an other spreadsheet/table (by checking route number and date)

In short I need to produce a report showing true performance results for drivers across the business

I welcome any suggestions/guidance
 

Read other answers
RELEVANCY SCORE 102.8

I have 9 queries (which I can convert into tables) that I need to have added to an excel spreadsheet onto certain tabs. I am using Access 2010.

I have read different ways of doing this either through a macro or linking them.

When I try to do the macro it puts me into the GUI screen and i am not sure how to get to VB to manually enter a macro.
I will probably be adding more queiries once I can get this step down. I am trying to create this for a speed for another user.

Any suggestions?
 

A:Access query/tables to Excel workbooks

I would suggest "Linking" them would be the obvious choice.

I do have some VBA code for transferring multiple queries to different sheets in a workbook, but it is a fair amount of work to set up and may be temperamental.
 

Read other 3 answers
RELEVANCY SCORE 102

Hello,

I am somewhat familiar with access and excel, I am trying to create a query from almost 40 seperate tables that are linked to a file on our server (so the data refreshes when changes occur).

I have tried the SQL option of

Select * from table apr13
union all
Select * from table apr14
union all

Etc...

I continually get this error:

Syntax error in query. Incomplete query expression.

Any assistance would be so helpful!

Ultimately I am trying to track due dates and build interactive dashboards. Since the origional Excel file is several worksheets by month instead of one consecutive sheet - I am trying to create one query that includes all worksheets as seperate imported tables. Most of the tables are exactly the same - just different due dates.
 

A:Access 2007 1 query with multiple linked tables

Read other 6 answers
RELEVANCY SCORE 102

I have 2 tables. Both of them have a Date field and both have a serial number field but they are not linked.

How can i create a query to show one list of all serial numbers and one list of all dates without the dates and serials for each table showing up in different columns.

I want one column for date and one for serial number. Basically put those 2 fields from each database on top of each other

Thanks,
 

A:Access - Query 2 diff tables, compile results

Code:

SELECT [Table1].Date, [Table1].Serial
FROM [Table1]
UNION
SELECT [Table2].Date, [Table2].Serial
FROM [Table2];
 

Read other 3 answers
RELEVANCY SCORE 102

I am wondering how to set up the parameters in my query so if a user wants to search by name, they can retrieve records from different tables by just inputting the person's first or last name or even a partial first or last name. For example, I have a user name Doug Jackson, I don't know Doug's last name so I want my query to scan all records looking for the name Doug and bring back the appropriate records. I am not concerned if it brings Doug Jackson or Johnson, just bring all the strings of Doug, Douglas, Douggie, etc.
Please advise.
 

A:Access 200: Query multiple tables with partial info

Read other 7 answers
RELEVANCY SCORE 100.8

Good morning!

Here are my computer specifications:
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: AMD Athlon(tm) II X2 250 Processor, x86 Family 16 Model 6 Stepping 2
Processor Count: 2
RAM: 3327 Mb
Graphics Card: NVIDIA GeForce 6150SE nForce 430, 512 Mb
Hard Drives: C: Total - 472278 MB, Free - 447618 MB; D: Total - 4649 MB, Free - 421 MB;
Motherboard: PEGATRON CORPORATION, 2A99h, 6.01, X312345678
Antivirus: eTrust ITM, Updated: Yes, On-Demand Scanner: Enabled

Here are my questions:
1) I am trying to set up an Access database that takes information from 3 tables (Customer Info, Ad Data, Issue Dates) and makes one form with all information included.
(I have a "Complete" table with the Customer Info, Ad Data, and Issue dates all included that works well with the form. The problem with this is that this will easily become a huge, unmanageable database.)
2) I want to be able to update the form (which also updates the tables) as well as being able to pull up past records. (I don't want to use a subform, because the Ad Data are all calculated fields.)
3) I also want to be able to group each ad by issue date.
4) The Customer Info table should be the master table. When I try to run a query using Customer Info, Ad Data, and Issue Dates as a record source, I get a blank query (not populated with data).
5) I am not able to create relationships between the Customer Info, Ad Data, and Issue Date tables for the query. (I want to have a ... Read more

A:Creating Access database with multiple tables for query, forms

sorry, forgot to add I'm working in Access 2003
 

Read other 3 answers
RELEVANCY SCORE 99.6

I am doing a query from table "A" selecting records by a date range. These selected records have a location code in them.

Table "B" has these location codes plus a descriptive title that matches the location code

Is there a way I can have my table "A" selected records go to Table "B" and pull out the correct descriptive title based on the location codes?

I am building this query to feed a report and having the descriptive location titles would make it easier to read.

I am using ACCESS 2000

Any help or tips on where to go to learn more are appreciated.

I have a book but have been unable to find out how to do this
 

A:Access 2000 find and insert names from one table into another tables query

Bring both tables into a query, then click (hold) on the Location field (from list) from table "A" and drag it to the Location field in table "B" (or vice versa). If there are any other lines joining the 2 lists delete them. Ussually the "ID fields automatically appear joined.
NOw "Right click" on the line (exactly) until you see "Join Properties" Choose the one that you feel appies.

put the fields in into the query grid and when you run the query the data from the 2 tables should match. At first you may display all the fields so you can compare data but after that just delete the repetitive fields, plus if table "B" has more details, it is probably best to only add the "location" and "Description" field.

You may need to experiment with the "Total" option to get the desired results.

This query can then become the control source for the report thus giving you the description.
 

Read other 3 answers
RELEVANCY SCORE 97.6

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 96.8

I have a microsoft access database that tracks projects for my department. I currently have a query that I use to generate a report that shown me how recently the project has been updated:

SELECT Tasks.Project, Tasks.Description, Tasks.Date
FROM (SELECT Project, MAX(Date) AS maxdate FROM Tasks GROUP BY Project) AS x
INNER JOIN Tasks ON (x.Project=Tasks.Project) AND (x.maxdate=Tasks.Date);

I would also like this query to sort the status of the project, so that I am not reminding people with completed projects to update their information. However, the Project status is stored in a table with all other details called "Projects", and all tasks are input through the "Tasks" table referenced above.
I am having a difficult time having the query reference two unrelated tables, and I am trying to avoid replicating data. the basic idea is " where Tasks.Project = Projects.[Project Name], display Status", but I am having trouble finding out how to do this in SQL or otherwise.
Any help would be greatly appreciated.
 

A:Solved: Query Displaying From Multiple Tables

Read other 9 answers
RELEVANCY SCORE 96.8

Sounds like a Lord of the Rings reference doesn't it? Ok i have 3 duplicate tables. 3 people will be entering data each into their own table. We have another girl that will need to see all the data from all 3 dispatch tables in one table. Now the fields are identical in each table. Can i write a query that will take for instance. Work_Order field from each of the 3 tables and display it in one field in the query, and do this for each field?

I dont want it to say Mike.Work_Order, Bob.Work_Order, Chris.Work_Order. I just want it to say Work_Order and display them from all 3 tables. Then do the same thing for each other field in the 3 tables. So she only sees the equivelant of one table with all 3 tables data inside it.
 

A:Solved: 3 duplicate tables, one query to combine them

Read other 9 answers
RELEVANCY SCORE 96.8

Hi there i wonder if you can help. I am trying to create a query by form where i have used a form that has various controls on. I wanted to use these controls as the criteria for my search. Once the Search command button is selected that this would open up the results that would show the relevant details. I had initially just linked the controls on the form to the criteria section of a query. However some of the controls could sometimes be Null. i know this is not such a problem, as you can just use the following formula.

[Forms]![frmStudentSearch]![cboCourseSearch] or
[Forms]![frmStudentSearch]![cboCourseSearch] is null

However i have 9 controls ranging from text boxes, combo boxes and two check boxes, and it seems to always crash and cause problems. Just to let you know that the query is based upon the data in three linking tables

I know it may be easier to use VB Code, however unfortunately this is an area that i fall down on. Any Advice or guidance will be greatly appreciated. Thanks for your time.

Hollinshead
 

Read other answers
RELEVANCY SCORE 92

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 89.2

hi
i have an asp application using access db
i need some help with an SQL statement
both tables are in the same db
in table 1 i have 4 columns, but am only concerned with two of them
in table 2 i have 10 columns , but am only concerned with one
i have populated column 3(3 letters of text) in Table 1 with a previous SQL statement
Column 4 in Table 1 is set to default 0 (zero).
Column 10(text) in Table 2 is origianally blank, but does get popluated when the user submits the form. it will contain 3 letters of text as well.
what i want to do is check to see if the text is the same in both Table 1 , column 3 and Column 10 in Table 2.
if they are the same, i would like insert a value of 2 to Column 4 (default 0) in Table 1.

i have been trying to do this for a while now , without success
any advice or assistance would be greatly apppreciated.

thank you
Mac
 

A:query across two tables

Read other 6 answers
RELEVANCY SCORE 88.4

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

A:query based on 2 tables

paulbol, welcome to the Forum.

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

Read other 1 answers
RELEVANCY SCORE 86.4

I have Macbook Pro with High Sierra OS, version 10.13.4 with Excel 2008 for Mac. At one time I was able to
use a web query to load tables in Excel for a spreadsheet that I created.
For some reason, unknown to me, I am no longer able to use this
feature in Excel.
Is there some workaround that you are aware of that I can use to restore
this great feature?
 

Read other answers
RELEVANCY SCORE 85.2

I will need to combine two tables on a weekly basis. Table 1 has the Product ID and the date and some important data. Table 2 has the Product ID, the date, and some other important data. For table 1, I only need to append the table once a month because the value needed stays consistent. However, for table 2, the values needed change on a daily basis.

How do I create a query that will command you for the date from table 2 and take the values from the month on Table 1?

The ID is not unique in either table, but it is unique for the date in each table.
EX:
ID Date Forecast
1 June, 2012 5
2 June, 2012 3
3 June, 2012 2
1 July, 2012 7
2 July, 2012 9
3 July, 2012 1

Sorry if I'm not too clear in what I need!
 

A:Utilizing Date/Time in Query to combine tables

You can "Join" the 2 tables using the date fields, that will show all the values that match from both tables.
You don't actually show the end result you want.
 

Read other 3 answers
RELEVANCY SCORE 85.2

Hi!I am trying to make a simple inventory program in Access 2007.I have tables for purchases,sales and a master table of the various items names.I have made a look up for item names in the purchases and sales tables from the master table of items so that there is no issue of spelling mistakes and to make it easier to enter item names.I am trying to make a stock sheet record using a query to show me the balances of the various items at a particular date(current date)The master table has fields for opening stock,reorder level and of course item names.In the query design view I have put current date as a formula,linked opening stock,reorder level and item names to the master table.Then I put in a formula to calculate the current balance of each item.The formula is thus:Take the opening stock from the master table add the quantity purchased from the purchases table and deduct the quantity sold from the sales table for each item.But when I run the query it shows me only the items and their current balance quantities for those items which appear in all three tables(ie.master table,purchases and sales table).Any item appearing in any one of the tables but not in the other two tables is not shown at all.So this means that non or slow moving items don`t appear when the query is run.Neither do items that have been purchased and not sold or vice versa.Please how do I solve this problem so that I show correct stock balances for all items at any time?

A:How to make a customised query to link same fields in different tables

You need to use an OUTER JOIN to get all the data.

Here is an MS article about MS Access Outer Join:
Creating an outer join query in Access - Access

It seems Access doesn't support a FULL OUTER JOIN, and I think that is what you want.
This article explains a workaround for this:
Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access ? DatabaseJournal.com

You can also Google for ms access outer join code if you want to find more articles.

hth,
David

Read other 2 answers
RELEVANCY SCORE 82

Hi everyone
Wondering if anyone could help. I have a table which displays workers and certificates. Is there a way that i could display the worker and his relevant certificates in a form without displaying the ones that they havent got? I have the relevant certificates in a yes-no format...
Kindest regards
 

A:Solved: Access query

Read other 16 answers
RELEVANCY SCORE 82

Hi Guy's I am struggeling to make a query work correctly. I have a table with project records and a sub table with multiple comments for each project. I need to make a report by project types but first I need to get my query to return each project record, but ONLY with the newest comment. In other words it is now returning a record for every comment (You can see if you run it)

Thanks, Joe
 

A:Solved: Access Query

Read other 12 answers
RELEVANCY SCORE 82

I have a query (called Ancient Art A) where i have run to see what children are on a specific course. once i run this query i want to be able to transfer a few of the students to a course called (Ancient Art B). but when i change the course title for one person to ancient art b (in the query) all the students in ancient art A get transfered into ancient art b? i know i can manually go into the records and change them manually but i thought it was possible this way?

thanks
 

A:Solved: Access Query help

Read other 6 answers
RELEVANCY SCORE 82

I am trying to create an Access Query that will find duplicates within a field in a table.

In this field, there will be entries like ABC123 or ABC123-03/03/2009

I need the duplicate query to regonize that the two entries above are the same.

Here is what I have done, but cannot get it to work properly....

First

Field: TNumber
Table: Test

Field: Left(TNumber,6)

Duplicate Query

Field: TNumber Field: TNumber
Table: First Query
Total: First

Field: NumberOfDups: TNumber
Table: First Query
Total: Count
Criteria: >1

Field: TNumber
Table: First Query
Total: GroupBy

I have purposely entered duplicate values within the table to test the duplicate query. It does not indicate that there is any duplicated values within the table.
 

A:Solved: Access Query

Read other 16 answers
RELEVANCY SCORE 82

I have a connected table and form where the user can input branches and their revenues by month. What I want to do is run a query that will display the branch w/ the highest revenue for a particular month and then use the top ranked branch revenue to compare with all the other branches revenue for that month. To delve in a little further I need the following...if the branch w/ the top ranked revenue is 60 then I need the query to make a field equal to this number. Then I would take 60 and divide it by 3 so I can compare the revenues in 3 tiers. So the main question I'd want to ask the query is if branch revenue is between 1 and 20 then rank would be "tier 1", if branch revenue is between 21 and 40 then rank field will output "tier 2", and if revenue is between 41 and 60 then rank field will output "tier 3". I need something that is automatic...meaning where I would have to enter in the parameter values myself b/c if the use adds in a branch revenue that is 61...it means I'd have to reset my parameters each time there is a change.

I've tried a few methods including the IIf function but since I'm not very familiar w/ the QBE...nothing has worked to date.

So...can anyone help??? Thanks.
 

A:Solved: Access query help

zhouma, welcome to the Forum.
I think you will need 2 queries to get this to work. The first would establish the highest ranked Branch.
The second would then use that query and the table to compare the other branches to that one.
I will see if I can create the queries for you.
What are the field names of the Branch and Revenue?
 

Read other 3 answers
RELEVANCY SCORE 82

I want to be able to use the "QTY" column, so I can query different part numbers at different times. I would like the query to work with the reports I have in the program, so I can print labels.
The query I created does not seem to work the way I want it too. It does not show the labels with the pictures.

Looking for help.

Mike
I can email the zipped file.
 

A:Solved: Access Help on a Query

Read other 13 answers
RELEVANCY SCORE 82

hello:

I created a query so that I can create a report. Basically my query has all this fields:

Name 1 Due Received date

Name 2 Due Received date

Name 3 Due Received date

Basicaly what I want is that all fields with a value on name field and due is "YES" and received date is empty, the I want all the names to shoe on the report.

So on my query i put name =Is not null Due "yes" Received date is null

And it doesnt seem to work. Can anyone help
 

A:Solved: Access Query

Read other 7 answers
RELEVANCY SCORE 82

hi there i have a from containing check boxes if these check boxes are ticked then they add 5 pounds to a bill. does anyone know how to do this with a query.
 

A:Solved: access query

Read other 12 answers
RELEVANCY SCORE 82

Hi All,

Can any one help me in creating query in MS access for the below formula;

=IF(A2=A3,0,1)

Example:
The query has to match the first and second record if it matches then it will update as "0".
Can anyone tell how i can do that?

Thanks i advance.
Regards,

Vinoth
 

A:Solved: MS Access Query

Read other 16 answers
RELEVANCY SCORE 82

i am trying to create a query that changes the names of positions so that i can group them based on similarity. ie. A Sr. VP operations and a Sr. VP Human Resources are the considered the same "SENIOR EXEC", where as the VP operations & VP Human Resources are considered "SENIOR MGRS"

now here is what i have so far.

Position Area: IIf([Position]="GM" Or [Position]="Assistant General Manager","GM/AGM",IIf([Position]="SR*","Senior Exec","Senior Manager"))

all is ok, except for the following
IIf([Position]="SR*"
what i want it to do is recognize anything that begins with SR, so that i do not have to type in every position. this does not seem to work and anytime i have a SR position i get a result of "Senior Manager", where as it should result in "Senior Exec",

Now if this is not the simplest request for a early morning.
 

A:Solved: Access Query using IIF & *

Read other 8 answers
RELEVANCY SCORE 82

I am trying to allow users to look up a text string in a larger database. The problem is, there's two columns I need to look the number up in. One with special characters, one without.

My thoughts on how to do this were a query with an or statement, but I don't want the user to have to enter the text string twice. The other idea is a table, which links to both tables, but I'm not sure how to make that work and haven't tried.

This is the query I tried, but it doesn't work.

SELECT Item_Master_PROD_20120618.MFG_ITM_ID, Item_Master_PROD_20120618.[Clean MFG_ITM_ID], Item_Master_PROD_20120618.INV_ITEM_ID, Item_Master_PROD_20120618.[Item Description], Item_Master_PROD_20120618.[Category Cd Desc], Item_Master_PROD_20120618.[Manufacturer Name], Item_Master_PROD_20120618.MFG_ID, Item_Master_PROD_20120618.STD_UOM, Item_Master_PROD_20120618.[Vendor Price]
FROM Item_Master_PROD_20120618
GROUP BY Item_Master_PROD_20120618.MFG_ITM_ID, Item_Master_PROD_20120618.[Clean MFG_ITM_ID], Item_Master_PROD_20120618.INV_ITEM_ID, Item_Master_PROD_20120618.[Item Description], Item_Master_PROD_20120618.[Category Cd Desc], Item_Master_PROD_20120618.[Manufacturer Name], Item_Master_PROD_20120618.MFG_ID, Item_Master_PROD_20120618.STD_UOM, Item_Master_PROD_20120618.[Vendor Price]
HAVING ((((Item_Master_PROD_20120618.MFG_ITM_ID) OR (Item_Master_PROD_20120618.Clean MFG_ITM_ID)) =[Enter Manufacturer Catalog Number]));
 

A:Solved: Access Either Or Query

Nevermind, fixed my own problem. Amazing the clarity posting something gives.
 

Read other 2 answers
RELEVANCY SCORE 82

Hello all
Im trying to create a report for a database. The database is a address update db. We put info of the clients new address and when the our system was updated with the new address. This help prevent sending more then one letter to the same insured. Now what i want to do is have the report pull off the ones where we have not recieved the address update. Which is system updated field.i want the report to be by client. So if i type in microsoft it will bring up everyone that works for microsoft that has not given us a new address. i know how to create the query so it pulls up just microsoft but i can figure out how to bring up just the one that havent gien us the info yet. the fields are left blank when they havnet been update.
I hope this all makes since if someone can help me that would be great

Thanks
 

A:Solved: access query

If you use the Query By Example (QBE) editor window, include the "system updated" field and type Is Null as the Criterion.

HTH

chris.
 

Read other 3 answers
RELEVANCY SCORE 82

Ive attached an Access database that has a table and a query. The issue is that this database only has a Beginning Balance set to Period 1 and for all other Periods it has only a Period Change number.

I need to take the Period 1 Beginning Balance and Period 1 Period Change to make Period 2 Beginning Balance.

For Period 3 Beginning Balance I then need to take Period 2 Beginning Balance and Period 2 Period Change.
and so on....

So from the table I would need to have the following
Item # 11110
Beginning Balances
Period 1 - 76317.72
Period 2 - 77138.48
Period 3 - 76788.48
Period 4 - 78664.94
Period 5 - 68402.14
Period 6 - 41960.36
Period 7 - 20032.24

I tried to do this in a query but..... I can't figure out the best way to continue - I was only able to get Period 2.
 

A:Solved: Access - Query

Read other 16 answers
RELEVANCY SCORE 82

Im having difficutly trying to work out how to bring together 3 different types of infomation.
I have a Mobile Device list table called TblMobileDeviceID which be used to input new Mobile Devices as and when we buy them. I also have a TblMobileSimID, which again, when having a new number it is in the list of sim's so we know what we have.
I then have the User infomation. I have a table with all the Users with Office they are allocated too. TblUserID

Think i need to Create another table with the UserID, MobileDeviceID, SimID...however i just dont know.

I already have the Sim and Device info in my Tables. I want to allocate Jo Bloggs with a particular mobile Device from my list, and Sim. How do i do it?

Would be greatful for some guidence please
 

A:Solved: Access Query Help

Read other 16 answers
RELEVANCY SCORE 82

I need to find records where a field's value is "", or " " or perhaps NULL (i don't know how Access treats those values).

For example, this works to find records where the field's value is "comp":

Code:
SELECT * FROM tablename where item = "comp";
However, the following don't work to find records where the field's value is empty, blank, or perhaps NULL:

Code:

SELECT * FROM tablename where item = "";
SELECT * FROM tablename where item isnull;
SELECT * FROM tablename where item = NULL;

Those queries produce an empty result set.
 

A:Solved: MS Access Query

The correct answer is

Code:
SELECT * FROM tablename where item is null;

 

Read other 1 answers
RELEVANCY SCORE 81.6

I'm allowing the user to choose the key from Select combo - my bound column is the key.
I want a second section of the form to go and fetch ALL occurances from another table that has the key from my first step as part of the combined key.
I've tried a subform/subreport (which I felt would be the ideal - I only want to display)
But nothing comes up.
I've also tried to use a combo box and populate it with a requery, but no luck there either. So now I've decided that my error is in the query I use for the combo box
SELECT Tenders.[TENDER NO], Tenders.TENDERDESCRIPTION, Resolutions.[RESOLUTION NO], Resolutions.[MEETING DATE], Resolutions.RECOMMENDATION, Resolutions.[TENDER VALUE]
FROM Resolutions INNER JOIN Tenders ON Resolutions.[Tender No] = Tenders.[tender no]
WHERE Tenders.[tender no] = Forms![2nd Copy of Find Tender Number and Resolutions]!Combo25
ORDER BY tenders.[tenderdescription], resolutions.[resolution no], resolutions.[meeting date];
I want tender no to be the bound column in combo25 (in vb I'd put combo25.column(0)
How do I put that in a query?
 

A:Solved: Wanting to access bound column in query access sql

Read other 13 answers
RELEVANCY SCORE 81.2

I have a table "tblBooking" with the following fields Booking ID, Table ID, Staff ID, Date Booking Taken, Date Booking For, Time Booking For, Number Of Seats, Customer ID.

I need to create two queries which will allow me to create two graphs.

I need to make a graph that will group all bookings by the hour and then count how many bookings are in that time range to create a graph. e.g. count number of bookings between 7-8, 8-9, 9-10, 10-11, 12,13 etc. and allow to create graph

The second query is similar to above but instead of the time and group by hour I wanted to work out the busiest month

Any help would be great

THANKS
STUART

[email protected]
 

A:Solved: Urgent: Access Query

Please Really Do Need Help!!!!!!!!!
 

Read other 2 answers
RELEVANCY SCORE 81.2

Hi,
I've been using Access 2000 to calculate individual and club scores in an Annual League.
Each year until now I have been able to create a select query along the following Lines: -

Field| First Name | Last Name | Club | Points |
Table| League 08 |League 08 |League 08| League 08|
Total | Group By |Group By | Group By | Sum|
Sort | | | | Descending|
Show| Tick | Tick | Tick | Tick |

This year I have lost the ability to select 'Total & Group By'.
All the results are in the same tables in the 2009 database as they are in the previous year

Please can you tell me what I have forgotten or am doing wrong?
 

A:Solved: Access 200 Select Query

With the Query in design view, on the main menu>View is Totals not on the list?
Can you post the a zipped copy of the databse for us to look at?
 

Read other 3 answers
RELEVANCY SCORE 81.2

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

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

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

Thx
 

A:Solved: Access Query Y/N Field

Read other 7 answers
RELEVANCY SCORE 81.2

I have a table using Date Of Birth but I want to run a search to see if anybody in my db has a birthday coming up in one month.

How do i do this

THANKS
STUART
 

A:Solved: Access Query Using Birthdate

Read other 10 answers
RELEVANCY SCORE 81.2

I'm developing a database for work and need to write a query to search a table and pull up the info in the table.
Right now I have:
SELECT Catalogue_Number, CD_Name, CD_Description
FROM CD_Table
WHERE CD_Name like SearchBox;

As it is when I hit the query button it pops up a searchbox and I have to enter in the exact info I'm looking for. I'd like to just be able to enter in part of the cd_name (Ex: Instead of "Label Factory Deluxe", I'd like to type in "Label" and get any Record that has Label in the CD_Name field

Any help would be appreciated.
 

A:Solved: Access Query question

Read other 7 answers