Can I use a query in Database A as a look-up table in Database B.
When I try File / Get External Data / Link Tables (in Database B), it only gives me a choice of Tables from Database A. Do I need to generate beforehand a physical Table using a MakeTable Query?
Any help or advice would be appreciated.
Hello all,
I am having trouble creating a query, here is the query i am having issues with
SELECT a.app_num, Max(b.app) AS Highest, a.prod_code
FROM table1 AS a LEFT JOIN table2 AS b ON a.prod_code = b.GSN
GROUP BY a.app_num, a.prod_code;
Now the query above works fine with out the "a.prod_code" as a column, as you can see i want the maximum value in b.app that relates to a.app_num, what i need to then do is select the b.prod_code the relates to the Max(b.app).
Sorry if this is not clear, please let me know and i will try to elaborate.
Many thanks
Tim
Hi there,
The aim of this (as part of a bigger solution) is to create an entry system where users can enter jobs in, assign a resource to the job and give the resource a cost. The conditions are that resources can be on more than 1 job, more than 1 resource can be on a job.
Obviously entering in resources manually is not a good solutioon (eg having 20 resource fields) as it is now. I want to create a relation between tables to achieve something better.
Let's say I had (for illustration sake) 2 tables, one which is a list of people and one which is a list of jobs. I want to be able to assign a person to a job for x amount of time (defined by a start date and end date in the job table) at cost y.
So we could have a field for the people table which would relate to the job id and a field for rate in the job db. However, people can be on more than 1 job (eg after doing one they do another) and there can be multiple people on a job (having one field for rate rendering it useless)
How can this be done in the most efficient way so that any job could be looked at historically and have the relevant rates etc? Would I have to create another table with rates relating to the job ID and person ID? This would allow for multiple resources per job and to have a rate assigned for each resource.
eg if person ID = 1 and job id = 1 then fetch rate etc and display in a list, and to calculate total cost simply sum all rates where job id = 1?
Thanks
I am pretty new to creating relationships between tables. I have a main form with subforms embedded. I tried running queries from the main form and they run just fine. I also run from the tables linked to the subforms just fine. however, when I try to run a query for a report that takes data from the main form and joins with data from the tables linked to the subforms all the data cells are blank. I have a linking table which is also blank. All the forms are linked by the same program ID number. Any ideas on how to resolve this? Thanks
View 14 Replies View RelatedHi All,
Could someone please look at the attached database and help me understand why my Union Query is not matching up correctly with the Program ID?
See attached
Thank you for all your help
Hey guys, I have a question for you. I have a query that gives me a list of names with info on each person. From the query I want to be able to click on the name of an individual and in turn have the form open up on that individuals page. Allowing me to enter more data and/or see more data on the individual. Thanks for any help you might give!!
Joe
I have a query which is run in a subform... however, I need a value from the parent form.
How do I link to this value in the parent form?
The query should look like:
SELECT tbl2.name, tbl.number
FROM tbl1 LEFT JOIN tbl2 ON tbl1.aID = tbl2.aID
WHERE tbl2.aID = parentform.[aID]
Now, the question is about the WHERE clause... how do I link to the parentform?
any help is appreciated
Jazz
I want to be able to click on the date in a calendar control and the jobs scheduled for that day appear on the screen. Is this possible and how would I go about this. I created a query to pull the information I need and I attached that query to a form, I then placed the calendar control the form but I am unable to get the control to pull the data.
View 1 Replies View RelatedWhen I make a search using a search form, I get a table with the results that match.Can I make a link from this table to a report to display the details of the matching product?One of the reasons I need to do this is because one search may bring up more than one product.
View 1 Replies View RelatedCan we link a table of one database to a query of another database ?
View 1 Replies View RelatedIn query design view, I have added a query with column (nameA) of names and a table with a column (nameB) of names. I am linking these two fields (nameA and nameB) which contain names. The names are spelled exactly the same so I should get results. Both fields are of the same data type.
But even when I add only the field with names from the query (the field called nameA), I don't get any results.
What is wrong here? Are there restrictions about linking a query to a table?
I'm having difficulty linking these two tables' date fields in the query qryDivVolShiftCount - what I'm doing wrong?
View 2 Replies View RelatedI have a query that has 2 fields of currency labour rates (current rate and baseline rate), but sometimes there are some blanks in either of the fields and is causing my text field which has a Sum formula (Rates x Hours) to #ERROR and throws out my other calcs. The only way to find out which blank line items are throwing my query numbers out is to manually type in "Is Null" in the criteria.
However, I would like to keep this process fully automated so I tried to create 2 lots of option buttons for my form, one for each of the 2 fields of currency labour rates and after I click on either option button and then run my query it will show all the lines that have blanks. I was able to link 2 text boxes to the option buttons (Select Case - Case 1 - Me.txtBox1.Value = "Is Null") and then link the text boxes to the criteria field using [Forms]![frm_labour]![txtBox1]. However, none of this seems to be driving my criteria in my query.
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
I have a query which performs a calculation calculating totals of many fields linked by BikeID This is not updatable due to the calculated field. Now i want to join this query to the BikeDetails table to create a form. So this means that i would have all the data from the table which i can update and the calculated field which i cant. But when i join the two tables/query all the data becomes non updatable.
Thanks
Sci
i am new to databases and have developed a ads site
when the user registers they can post ads for free.
i would like to allow one user to post more than one add.
these are my databases
[members]
memberid = autonumber (primary key)
username unique
password
surname
firstname
address
post code
country
[ads]
adid autonumber (primary key)
adtitle
price
picture
contact
date posted
end date
i am guessing i will have to create a one to many database but i am lost at how to do it.
any advice would be appreciated.
hello,
I've got a back-end/front-end Access database. The front end sits on a user's desktop. There's one table she needs to update, but cannot because it's not linked. It's table called Login that contains passwords/users names. The table did not exist in the front-end. I would like to copy it from the back-end to the front-end and link it, but when I try to do that, I don't see it in the list of tables. I do this rarely. How does this work exactly?
thanks-
Is there anyway to link an excel spreadsheet to an existing database table - so that entries into either update each
other?
Question for someone..... I was looking at the "LinkTables" in Access..... I see that I can link one database to another..... When I do this I can add the table to my relationship window... I have not played with this too much.. But looks interesting. Now, all the info I might add to this linked table is stored in the other database??? If this linked table is also linked to other tables in the other DB can I use those in queries? Not much covered (that I can find) in the Access help files on this sort of thing.
Thanks
I have a field on my Form. The form holds all the information about Artists under our record label. I need to be able to attach a copy of each artists contract to their separate form. I just found out that you can't physically attach this. Does anyone have any ideas and how I could play this. Any ideas would be well appreciated.
Thanks
Shane
I use a packaged software program at the dealership I work at. In the past I have been able to pull queries and information out of the database and into Excel. The query builder pulls the data as read-only and thus no fear of changing the main database. I have developed an access database for doing commissions. I want to be able to link my database with the companies database as read-only. This would save typing a lot of the duplicate information (ie customer name, date etc.). I have been able to successfully link the 2 databases but realized quickly that anything I change in msaccess will change on the main database. I don't want to corrupt the main database and really only want to pull the information for viewing. Any comments or help would be greatly appreciated.
Thanks
Rob
I have a Form that refers to two Reports, both of which refer to two query's one for each. the whole lot(1 Form, 2 Reports, 2 Querys) is in one mdb file and the actual database is another mdb file. How do I get my objects to get their data from the main Data Base File? the 5 objects are acting as a small report making application.
View 5 Replies View Related:confused: Does anyone know if there is any way to link to seperate databases so that any information entered in one will update the same information in the other and visevirsa?
Thanks
I want to use the data contained in Access table in an Excell app. What is the best way to link them - preferably in 'real time' - is there a way excell can read directly from the tables? We need to analyse some of the data in the database through Excell....
Any suggestions welcomed...?
I am looking to lonk tables in a database.
I have one table a pc tables which has a list of pc's
the other table is a pc software table which has a list of software
i want to build a form to show the pc's and their software how do i do this
I am having a hard time linking some certain information. I think I have my relationships formatted correctly but not all the information is connecting.
For example: I have a form where I am creating a sales quote. In the form I have a combo box where I select my product ID. When I select the product ID, the product name automatically populates but the price does not.
I have attached a screenshot of my relationships. Can someone please look it over and see if anything stands out to you as being incorrectly formatted? Or offer any advise in getting my price to connect to the product ID I enter?