I'm trying to create a query that can sum values of different fields in different tables...Can I sum values of a field and put the result into another field in different table?
I am trying to use a join query to join two tables in order to create a form. I have done this on two other occasions in my database with no issues. Now it is only pulling the ID and I want it to pull the description. I have looked at the SQL view for the other queries that are similar as well as the design view and all of them are set up the same way with the exception that this new join query is pulling CertID rather than the CertDescription.
Is there any reason why it would be doing that? Is there an error that I could possibly be doing? It seems according to my notes that I have created all the queries the same but this last one does not pull the same info as the other queries.
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A Field X Field Y 1 a 1 b 1 c 1 d 2 a 2 b 2 c
I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.
I am hoping to do a report all my queries in my Database.
I have managed to use some sql code to list the queries which is the following:
SELECT MSYSOBJECTS.Name FROM MSYSOBJECTS WHERE (((MSYSOBJECTS.Type)=5) AND ((Left([NAME],1))<>"~"));
But i cant seem to get any further i would also like to include the creation date, the description if any, the tables associated with each query, as i know i have a good few queries which maybe duplicates i just want to clean it up.
I have a rotating number of tables that are created from excel spreadsheets that are imported. The Tables will change, but when they are there I need to be able to create a query that will merge them all together so I can run one query against all the tables. In SQL I know you can use a * to say Select * From Table_1
Is there a syntax for the From portion so I can say:
Select * From * (AKA all the tables in the Database)?
I have searched for a wildcard for the FROM statement that works like in the select statement but have been unsuccessful at finding an answer. Can anybody help? I'd list the Tables in the from Statement but there are 266 of them. Unless someone knows how to say:
Select * From All tables in a folder with 266 excel spreadsheets
As I usually concentrate on building the tables and fields in my initial stage, and do some experimenting to analyze the relationship.
However, when I'm done, I find it quite a hassle to go manually through the tables to add description to each field for documenting.
What I would like to do is create a query that will list all fields and its description. Now, I have found codes to retrieve either table or fields properties, including the description property, but am kind of stumped on how I can make it a SQL statement so I can create a temporary query, which will be pretty be one time thing, that can read all fields' description and allow me to type in it, save it.
If that's not possible, I suppose I could write code, using Allen Browne's example, that would loop through the fields' description, debugging to the immediate window and prompting me if I would like to add something to the blank description. However, this isn't exactly greatest as I need to see all fields so I can be sure I'm giving good descriptions.
I figured someone may have had this same problem and maybe came up with a solution?
I have done smoe baisc queries to sort, filter and report on data.How do I create a nested query where I don't repeat all the information across the query results line? Is there a way I can build a nested query to give all the SMCode codes associated with each appointment in one column separated by a coma?I have a table with appointments (APPTS):
RecID ADate ATime Durantion PVID Desc Case#
Then I have a table with Orders related to these appointments (ORDERS):
RecID Case# SMCode SMDate
Here's my query:
SELECT DISTINCTROW Appts.RecID, Appts.ADate, Appts.ATime, Appts.Durantion, Appts.PVID, Appts.Desc, Orders.SMCode FROM Appts INNER JOIN Orders ON Appts.[Case#] = Orders.[Case#];
I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .
I also have a table that list the items and on that table the column ItemID is the key.I want to create a query that will list all items ordered on a PO# and how many have been received so far. With that I will calculate the outstanding qty. I want to see:
I have created a query for telling me the dates when I need to check driving licenses from my drivers.
I have also made a Yes/No check box for when they are 'Live' employees and another Yes/No check box for when they have left. I need to exclude the records from the query with the 'Left Employment' box checked ('Live' box un checked).
I need to keep the record for 12 months after they leave so I cant delete it.
I am trying to create a query to append new records from my NEW database into my old excel database.....
The old DB has 4-5 extra tabs that the NEW database does not have so when I append, in those extra columns the new database will just have blank records since the column doesn't exist.
Usually I do a append query in design view. but sometimes it gets funny because it creates duplicates...
How would I go about it, so its quicker and persistent like creating a macro excel.
I am trying to create a query in Access 2010 for records that don't match based on the following criteria. I have two tables with identical ID's and I need to do a comparison on the amount field between both tables and only return the records from one table displaying all fields plus the amount fields that do not equal. I tried this in the wizard unsuccessfully.
I am creating a database for users that no nothing about access nor do they want to learn. So the database needs to be as simple as possible.
This being the case I would like to avoid users from creating/editing querys.
I would like to have a form that would run the append query. The query would have a date column and a status column that the query would filter.
The query would have a criteria for status that would equal to "BACKED OUT" and the date would need to link to the two text fields on the form. My problem is writing the VBA code to make the query append in the background.
For sake of argument: Query = qryappend Form = frmappend Table = tblappend
I am trying to do a lookup from a field from one of my databases (DB1) onto another field in (DB2).... I have been using excel to do vlookups but a report that I do weekly I do atleast 7 lookups for the past weeks... So I am trying to find a way in access to do lookups and save those lookups each time i run the report... Do I have to create the query and make the ID the primary key on both the databases and then create a relationship?
I used the wizard to create a find duplicates query. I know for a fact that 15 records are duplicates. I'm getting over 300 records. When I dumped the data into Excel and did a pivot table to count the number of records by account number only 15 had a total count of 2. All others only had 1. Why are the ones with no duplicates showing? I just want to see the 15.
I can create a select query with a combo box in it by changing the property of one of the fields - making the display control to combo box and adding the values. No problem there.
What I want to do is to be able to create a query dynamically from vba using a CreateQueryDef statement.
The problem is that you can only pass an SQL string to CreateQueryDef, and the combo box values are not part of the SQL code, but are saved with the query (presumably the same way column layout gets saved).
Is there a way to create a query with VBA and include non-SQL layout criteria so I can have combo boxes?
I need to create a form that using combo boxes selects a product-size-quantity, and then calculates total price.I asume that I create a query to make the calculation from the form, but for the life of me, I can't fathom out how to do it.
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
I am trying to create a query which will return records which are from a specific month of the previous year...
To better explain:
If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's probably really simple.
But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.