Queries :: Create Query To Do A Lookup From Another Database
May 28, 2014
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 am currently working on ODBC linked tables to our webend system. I need to create a join to a lookup table but I cant seem to get it to work as it only seems to show me results from one of the tables not both? Ive tried LEFT and RIGHT joins plus INNER JOIN.
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 wanted to create a form where you can select multiple values from the table "years" and on a button it would open a query displaying all the records in "students" in those years selected.
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields ContractName IssueDate SubSup DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
I am trying to create a statistics function on a database. The idea is that the user will enter a start and end date and either search for all records during that date range or select a client from a combo box and only view records for that client during the selected dates.
After doing quite a bit of searching, it seems that I should be using a wildcard in the criteria however I cannot get this to work. The code I have been playing with is:
Code: =Nz(Forms![Statistics]![ClientCombo] ,"*")
I have changed the "*" to a specific client number and if the combo box is left blank, results are shown for that client only and if a client is selected from the combo box then the selected client is shown. The only thing I cannot get it to do is show all entries if the combo box is left blank.
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
What I am wanting to do is to have columns at the end of my imported data that reference another table to tell me based on the queue what service type and centre it relates to.
I have attached 2 pictures one with table 1 and the other with table 2. Table one shows the last column 'SERVICE_TYPE' which I need to lookup from the reference table (aka table 2 - which has been attached).
I worked yesterday to create a database consisting of a table, multiple querries and a form. Today I went to open it and I got a different database I worked on months ago. The properties say it was created yesterday, but it is not the same database and I can not find the database I created yesterday.
I have a query which looks up records from a table, now what I would like to do is append the word All Projects to this query - is it possible, and if so how?
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
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
I wanted to create a field lookup with values that I specify, not on the table sheet, but on the form. User can click on a text box or combo box and can select a list of value that I specify, not values that are listed on a table but ones that I type in, in the form.
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 a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
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
