Over 1 million tech questions and answers.

Solved: Access Make Table Query and Select Query

Q: Solved: Access Make Table Query and Select Query

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.

RELEVANCY SCORE 200
Preferred Solution: Solved: Access Make Table Query and Select 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 Make Table Query and Select Query

Read other 7 answers
RELEVANCY SCORE 135.2

I have a Make Table query that when I run it and view the table in the query it shows only the data I want. But when I go and open the table that theis query made, there are entries that should not be there.

It is putting in rows with blank fields, I am not sure how to stop it. I have tired to put something in the query for that field but I get back the error "Invalid Use of Null"

If I could put in the criteria that worked I would be all set.

Any ideas?
 

A:Solved: Access (Make Table) Query Results Returned not Same as Table

Read other 6 answers
RELEVANCY SCORE 130.4

I currently have three table joined in a database which have worked providing queries, reports. However, I cannot get the query make a table consisting of all three tables. What's wrong?
 

A:Solved: make table query in access 2007

Read other 16 answers
RELEVANCY SCORE 126.4

Hi all,

I have been struggling for a few days to build a functioning query. I actually managed to make it work for a smaller set of data but I'm using the following function:

RankOfDup: (SELECT Count(*) FROM [Table] WHERE [field1]=[table].[field1] And [field2] >= [table].[field2])

which I believe slows my query, if I do select query it runs for around 2 minutes but if I try to make table it never executes.

The reason I need make query is that after this I need to create Crosstab query that basically turns the ranks from rows into columns. I tried to create the crosstab using the select query with the RankOfDub but it gives an error that the the database doesn't recognise a field.

Any ideas how I can make this work for large set of data, ie rank the rows and turn them into crosstab?

If anything is unclear please ask questions and I will explain.

Thank you very much, I hope someone can help!
 

A:Solved: Access 2003: Make-table query with around 2 ml records taking too long

Read other 16 answers
RELEVANCY SCORE 124.4

I have created a select query where I am attempting to update/revise data to the Table in the datasheet view. This process seems to work only when running the query is first run. When changing criteria of the query and re-running the query, the fields are locked and I can not make any changes. Why is this? Is there a better way to make Table updates rather than a select query?
 

A:Update data in Table via Select Query wit Access 2003

Read other 8 answers
RELEVANCY SCORE 122.8

Let me start by saying other than how to spell it, I know nothing about Access. Someone just asked me if there is a Make Query Table in Access. Is this something that would depend on the database, or is this a general feature/option in Access.

The version is Access 97.

Thanks guys!
 

A:Make Query Table-Access 97

Sure, it's there.

A make-table query is like any other query in that it provides information based on your criteria.

The difference is that, unlike a select query, which gives you a temporary datasheet view, a make-table query makes a brand new table with your data in it.

To create a make-table query, you create a regular (select) query, then hit Query-Make table on the menus.

Knowing how to spell it is a great deal more than some know!!
 

Read other 2 answers
RELEVANCY SCORE 120.4

I have several queries that I run daily to update sales, shipments, etc. How do I set up a macro to run a make table query and then export the table to another access database? (The query deletes the records already in the table before re-populating.) If possible, I would prefer to not open the query, just run it. Please know that I am a very new user of Access, so I may need step-by-step examples.
Thanks for your help!
 

A:Access 2010 Macro to run make table query

Are you sure that you need a maketable query?
Won't an append or update query work?
Also why do you then need to export the table, can't this be done in the other database?
 

Read other 3 answers
RELEVANCY SCORE 110

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 109.6

