Over 1 million tech questions and answers.

Solved: Access Query, Query

Q: Solved: Access Query, Query

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.

RELEVANCY SCORE 200
Preferred Solution: Solved: Access Query, 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, Query

Read other 6 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'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 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 101.2

I have a previously designed Access DB and there is a query that I am trying to understand. It has several tables that are queried more than once in a single query. First how do I determine the actual table name? I can only see the alias name. Also what is the purpose of using the same table twice in a query?
 

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

It is normally to do calculations or comparisons on prior or next records. It is the one weekness of Access Queries, the Totals function has some good arithmetic calculations like average, Count Sum etc but manipulating previous records like add or subtract values from the current record it can't do. So it requires more advanced SQl. Which is apperas you have.
Have you looked at the query in SQL view?
It should use the word "As" to designate the alias.
 

Read other 2 answers
RELEVANCY SCORE 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 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

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

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

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

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

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

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

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

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

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

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

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

When combining two tables containing addresses with properly capitalized state abbreviations the resulting query does not maintain the anticipated formatting. How do I factor this into the sql statement?
 

A:Solved: Access Union Query

Read other 7 answers
RELEVANCY SCORE 81.2

I know I need to make a Max Aggregate query but I'm not sure how to do it. I am trying to make a query that will bring back ONLY the most recent record. There will be records made several times a day, so I think the best way to do it is make a Max Aggregate query of the Autonumber primary key?

HELP!!
 

A:Solved: Access: Aggregate Query

I've used a table from a project I'm working on as an example

The table name is [tLookup JC Stuff].

I want to retrieve fields [JC Area Acronym] and [JC Capability] for the record with
the Max of the key autonumber field [JC Gap No]

First I set up a query selecting the three fields named above. Then on the toolbar, I click on the greek capital sigma (see attached screen shot).

Now a new row called "Total" appears in the query design grid between the "Table" and "Sort" rows.

The total row will be filled in with "Group by" as default.

You want the Max [JC Gap No] so you enter "Max" instead of "Group by" in the Total row under [JC Gap No]. (There is a drop down list of Total row choices.)

You could run the query now but you wouldn't get what you want... Since the "Total" for [JC Area Acronym] and [JC Capability] are still both set to "Group by" you would end up retreiving the Max of [JC Gap No] for every unique combination of [JC Capability] and [JC Area Acromyn]. That's not what you want.

You want the fields associated with the global max [JC Gap No] for the whole table.

Since JC Gap No is a key autonumber field, you can enter "First" (or "Last") into the "Total" rows for [JC Area Acronym] and [JC Capability]. In this case, "First" (or "Last") equates to "only" because [JC Gap No] is a unique key field.

