Hi all,
Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.
Any help would be greatly appreciated.
I have a large database of Purchase Orders
Orders can be placed by people in one of 4 departments
I would like to automatically make 4 separate databases (so each department can access their own) from the master
Is there a way to do this?
I would like to know in what type of working environment everyone works. i.e. an office with a closed door, a cubicle with high walls, shared cubicle, low-walled cubicle.
View 2 Replies View RelatedI have 2 different databases. One for Archives and one for active claims. Both databases have identical tables. I'd like to create a query that will pull information from both databases. I'm pretty sure that I can do this with a union query, but I'm a complete noob with sql.
Can someone give me an example of the sql code to use to pull information from tables on both databases?
Hi
I have a problem with Update Query, I have 2 DB's and need to update a table held in DB#1 into a table in DB#2. I have read quite alot of post's about this but nothing is giving me the correct answer. I tried using Append Query but this was no good because when i ran the query it just adds the extra entries, I want the query to Update the entries already in DB#1. I also tried Update Query but didn't get the option to choose another DB.
Could anyone please tell me the best way to extract the data from tblInstruments(DB#2) and update tblInstruments(DB#1) i.e. overwrite the original field entries.
Many thanks.:confused::confused::confused:
Dear all
I need to merge a number of databases of the same type into one database in access. can you please help me with code itself.
thanks.
Ampah
I have a query that is based on two other queries (they feed a larger query that a report uses). They are all select queries, no make-table, etc.
The query runs fine against the production oracle database. However when the query is run off of either of the two identical test databases (production was dumped into test within the last week), the query stops with an error of Invalid Operation.
The Help Screen (see below) doesn't seem to apply to anything this query is doing. Any thoughts?
Invalid operation. (Error 3219)
Possible causes:
You tried to write to a read-only property. See the Help topic for the property to determine whether it is read/write.
You tried to use a method or property on a type of Recordset object that the method or property does not apply to. See the Recordset object summary topic to determine which methods and properties apply to a given type of Recordset object.
You tried to append a property to a Properties collection of an object that does not support user-defined properties.
You tried to use the Update method on a read-only Recordset object.
Thanks,
B
I have two identical databases in MS Access. I have created a query in the first mdb file, which retrieves the matching records in two tables.
My problem is that i want to execute the same query created in the database1, on database2, without creating the query in the database2.
I think it is possible, but the query i was trying was not working file.
select * from db1.[Query1] in 'C:DBDB2.mdb'
where db1 is the name of the first database,
db2 is the name of the second database and
Query1 is the name of the query in the db1.
Thanks in advance for the help.
hello!this is my first time in this forum
i hope you can help me with this...
i created a database wich has one table with many fields, what i need is a parameter query to sort the information on that table, the problem is that i want the user to decide wich fields he needs, and if he want to use parameters (criteria) or not on those fields he choose, i wanna now if this is posible with just one query instead of one for each combination of fields the user wants on his query. i hope i make myself clear if i dont just let me know
oh and the criteria is taken from comboboxes in a form, and the query is runned from a comand button on that form too.
thank you
In database is one Table, where are only two fields (ID and Text):
ID Text
1 AA
2 BB
3 AA
4 CC
5 EE
And in a form I have TexBox where is a text string, for ex. : aa; cc; ee
Im trying to create Query which find all records from Table which contains
"aa" or "cc" or "ee".
If I use concrete values in Query, it works :
SELECT * FROM Table WHERE (((Table.text) In ("aa","cc,ee")));
but when I use refer to a TextBox it doesn't works :(:
SELECT * FROM Table WHERE (((Table.text) In ([TexBox])));
Because it will do [TextBox]="aa;cc;ee" and not "aa";"cc";"ee"
Can anyone help me with it?
Thank you and have nice day.
Jira
HI,
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.
thx guys!:D
Hi All,
I have 2 tables like below:
TableA:
- Customer
- Customer Industry
Table B:
- Customer
- Project
Each customer has many projects.
My ultimate goal is to create a table like this:
Table C:
- Customer
- Project
- Customer Industry
Trouble is, tables A and B are updated by different persons. A project is usually created first before someone creates the entry in Table A to enter the customer's industry. If someone forgets to update Table A, I will see entries in Table C with industry of customers as blank - which i don't want to.
What is the best way to do this?
I'm thinking of running some sort of queries that will automatically create an entry in Table A when a new customer project has been entered in Table B, so that the person responsible for Table A knows to enter in the industry - but i don't really know how to start, could you help please?
Merry x'mas all!
:) Hello,
Can anyone please tell me how to create a graphic (a chart like in excel) based on the result of a query? It's just names and values (2 fileds only)
Thankyou.
Hi
Is there a quick way to split up a table into many other tables? I want to split the table by Client Number, so all the Clients with 1234, for example, are in a table called Client1234
I know the very long winded way, creating a ‘Create New Table’ query, inputting the Client Code criteria each time.
Is there a better more efficient way?
Thanks
coley
Hi. I have a simple access table that contains details of our customers, and what products they have bought from us. Each row represents a product bought, and contains the customer details & the value of that product.
What I wish to do is create a report that lists these purchases, grouped by the client. That much I can do. However I also wish to sort the report by the total spend of the customer.
What I think I need to do is create a query that totals the "income" column for each customer, and then sort the report on that new figure.
All I cant work out, is how ?. Could someone please let me know how best to do this - small words please - my ambition exceeds my ability !.
Thanks all.
Hey guys, I am trying to creat a query from my Status table, but having NO luck getting it to work. Basicly the Query is going to create brand new columns with the Status entry from the row selected. I have been able to create a query with 1 status column, but I cann't seem to figure out how to code it where it creates a 2nd. Heres what I have
SELECT SiteStatus.Status AS MailStatus
FROM SiteStatus
WHERE (([SiteStatus].[Site Component]="mail"));
Some how I need the Where statement to go along with the new Mailstatus column and then create a new column with the same effect, have it select the status from the row what contains the "component" in the "component" col. Anyone get any of this cause its starting to confuse me even now. Using Access 2003
Trying to do this sort of query is beyond my comprehension of the software, so hopefully I can get help here.
What I am trying to do is add a total of a certain field together, while not adding the total of another field together. I hope that makes sense.
Anyway, here's an example:
Family: Sum of Duration: Years: Total Birds per Hour:
Tyrannidae 10 1999 0.1
Charadriidae 107.35 1996 26.967
Charadriidae 175.05 1997 45.29
Rallidae 34 1995 46.15
What I am trying to accomplish is that I want to add together the Total Birds per Hour field, without having the duration sum together. What has been happening is that when I sum TBpH, Duration sums, also. Yes, I know I have it as sum of duration, but the point is that has -already- been summed and therefore does not need to be summed again.
Basically, we are trying to see how many birds appear per hour within each year. However, our Duration totals cannot exceed a certain amount per year. Say, we can only get 600 hours in the year of 1997, but instead I end up getting something like 7000 hours because each entry in the family "Charadriidae" gets summed together when it should not.
What was explained to me was that, when we are summing the durations together, only one duration entry at a specific date should be added together. Here is what we believe is happening:
Date: Duration: Species:
199534B 5.5 Ruddy Duck
199534B 5.5 American Avocet
Theoretically, as a total duration for the date "199534B", we should only be getting 5.5, because both of those birds were spotted during a 5.5 hour period, not an 11 hour period if you were to sum those up.
I know that sounds complicated, and I'll try to explain more later. But, are there any suggestions on how I should go about this?
How do you create a query in code. Actually creating a query that appears under the query section of access. Is this possible?
Thank you
Hi Guys!
It's me again.
From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:
Field No Records With Records Total
Address 10 256 266
CreditREc 5 261 266
Is it possible?
Thanks!
Hi,
I wrote a script which creates a new table and append records into it. Here are my codes:
For Each rst In dbs.tabledefs
If rst.NAME = "Biweekly_Temp_Table" Then
dbs.tabledefs.Delete rst.NAME
dbs.tabledefs.Refresh
End If
Next rst
qryBtt = "SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='R',[DEDETAIL" & payp & ".ded_am],0)) AS [Employer Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='A',[DEDETAIL" & payp & ".ded_am],0)) AS [Admin Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='E',[DEDETAIL" & payp & ".ded_am],0)) AS [Employee Actl], " & _
"First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, " & _
"First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS Type, " & _
"Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS LeftType, Sum(DEDPARMS" & payp & ".NBR) AS SumOfNBR, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _
"First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, DEDPARMS" & payp & ".DEDPLAN_CD Into Biweekly_Temp_Table " & _
"FROM DEDPARMS" & payp & " LEFT JOIN DEDETAIL" & payp & " ON (DEDPARMS" & payp & ".EMP_ID = DEDETAIL" & payp & ".EMP_ID) AND (DEDPARMS" & payp & ".DEDTYPE_CD = DEDETAIL" & payp & ".DEDTYPE_CD) " & _
"GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), DEDPARMS" & payp & ".DEDPLAN_CD " & _
"HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P'))) " & _
"ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"
DoCmd.RunSQL qryBtt
NOW THE ISSUE IS, AN ALERT MESSAGE ALWAYS POP UP SAYING SOMETHING LIKE, "You are about to paste 34590 row(s) into a new table." What would I have to do to get rid of this pop up? Eventually my users are going to use this and I don't want them to be alarmed when the message pop up.
Thank.
Joe
Hi.
I have three tables. Each link together by relationship. In a form, i will display the relevant data from these three tables by means of a query. However, i realised that i could not perform a delete operation as my database table still exist the particular data even if it is deleted from the query.
How do i create a delete query so that it will also automatically delete all the datas from the 3 table?
Please help.
I have create these two count querys that count the no of booking in January and the other no of bookings in Febuary
SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#12/31/2006# And ([Booking Order].[Start Date])<#2/1/2007#));
SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#1/31/2007# And ([Booking Order].[Start Date])<#3/1/2007#));
but i need to create a query which will bring up the two counts in the same query. This is what i have create so far but have got completely stuck and don't know where to go from here.
SELECT Count(BO1.[Booking No]) AS [Count Of Jan], Count(BO2.[Booking No]) AS [Count Of Feb]
FROM [Booking Order] AS BO1, [booking order] AS BO2
HAVING (((BO1.[Start Date])>#12/31/2006# And (BO1.[Start Date])<#2/1/2007#) AND ((BO2.[Start Date])>#1/31/2007# And (BO2.[Start Date])=#3/1/2007#));
Hi guys,
Can someone please tell me if there is any way to create a query that gives me ... lets say a sum of smth monthly.
I'd like to create a crosstab query to have at
rows : names
columns : months
values : sum of smth (kilometers for drivers)
Thankyou in advance
can I use create table query and change a fields data type?
I have a field with numbers, but when I get the table, it is set as a text field. I want it to be a number field, but I can't change the orignal table. That's why I was going to use a create table query, but I can't figure out how to change the data type from text to number.
Thanks
I'm creating a database that keeps track of printing jobs at a printing company... I started my project by drawing out how I want the databases to be configured.
I was going through a book that was made for access 2000, but I need to create this in access 97 because that's what the company has on their computers. One of the features in Access 2000 thats not in 97 is subdatasheets...
Basically, what I want to do is for each printing job, there can be a bunch of different tasks that need to be completed and billed for. For example, on one printing job, they need to design a logo, and then they need to print it out and send samples across the globe, and then they need to create a pdf, etc. This is going to be different for each job.
What I figured I would do is create a separate table to take care of all of the different tasks that are related to each job. This table would have the primary key of the job from the main table for each individual job, and then they would be related in a one (MAIN entry) to many (tasks) relationship.
Is this correct in how I want to do that? How will I do this inside a form, I want them to enter the information in table that expands as they put more tasks in?
This might be a very simple question, I just want to know if I'm going in the right direction.
Thanks much!
Hello,
I have been reading a lot about splitting databases on this forum. I still have some questions.
1) Will the FE (Front End) still show the tables?
2) Will users still be able to edit the forms, reports, etc.?
3) Will my code be hidden
4) Will all the users have up to date data showing when they open the Database?
5) Can more than one person open and input data in the database at the same time?
I also want to make an MDE copy, do I split first or make the MDE and then split?
The whole point is the following: I want the people (maximum 10) that will be using this database to only be able to do enter and view data. They should be able to generate the reports but not create new reports. I only want ONE person to be able to edit the forms, code, and reports. How would I do this.
Your help is much appreciated