hello,
I have created a make table via a parameter query. The parameters are defined correctly and I am prompted each time to enter them. the DeptNo does work as my query is filtered based on a value entered, but when I enter the tablename parameter, the make table query always saves the table as @tablename, and not any text i enter for the @tablename like "mynewtable" or something. Here is the SQL code.
Any ideas? Ignore the complexity of the query, my main concern is being able to loop through some VBA code and continually change the tablename each time (along with the DeptNo. I think this will save me time vs creating a new sql statement each time and doing a docmd.runsql ...
Thanks.

PARAMETERS DeptNo Text ( 255 ), [@TableName] Text ( 255 );
SELECT AUMaster.Lawson, ChgMstr.DEPT, ChgMstr.[CHG CD], ChgMstr.[REVENUE DESCRIPTION], [Prior and Curr Year Revenue - Qty].Price, ChargeCodeProfile.[Count per Charge] AS Weight, [Prior and Curr Year Revenue - Qty].[2006 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2006 Projected Revenue], [Prior and Curr Year Revenue - Qty].[2007 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2007 Projected Revenue] INTO [@TableName]
FROM ((ChgMstr LEFT JOIN [Prior and Curr Year Revenue - Qty] ON ChgMstr.[CHG CD] = [Prior and Curr Year Revenue - Qty].ChargeCode) LEFT JOIN ChargeCodeProfile ON ChgMstr.[CHG CD] = ChargeCodeProfile.[CHG CD]) LEFT JOIN AUMaster ON ChgMstr.DEPT = AUMaster.Affinity
WHERE (((ChgMstr.DEPT)=[D... Read more

A:Parameter Make Table Query-passing the table name - HELP!

Read other 10 answers
RELEVANCY SCORE 109.2

I'd like to concatenate two Fields together but coming into a problem if the second one is Null.
Field1, Field2

I would like it to look like
Field1.Field2 (only if field 2 is not null)

In my Field line of the Query.
ExtValue: [Line Item Parent] & Nz([ext],"." & [ext],'') which says there are too many arguments.
Thank you for the helphelp.
 

A:Solved: Query Table Extended in Access

Try this

ExtValue: iif(not isnull([ext]), [Line Item Parent] & "." & [ext], [Line Item Parent])

That will check if ext is Null, if it isn't it should add the "." & [ext], if it is null it will just use the [Line Item Parent].
 

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

Hi,
I want to run a module and what I need is the way to show only the available queries in an inputbox or similar:

example:
Function SingleQuery()
Dim bla bla bla
Dim strQuery as string

strQuery = Inputbox("Select a query","TITLE", < here a pick-list of available queries >)

after this the rest of the code works but I have to type the query name myself
I'm sure its simple, but just can't find it.
Thanks.

Hans
 

A:Solved: Ms Access 2003 select a query from pick-list

You can set a Combo or List Box Row Source to the Query Names of the AllQueries Collection using VBA. If you put AllQueries Collection in to the VBA Editor's Help it shows how to loop through the Collection and print the name of each query.
You would need to loop through the collection and assign the names found there to an SQL string and then set the Combo's Record Source to the SQL string.
 

Read other 3 answers
RELEVANCY SCORE 105.6

Hello,
Long time since I posted here.

Basically, I have an Access database which calculates the cost of jobs by multiplying the number of hours worked by the Labour Rate.
The labour rate changes over time, but we need to keep historical data - i.e. When the labour rate changes I can't just go and change it in the query, I have to amend the formula so it uses the correct labour rate for jobs done on certain dates.

At present, in my query (CALC) I'm using nested Iifs, as follows (note: dates are in UK format):

LabourCost: IIf([JobDate]>=#01/09/2005# And [JobDate]<#01/04/2007#,[HoursWorked]*9.52,IIf([JobDate]>=#01/04/2007# And [JobDate]<#01/06/2007#,[HoursWorked]*9.82,IIf([JobDate]>=#01/06/2007# And [JobDate]<#01/09/2007#,[HoursWorked]*9.85,IIf([JobDate]>=#01/09/2007# And [JobDate]<#01/01/2008#,[HoursWorked]*9.93,IIf([JobDate]>=#01/01/2008# And [JobDate]<#24/01/2008#,[HoursWorked]*11.62,IIf([JobDate]>=#24/01/2008# And [JobDate]<#01/04/2008#,[HoursWorked]*11.68,[HoursWorked]*11.73))))))

The liklihood is that these labour rate changes are going to become more frequent, so I want to scrap the Iifs and use a table instead.

I've already set up a (unlinked) table called LabourRateStd with the following fields:
ID (autonumber)
Date1 (Date)
Date2 (Date)
LabourRate (currency)

The dates and the values have been entered as per the Iif statement above. For the final Date 2, I've just entered a date way off in the future.

So, wha... Read more

A:Solved: Access query: check date against range in table and return value???

Read other 16 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 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 100.8

Im having a problem designing a query that gives me the desired results.

This is what I have a select query with multiple criteria, which involve dates. Im looking for the query to select the oldest (dates) for each criteria. I also need 5 of the oldest which meet the specified criteria. Ive selected with a grouping of max for the date field and that seemed to work but only gave me 1 record (the oldest) for each criteria. Unfortunately I need the oldest 5. Ive tried select top 5 from SQL view but it only give me 5 records. How can I tell it to give me 5 records for each criteria or max 5?
 

A:Access 97 Select Query Help!!!!

This might not work but something to try. When you go to SQL view, does this return only the first 5 in the table? If so, could you try sorting the data at the same time; cause this would default the oldest to be grouped.

Another thing to try would be setting a parameter so you could enter the date and then anything less than that would appear.

Sorry not much on Access help, Downwithyourbadself or Dreamboat may be able to help more.
 

Read other 3 answers
RELEVANCY SCORE 99.6

I have two tables. One with Customers down the side, and billcodes across the top. The table is populated by billcode quantities.

The second has table has two columns: Billcode and Rate. The table maps billcodes to their rates.

I want to construct a query that will multiply each billcode quantity by its rate to get revenue. How might this be constructed in Access?

Visually, here are the two tables:

Table 1:
| BC1 | BC2 |
CustA | 5 | 10 |
CustB | 3 | 8 |

Table 2:
Billcode | Rate |
BC1 | $1 |
BC2 | $2 |

Desired result:
| BC1 | BC2 |
CustA | 5 * $1 | 10 * $2 |
CustB | 3 * $1 | 8 * $2 |
 

A:Access query with table reference

ChipT, welcome to the Forum.
What you have described is what you do.
Create a Query for table 1 with the Customers and Bill codes.
Add table 2.
Add a new column, in the heading put the following
resultBC1: tablename1.[BC1] * tablename2.[BC1]
Add a new column, in the heading put the following
resultBC2: tablename1.[BC2] * tablename2.[BC2]

where tablename is the actual names of the 2 tables.

This Table structure is OK for data entry but far from ideal design wise. If you have to add a BC3 later you have to add fields to tables, queries, forms & reports.
 

Read other 1 answers
RELEVANCY SCORE 99.6

I have a table called tbl ACOP. The ACOP can be applicable to persons in one or more sections.

I tried setting up the table as follows,
ACOP Number
ACOP Description
Review Date
Amend Date
Applicable To 1
Applicable To 2
Applicable To 3
Applicable To 4

However that has not worked for me, can anyone advise on the most approriate way to make this worked, ultimately I want to run a query against what a person should have (according to the section(s) the ACOP is applicable to!) and what the person has.

Any help would be much appreciated!

Thanks
 

A:MS Access 2000: Table Set Up & Query

Can you explain a bit more about the "persons", are they in another table?
What are Applicable 1 to 4?
 

Read other 1 answers
RELEVANCY SCORE 99.6

I have one table and one query that produce the EXACT same fields but with different values. How can I create a selection query that gets both tables into the query without duplicating data?

Example:

Table 1:
<table border = 1>
<tr>
<td><b>ID</b></td><td><b>Name</b></td>
</tr>
<tr>
<td>1</td><td>John</td>
</tr>
<tr>
<td>2</td><td>Bill</td>
</tr>
<tr>
<td>3</td><td>Dan</td>
</tr>
<tr>
<td>4</td><td>Mark</td>
</tr>
</table>
Table 2:
<table border = 1>
<tr>
<td><b>ID</b></td><td><b>Name</b></td>
</tr>
<tr>
<td>51</td><td>Eric</td>
</tr>
<tr>
<td>52</td><td>Fred</td>
</tr>
<tr>
<td>53</td><td>Rob</td>
</tr>
<tr>
<td>54</td><td>Paul</td>
</tr>
</table>
And I want to make Table 3 that should result in this (as a select query not a Table):
<table border = 1>
<tr>
<td><b>ID</b></td><td><b>Name</b></td>
</tr>
<tr>
<td>1</td><td>John</td>
</tr>
<tr>
<td>2</td><td>Bill</td>
</tr>
<tr>
<td>3</td><td>Dan... Read more

A:Access 2000 -> Select Query

Crosstab query...
 

Read other 3 answers
RELEVANCY SCORE 99.6

I'm attempting to clean-up the data in table. My problem is the person that entered the information placed two rows of data in a single field.

In other words, the field is for a "Box Number" but the person placed two box numbers in the field. The way they did it is similar to typing a box number, then pressing "Enter" on your keyboard and typing the second box number.

Is there a select query that I can run in which I can pull all of the records that have that "enter" in them?
 

A:Access 2K: Select Query Criteria

Describe your data more, are the box numbers a constant length? If so a combination IF with LEN and Left or Right function will help in the criteria of an Update query.

With a constant of 4

= iif(LEN(YourField] > 4,LEFT(YourField],4),[YourField]))

And if you just want to group them together as q query criteria just

make a field that is Expr1:Len([yourfield]) and in the criteria of >4

Again I am assuming you have a constant Box number length, If you want to use an Update query I may have to explain what I mean by the first expression
also you might want to check your database options on the Keyboard tab, in the top left for "Move after enter" it must be checked for "Don't move" You might want to change it.
 

Read other 3 answers
RELEVANCY SCORE 98.8

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 10 Home, 64 bit
Processor: Intel(R) Core(TM) i3-3227U CPU @ 1.90GHz, Intel64 Family 6 Model 58 Stepping 9
Processor Count: 4
RAM: 3977 Mb
Graphics Card: Intel(R) HD Graphics 4000, -2043 Mb
Hard Drives: C: 454 GB (412 GB Free);
Motherboard: Dell Inc., 033MX4
Antivirus: Windows Defender, Enabled and Updated
MS Access Pro 2016

I run the same query routinely. I use my basic database table and another. The 2nd table is created for this query and then deleted once I have run the query. I then create a new table (new data) and run the query to obtain this info. I do this manually at least 4 or 5 times daily.
The 2nd table data put into the query is 2 items....name and location which is the table; >> does that easily. The basic database has over 60 columns and I use 25 of them in the query. Scrolling, highlighting, and clicking > for each item is time consuming and ripe for errors.
Can't I save most of this, add or change a bit, and be a lot more efficient while saving a bunch of time and potential errors?
Thanks for helping!
 

Read other answers
RELEVANCY SCORE 98.8

I am using a query in Access to clean up some unneeded text from a table, then create a new table with the cleaned up text. In addition, I need to add a column to this new table that is boolean format defaulting to a "no" value. I have successfully created this query except for the new boolean column. I can get the new column to default to a value, either no or 0, but it is in text format. If anyone knows how to do this I would appreciate the help.
 

A:Access-Adding a field to a table with a query

This query will do it:

ALTER TABLE TableName ADD COLUMN NewColName bit
 

Read other 3 answers
RELEVANCY SCORE 98.8

hello
i am having a problem in Access 2002, it is probably quite simple, as i am very new to working with it.
the problem i am having is that i need to use a query to calculate a total, using 2 columns already in the table to fill a blank column in the same table.
i have tried using append and update but keep getting validation errors
in short i am trying to multiply column A times column B to fill column C.
i tried using a make table query to do this , but that makes another table, although it does do the calculation, but i need to keep the original table intact. i hope i have provided enough information to get some help, because i am running out of hair to pull out.

Thanx
MacataQ
 

A:query/table problem access 2002

Read other 7 answers
RELEVANCY SCORE 98.4

I have set up two queries in access. One lists all the time spent working on client files, in weekly totals. The other lists all the fees charged weekly. I have built a 3rd select query based on these first two, that pulls the information together, so I can build a report based on the third one. (I know, I could have done it in one query... but I already have the report built this way, and it works the same).

Each separate query works properly. But on the 3rd combo query, if there are weeks where no time is recorded for that client, it also drops the fees. In other words, it needs to see BOTH fee records AND time records. I would like it to still show the week, but with "0" as time spent. Right now, I'm having to go in and record a time entry with 0 minutes just so that it will work.

Perhaps it's a problem with the relationships? In the 3rd query, I have established a relationship between client #'s, and "week" (the week ending date) where it includes rows where joined fields are equal. If I try any of the other options, I get a message about ambiquous outer joins..... If I delete the join, I get a zillion records..

Oh why do I struggle so much with Access? Thanks for any help.
 

A:Access Select Query based on other Queries

Read other 14 answers
RELEVANCY SCORE 97.6

I want to auto-fill a Word 2010 Table from a Access 2010 Query. Here is what I mean:

I have created an Access 2010 database file called MasterTestEquip.accdb with a MasterTestEquipList" Query. Using the Word Merge function in Access, I created a 5-column, multi-row Table in Word 2010 with the following Column names:

Description, Manufacturer, Model, Identification #, Cal Due.

I want to be able to enter in the Identification # (e.g., 469-0068-601) and have the row auto-fill in the other information (Description, Manufacturer, Model & Cal Due). I have been trying to get the Mail Merge function to do this but I am stuck. I used Insert Merge Field and selected the appropriate name for each column. When I do a Preview, I can click on the right and left arrows and the information in the row steps through each one of my Identification numbers in my recipients list and the corresponding information is correct, but this is not what I want. I need to be able to manually enter in the Identification # and have the additional information auto-fill in. I also noticed that if I perform the above Insert Merge Field function in multiple rows that when I use the arrows to scroll through in one row, that all the rows change and show the exact same information. How do I make each row independent of each other and be able to manually type in the Identification # to get the rest of the information to auto-fill for each row/line?

This is a Test Equipment List form that I ... Read more

A:Auto-fill Word Table via Access Query

Read other 13 answers
RELEVANCY SCORE 97.6

You guys have been a huge help in the past with issues I've had with Software Licensing database I created in Access, so I'm hoping you can lend a hand with a new issue I'm running into:

i copied some data from a table to do some comparison and clean up in Excel. I realized a little late that I should have pulled more fields than I did... I have done the clean up and now I'd like to get the additional fields to finish the job. I've imported the cleaned up data as a new table (Table A) and want to run a query against the original table (Table B) to pull those additional fields. Not all of the records in Table A are present in Table B, and vice versa.

In the query design, I show Tables A and B and join the First Name, Last Name and Application fields. The Join Properties are to include all records from Table A and only those from Table B where the joined fields are equal. Table A has 1606 records, so my hope is to have the query return 1606 records with the missing fields added. I'm bringing in all of the fields from Table A and adding the missing fields from Table B.

The query returns 1899 records. I ofound one user who only has four records in Table A, three records in Table B, and the query is returning 12 records for them.

What am I doing wrong?
 

A:Access 2010 - Adding fields to a table using a query

Read other 16 answers
RELEVANCY SCORE 97.6

Hallo.

I'm writing an MS Access database (will end up being Access 2010, but is currently a .mdb), which is to be updated by Append and Update queries, and also exports various data. As far as possible, I'm trying to make everything one-click deals for the user.
The data source for the Append and Update queries is an Excel file that contains 20 columns of data. This file is sent back and forth from the user to an external company, who fill in data on existing rows, add new records and delete old ones.

If there are values in column A, and nothing in the other columns, this generally means this is a new record that needs appending to the database table. I've written the Append query, and that's operating fine.
If there are values in the other columns, this generally means this is an existing record with new info, so the database table needs to be updated.

I figured I'd add a macro to the OnClick event of a button on the switchboard, which would first fire the Append query, and then the Update query.

However, I don't know what I'm doing with the Update Query.
6 of the 20 columns in the Excel sheet are relevant, and I'm updating the Stays table in the database:
Excel > Access
Chassis > FullChassis
Final Distr Point > Dealer
Order Type > OrderType
Model Type > ModelType
Hold > Hold
Sold > VehicleSold

However...
The Dealer field in the Stays table is actually a lookup (autonumber) ID field. It looks up the DealerID f... Read more

A:UD query to update Access table from Excel data

Read other 9 answers
RELEVANCY SCORE 97.6

I am trying to count the total occurrences of an item within a subcategory of items in a query and then populate that number of occurrences in the row of the item in the table.

Here's an example.

Certification.......ComponentType...........ComponentTypeOccurrenceInCert
Cert1................Optional elearning.......................3
Cert1................Optional elearning.......................3
Cert1................Required Course.........................2
Cert1................Required eLearning.....................1
Cert1................Optional elearning.......................3
Cert1................Required Course.........................2
Cert2................Optional Course..........................1
Cert2................Required Course.........................2
Cert2................Required Course.........................2
Cert2................Optional elearning.......................1
So, in Cert1 there are three occurrences of Optional elearning, while in Cert2 there is one, and this is listed next to each one relative to the cert.

Other background...
-Using Access 2010.
-We cannot use the Totals row in the query as one of our field types is Memo and causes the Memo field to return a Chinese character (known glitch).
-The final product will be a exported Excel spreadsheet to upload into an LMS.

Thank you for any direction you can provide.
 

A:ACCESS - Count occurrences in query to add to table without totals row

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

I'm working on a database where a user can perform searches by dates and when they click my command button it saves the form's data into a table then runs an SQL sequence which creates a table. I need that table to delete the results and append when the next user performs a search. My code is as follows

'CurrentDb.Execute "Update tblFilter set InspectStart=#"
cboStartDate.Value = Null
cboEndDate.Value = Null
RunCommand acCmdSaveRecord
sql = "Select * into tblInspectionDateFilter from qryInspectionDateFilter"
DoCmd.RunSQL sql
'DoCmd.OpenQuery qryInspectionDateFilter, acNormal, acEdit'
Call MakeTables
DoCmd.OpenReport "MasterReport", acViewPreview
I am not sure how to go about it. Need help please. The qryInspectionDateFilter is the one I need to delete and append to. Thanks
 

A:Deleting a table and appending its records before a query in Access 2007

ladyvj, can I ask why you do it this way?
Why create a table to create another table?

To answer your actual question you just need to create a "Delete Query" for the table that you want to clear of records and run that at the appropriate time in your current VBA code
 

Read other 1 answers
RELEVANCY SCORE 95.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 93.2

Afternoon all,

I'm trying to use a Make Table query in Access to import email addresses into a new table, where I want to add 3 new fields (all Yes/No Select fields) so that the user can select what contacts are listed for "To:" "Cc:" and "Bcc:" in an email.

I read somewhere that I would have to use SQL to create the new fields, but I'm not so sure how I would do that. I checked Microsoft Office help, but my keyboards don't seem to yield any good/helpful results ("create new field make table query", "add fields to new table", "new table query add field", etc.).

Any ideas?

Thanks for your help!
 

A:How to create new fields for a "Make Table" query

Read other 6 answers
RELEVANCY SCORE 92.4

Hi there i wonder if you can help. I have a report which is based upon a query (Course Table used) that shows information on courses, like shown below. For the one you see below in red 'No of Students enrolled this is where i am hving the issue. i would like for the query to automatically count the number of students that are enrolled on that specific course. To make things a little more complex i have created status to evaluate whether the course is "Completed", "On Going", or "Completed' related to whether the Start and End date are greater less than or equal to todays date. So the count will have to involve this.

Course ID
Course Name
Start Date
End Date
Status
Number of Student Enrolled

The tables are linked together with

Student Table--------Student ID
Enrolled Details Table------------EnrollmentID,StudentID,CourseID
Course Table------------Course ID,CourseName,StartDate,EndDate
Status Field: Course Status: IIf(Now()>[Course Start Date] And Now()<[Course End Date],"Course on going",IIf(Now()<[Course End Date],"Course Not Started",IIf(Now()>[Course End Date],"Course Completed")))
Any help would be greatly appreciated. Thanks for your time and efforts.
 

Read other answers
RELEVANCY SCORE 92.4

I'm using ms access 2003 and i created a table that tracks safety violations at work. I have a column that allows me to select from a couple different statuses. I have 3, Corrected, not corrected, and void. I am trying to query the table so that i can see the complaints that are either marked not corrected, or have not been looked at yet and so the status is blank. So i need a query for "not corrected" and "blank". I'm new to visual basic and the coding that goes with it. Can someone please help me. Thanks.
 

A:Solved: how to query a blank table or list

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

I have 2 tables where the id in the main table that I need to match with the second table could appear in any one of 3 columns of the second table. I can get the data by created 2 more tables and making three separate queries. Is there any way to create a query that would merge the two tables no matter which column the matching data is in without create 3 separate tables and queries?
 

A:Solved: Excel Query where id matching 2nd table could be in 1 of three columns

After looking at the sql, I just added or statement for the where. I used the same syntax as matching one of the columns and then added an or and changed the column to match to.
 

Read other 1 answers
RELEVANCY SCORE 90.4

Hi

I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?

Thanks

Garry
 

A:Solved: Excel Pivot Table (based on query with formula)

Read other 16 answers
RELEVANCY SCORE 84.8

What do I add at the end of this 'field' formula that would select data if the dateofprocedure was in the first quarter or second quarter of the year?
Qtr One: DatePart("q",[DateofProcedure])
 

A:Select Query by

Read other 16 answers
RELEVANCY SCORE 84.4

I have 2 tables, Table A has 16000 records all addresses in a certain region (ID, address1, address2, postcode, mailout) and Table B has about 1100 records with all unique postcodes and the number needed for each postcode (Postcode, Needed (numeric field)).

I need a query that joins the tables on postcode and then randomly selects the number specified in the needed column and update mailout to yes (mailout is a yes/no field).

So something like:
UPDATE TableA SET Mailout=True
Where Key (SELECT TOP [TableB.Needed] INNER JOIN TableA.Postcode=TableB.Postcode
ORDER BY Rnd([ID]);

I know the above syntax is total rubbish, but consider it as pseudo code
 

A:Solved: Access Select Top Table Variable

Read other 16 answers
RELEVANCY SCORE 84

I have 2 tables that need to be updated when a command button is pressed on a form. The first table gets updated no problem but the second table will not update. I've tried to update the 2nd table through a query when the button is pressed but instead of it updating the 1 record that it should, It updates all the records in that table.
eg: The form has drop down list showing all the printers we use along with the location and type of cartridge. Those records get updated. The second table has the same listings in it but shows how many of each cartridge is in stock and what I would like it to do is to reduce the stock for a particular cartridge by 1. Instead, It shows the instock column all at -1. I'm obviously new at access 2000 and if someone could help me with this I would really appreciate it....Thanks, Hebbs
 

Read other answers
RELEVANCY SCORE 84

Hi,

Man, I am battling to describe my issue, forgive me if this sounds confusing!

I am trying to generate a pivot table in Excel that will show me how much GP each customer generates per month, on average. So my PT is summing all the gross profit per month per customer, as per the below screenshot:


I want to work out how much each customer makes us per month, but only looking at months where the customer has purchased. So ABC average would be 1797/1 = 1797 , ACTIONAID average would be 909/4 = 227.25, Administration would be -2097/5 = -419.4, etc. The issue I am running into when I make the calculation use averages, is that it averages based on the number of invoices the customer generate in the month. I need a way to use the total gross profit generated over the period, divided by the number of months the customer purchased in.

Hopefully this makes sense!
 

A:Pivot table query

Read other 7 answers
RELEVANCY SCORE 84

I want to run something by here to see if it will work in Access 2010.

I have a spreadsheet that I will attach. What this is used for is to see the last 3 months of the inspection % for companies we have for inspections.
As you can see at the top, the Month and year and then underneath that is the type of inspection and the threshold for each.
The threshold is the max % that an inspection can have before it gets an alert. Unsafe Driving anything over 65 would give an alert, in this case the numbers turn red and the background goes yellow.
So for, is this something that can be done with a form, query or report in access? If not, then I will continue to use the spreadsheet. I am trying to stream line this so that I dont have to go back and put in the same numbers over for the last two months.
Also, you will see on the spreadsheet there are different types of companies with different thresholds.
I have used this forum to help build this Database from scratch and had a lot of help here. I am slowly learning this and SQL as well. Still got a long way to go.
Again thanks for your help.
David
 

A:building a table and query

Read other 7 answers
RELEVANCY SCORE 82.8

Hi

was wondering if i could get some assistance to hopefully a simple query.

In one of my tables i have about 1000 records with the data type yes/no and the problem is I have reliased that I need to have as a default value a yes (or a tick in my form) but i dont want to go down each record filling in a tick. Is there any easier way to do this

You will have to bear with me I am pretty new to access.

I manage to set up a default value of yes but this will only work on new records entered, but the issue i have is I require it to be on my records which are already there.

Any help will be really appreciated.

Thanks

Ranj
 

A:MSACCESS Query. Table with yes/no datatype

Use an "Update" query. Use the query Wizard to generate an ordinary query and then in design view on the query's main menu click the "Query">"Update" and it wil be converted to an update query. In the yes/no field you need to set the "Update to" to "yes".
Save the Query and then from the list of Queries just double click your new update query.
 

Read other 1 answers
RELEVANCY SCORE 82.8

I have an access database that keeps track of participant in competitive events

Depending how a person places & how many participants they competed against in any given event determines how much money they win & are paid up to 4th place.

for 1st place winners they would receive 35% ( times the dollar figure shown below of how many participants they competed against)

for 2nd place 30% ( times the dollar figure shown below of how many participants they competed against)

for 3rd place 25% ( times the dollar figure shown below of how many participants they competed against)

and for 4th place 20% ( times the dollar figure shown below of how many participants they competed against)

1 participant = 25.00
2 participants = 30.00
3 participants = 35.00
4 participants = 40.00
5 participants = 45.00
6 participants = 50.00
7 participants = 55.00
and increments by $5.00 so on and so on

I'v created a "PARTICIPANTS" table that stores all the values I require above using "participants" as an auto number, "winnings" as the dollar amount listed above that increments by $5 per participant then created a Query based on the Table for the dollar amounts using "Expr:" made for each "1st", "2nd", "3rd" and "4th" place amounts.

I have a form created with the competetors first name, event name, time etc

I want to have a drop down for "Placing" and " # of Participants in Event" and h... Read more

A:Need a formula to lookup a value in a query or table

Read other 16 answers
RELEVANCY SCORE 82.8

Hello guys, How I can manage to generate query for mysql database e.g

Code:

<select name="fly">
<option value="k1">kt-403
<option value="k2">kt-404
</select>

when user select 1 of these options, I will generate query on the base of this selection, How I can manage it.
e.g

Code:

mysql_query("select * from table where this='kt-403');
or something similar
 

A:generate query on the base of select...

Read other 9 answers