If [JC... Read more

Read other 2 answers
RELEVANCY SCORE 81.2

Hi,

While running the make table query i am getting this error as "Not enough space on temporary disk. (Error 3183)" . I dnt know what i need to do to resolve this issue. Actually i am having a database size around 1 gb. this query has to match the records from three tables and create a table in this database hope there is lot of space for this database to create that table.

Can you please anyone advise me for this issue.
 

A:Solved: Access Probelm in query

Read other 16 answers
RELEVANCY SCORE 81.2

Hi,

I have a basic CMS that is running on MS Access. I'm trying to convert my queries to run on a mysql server too, but I'm having some trouble with one of them (and my sql knowledge is limited).

Basically, I have two tables, one is called [articles] and the other is called [authors].

The [articles] table has the following fields:
- articles_id (autonumber)
- article_title
- author_id

The [authors] table has the following fields:
- id (autonumber)
- author_name

The author_id field in the articles table stores the id of the articles's author. There is no relationship drawn between the two tables.

So... In MS Access, the following SQL displays all the articles in the database along with the names of their authors:

SELECT article_id, article_title, (SELECT author.author_name FROM authors WHERE articles.author_id = authors.id) AS author_name FROM articles ORDER BY id

However, in mysql, only the article_id and article_title are returned. Any idea what I'm doing wrong?

Thanks.
 

A:Solved: Access to mySQL query

I have to ask the obvious question, why is there no relationship between the 2 tables when there obviously should be.
That is what "Relational" databases do.
 

Read other 3 answers
RELEVANCY SCORE 81.2

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:

IF
[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
RELEVANCY SCORE 81.2

I am trying to query records that are sixty days old or more based on an "arrival date" field and today's date. For example, if an arrival date is 12/12/2004 the query should return this record and any other that are more than 60 days old. Does anyone have a suggestion for the critera?
 

A:Solved: MS Access Query Question

Read other 8 answers
RELEVANCY SCORE 81.2

I am trying to create an update access query that will allocate values into a table according to parameters provided. So far simple enough.

The complication starts here: this table is an invoice table that may have one or more lines per invoice, in which case I need to generate a total price per invoice, by adding the amount of each line as a total, or having the amount of the unique line if the invoice has just one line.

I did this before with a recordset and and recordset.update command, but it is taking quite a long time, and I need to shorten the porocessing time on this issue.

Any ideas?

I value your opinions and I thank you in advance for the help.
 

A:Solved: update access query

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

I have a query but I need some help with it so that I can have the reports run correctly.
in the query, it has fields that are:
ProfileRunDate; Freq; NextRunDate

Now what I like to do, it might not be able to be done.
I need to be able to take the ProfileRunData and be able to have the NextRunDate come up when I put in the Freq.

Under Freq, I have m for monthly, q for quarterly, semi-annual and I think that some one told me to use d for days and use 180 days.

I have this formula for the NextRunDate: NextRundate: DateAdd("m",1,[ProfileRunDate]) but my question is, is there a way that I can have the same query set up so that when i put in the freq that it will generate a report for that time period?
Oh, I wanted to add the SQL view here. SELECT [M_DOT Profile Info Table].CoCode, [A_Co Info Table].[CoDOT#], [M_DOT Profile Info Table].[DOTPIN#], [A_Co Info Table].MCS150Update, DateAdd("yyyy",2,[MCS150Update]) AS MCS150Due, [M_DOT Profile Info Table].[MCC#], [M_DOT Profile Info Table].FreqRan, [M_DOT Profile Info Table].ProfileRunDate, DateAdd("m",1,[ProfileRunDate]) AS NextRundate, [M_DOT Profile Info Table].PortalUserID, [M_DOT Profile Info Table].PortalPassword, [M_DOT Profile Info Table].HowTransmited
FROM [M_DOT Profile Info Table] INNER JOIN [A_Co Info Table] ON [M_DOT Profile Info Table].CoCode = [A_Co Info Table].CoCode
WHERE ((([M_DOT Profile Info Table].CoCode) Like [Enter CoCode:] & "*") AND (([M_DOT Profile Info Table].F... Read more

A:Solved: Query in Access 2010

Read other 10 answers
RELEVANCY SCORE 81.2

Hi,

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

I have three fields in a table. ie....field1 field2 field3.
field1 rec1 has m123 data in it field2 rec1 has pa123 data in it
field1 rec2 has m233 data in it field2 rec2 has pa123 data in it
field1 rec3 has m223 data in it field2 rec3 has pa123 data in it

I'm would like to key in a propmt box the field value for field1 ie.... m123 and get all the records that have the same data in it for field2.
The result would show the following in the record set: m123 pa123
m233 pa123
m223 pa123
I'm having a problem with setting up the query properly or should I use macro or vba
thank you for any help
boaterjohn
 

A:Solved: access query issue

Read other 9 answers
RELEVANCY SCORE 81.2

I have a database that includes repeat names but with different dollar value's attached to each name. For example:
| Joe Smith | $10,925 |​| Joe Smith | $10,327 |​| Joe Smith | $10,725 |​
I want to create a query that will find a repeat name, identify the record with the highest $ and then delete the rest. Allowing me to have a quesry that shows no repeat names but keeps the record with the highest $.

Any suggestions?
 

A:Solved: Microsoft Access Query Help

Take a copy of the Table using the "Structure Only".
Make a Query of your original Table using Totals and group by the Name and set the Dollar value to Max.
Run the Query and it should give you the record you need.
Change the Query type to an Append Query and append the data to the new Table, save and run it.
If it allows you to run it, it will do what want.
 

Read other 3 answers
RELEVANCY SCORE 81.2

I am wondering how I could run an access query(s) from Excel via VBA? These are update and a make table queries.
 

A:Solved: Run an Access Query via Excel VBA

Try this
Code:
Sub Access_RunQuery()
Dim aa As Object
Set aa = CreateObject("Access.Application")
aa.OpenCurrentDatabase ("D:\Users\Jimmy\Documents\db1.mdb")
aa.CurrentDb.QueryDefs("Table_make_query").Execute
aa.Quit
End Sub
Jimmy
 

Read other 2 answers
RELEVANCY SCORE 81.2

I'm trying to write a query when you put the first few letters of a name or a loc. it brings up all names or loc that start with the coinciding letters. I can't figure out what to put down in the Criteria to do this
Any Help would be Appreciative.

Thanks
LoTek
 

A:{SOLVED} Access: Query Syntax

Read other 9 answers
RELEVANCY SCORE 81.2

Is it possible to have a table with a column for "name" and a column for "Prior Year Eval Rate" and then in a query, everytime a particular name shows up in the table it will find the "Prior Year Eval Rate" last recorded for that name? The example below is showing a table of information going to folks and I want to pull what the prior year eval rate was from the Eval Review Date of 1/1/2012.

Eval Packet to Manager qryDate to HRCOS ReasonEmp NameDept FromEval To ManagerEval Review DateEffective DateEval PayDateEval to Director DateCommentsRN Demographic SurveyPrior Year Eval Rate
Annual ReviewRhoda Meza61412/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewAnita Stowers66812/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewAmanda Machen76212/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewMichelle Breaud75512/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewTerra Garrison75512/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewAllison Stephens75512/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewNancy Brock75212/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewMelynda Williams75212/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewDaniel Harbin66812/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewSherri Moore621112/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewSallie Heard61212/3/20121/1/20132/3/20132/21/20131/28/2012
No

Annual ReviewSusan Kelley61112/3/... Read more

A:Solved: Access DB Question Regarding Query

Read other 13 answers
RELEVANCY SCORE 81.2

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

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

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

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

A:Solved: Access - Query Design

Read other 16 answers
RELEVANCY SCORE 81.2

I am trying to sort the results of a query alphabetically, but the default ascending sort returns parenthesis before it starts into the alphabet. It is a song list and the default sort lists songs like "(This Song) Starts With T" before songs that start with "A" like "A Complete Mess Of My List". Is there a workaround that will allow me to have it ignore the parenthesis?
 

A:Solved: Access - Sorting in a query

You could trim or replace that charachter in a seperate field in your query and have it sort that field.
 

Read other 3 answers
RELEVANCY SCORE 81.2

Hi,

I have a query called Query1, and it has 3 fields called Name, Volume. I input the values for Name and Volume fields myself. I need to calculate another field in this Query1 called Hour. But in order to calculate this field called Hour, I need to run this other query called QueryA that will need to get input values from field Volume of Query1. QueryA only output 1 solution everytime it is ran.

Name Volume Hour
Bob 2
John 3
David 4

To clarify it up...QueryA will take "2" and run the query and will give the result for Hour for Bob. Then QueryA will have to take in "3" for John and give his Hour.

I am pretty new to Access. I dont have any relationship going yet. I wonder if this is the problem. When I tried to do this...I got a popup asking for the value.

Can someone please help me. Thank you in advance.
 

A:Solved: Problem w/ Access Query!

Read other 13 answers
RELEVANCY SCORE 81.2

I have information in tables and when I query the tables some records do not appear. What causes that to happen and how do I solve it? I have written specific queries just to get that record and it does not even show up.
 

A:Solved: Access Query Question

Read other 8 answers
RELEVANCY SCORE 81.2

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

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

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

Here is my existing SQL view:

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

And here are my parameters for the record count:

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

A:Solved: Access 2003 query

Read other 16 answers
RELEVANCY SCORE 81.2

I am trying to get a query/or several queries to return the min and max values for each range that correspond to a value in another column. eg.

001 in
002 in
003 out
004 out
005 in
006 out
007 in
008 out
009 out
010 in

where the values I want are equal to min max in range = 'out'
min max
003 004
006 006
008 009

Any ideas?
 

A:Solved: Access Query - Range

Read other 10 answers