I have an Analysis_Database which reads tables and performs calculations in a Project_Database. There are multiple Project_Databases which reside together in a folder. To run my queries and analysis I link my Analysis_Database to each of the other databases and run my reports.
Is there a way to set up these links in a table or something to eliminate the Tools/Utilities/Linked Table Manager...etc
Any siuggestions to make this process more streamlined would be appreciated. (I can not make any changes to the Project_Database).
So I have an access application to keep track of the status of small programmable PCBs that we use.. every time one of these pcbs is relocated or reprogrammed, an entry is made into a table in my db (tblLocation) with a unique ID for that action, the ID of the PCB (SD), the location it is going to, the new owner, the firmware version it's been programmed with and the date. Hence, multiple (dated) entries for each board are possible.
what I need to try and do is retrieve the most recent set of data from this table for every board we own... so far I have come up with a method that uses 2 queries, but I would like to combine this into one SQL statement that I can pass to the access db from an excel front end using ADO...
here's my 2 queries: #1 (qryLastEntry) finds the date of the the most recent entry for each board:
SELECT DISTINCT tblLocation.SD, Max(tblLocation.Date) AS LatestDate FROM tblLocation GROUP BY tblLocation.SD;
#2 uses the result set from the above to retrieve only the most recent set of data for each board:
SELECT tblLocation.SD, tblLocation.Date, tblLocation.Owner, tblLocation.Location, tblLocation.Firmware FROM qryLastEntry INNER JOIN tblLocation ON (qryLastEntry.LatestDate = tblLocation.Date) AND (qryLastEntry.SD = tblLocation.SD) ORDER BY tblLocation.SD;
If there is a way to combine the 2 into one statement, that would be very useful, as although this method works just fine with access and using the built-in 'get external data' feature within excel, I'd like to be able to manipulate the SQL pragramatically (and also, I may want to migrate to mysql so a cross-platform solution would definitely be preferable in the long run). I've had a look at using subqueries to achieve this but with no luck so far..
This query works, but is really slow. Is there a better way to write it? I have 2 tables, Card and Shipped. For all cards, I am trying to insert a record into the Shipped table if it they are not already there:
INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped) SELECT Card.CardID,'Wpg',#1/1/2007#,0 FROM Card WHERE Card.CardID NOT IN ( SELECT Shipped.CardID FROM Card LEFT JOIN Shipped ON Card.CardID=Shipped.CardID WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007# ) AND SystemID=10
i have created a query that solves for the difference of the debit and credit of the accounts receivables of a certain member or person..
SELECT DISTINCT ( (SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction FROM tblJournal WHERE JournalEntryNumber IN ( SELECT JournalEntryNumber FROM tblAccountsReceivableFromMembers WHERE MemberID = '2008-0001') AND AccountNumber = 120 ) WHERE AccountAction='Debit') - (SELECT SUM(Amount) FROM (SELECT tblJournal.Amount, tblJournal.AccountAction FROM tblJournal WHERE JournalEntryNumber IN ( SELECT JournalEntryNumber FROM tblAccountsReceivableFromMembers WHERE MemberID = '2008-0001') AND AccountNumber = 120 ) WHERE AccountAction='Credit') ) AS ['TotalAccountsReceivables'] FROM (SELECT tblJournal.Amount, tblJournal.AccountAction FROM tblJournal WHERE JournalEntryNumber IN ( SELECT JournalEntryNumber FROM tblAccountsReceivableFromMembers WHERE MemberID = '2008-0001') AND AccountNumber = 120 );
i need help on this so that there would only be one (WHERE MemberID = ?) to use. thanks so much! i hope to read your replies as soon as you can!
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.
It looks a little like this (and you can see my not matching ID issue):
Homeowner table ID First Name Last Name...........Total Dogs 1 Max Maximus 5 2 Min Minimus 0 3 Mus Musculus 1
Dogs 1-5 table ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name 1 (Max's) 4/11/14 Bobby Billy Betty 2 (Mus's) 4/11/14 Jojo
Min will have no dog records at all, just home information.how to link the dog's to the homeowners .
Since I am using Domain lookup function in this field, it takes considerable time for this field to get populated on the form when I move from one record to another.
Can anyone tell me the syntax for me to move this expression inside the Form's Query so this can execute faster?
Please note that I need this query to be updateable as I have some other form fields that needs to be edited.
Record_No <--- This is an autonumber field Est_COE --Date Field Type DATE_DOCR DATE_Followup Progress
I then have used multiple querries that just count records based off of Status:
Example of one of the querry's: SELECT Count(borrower.Record_Number) AS Close_Of_Escrow FROM borrower WHERE (((borrower.Est_COE) Between Date() And (Date()+7)) AND ((borrower.Progress)="active"));
Another Example of one other querry: SELECT Count(Borrower_Journal_Notes.ID) AS Followups_Not_Done FROM Borrower_Journal_Notes WHERE (((Borrower_Journal_Notes.Follow_Up_date)>Date()) AND ((Borrower_Journal_Notes.Followed_Up_Complete) Not Like -1));
Then I put both these querry's into a another querry so i can get the data put into a single form using one record source. (The master querry)
***this has already been posted on the report formus...i posted it here also due to its relation to forms***
**********************DO NOT GET MAD**************************
GOOD MORNING...
I currently have a form to launch a set of pre-desinged reports. The main function of the form is that it allows the user to sort or filter the data that appears in the report based on their selection (from combo boxes). I currenty have it set up so that the user can choose a specific client, facility, or region. The problem is the following:
THE FORM IS WAY TOOOO BUSY AND NEEDS TO BE SIMPLIFIED. EX:
CLIENT (combo box) Microsoft nortel dell ibm . . . btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3
REGION (Combo box) North South East west north-west . . . btnLaunchRPT1 btnLaunchRPT2 btnLaunchRPT3
etc.
each btnLaunchRPT'n' has the following code DoCmd.OpenReport "rptECM Numbers", acViewPreview, , "[Region] = " & "'" & Me.Combo299 & "'"
but I change the [region] to whatever it is I am sorting by. Can I reduce this down to only having one set of btnLaunchRPT buttons that will detect what the user has selected and applies the proper sorts
I have been asked to design a database to replace our old excel sheet to log one of our customers server builds. It a four noded system and i require a seperate log for each node serial models of parts etc, qa checks.
I intitial started by just using one table but forgot the limit was 255 rows, i need about 600 - 700. So my plan is now to transfer to indivial table for each node. Then, a general table named Main for all my other details.
Although this seems simple, i now have the annoying task of being able to link them all together and produce a single PDF file to send to our customer whilst only entering our system serial number once to produce this.
My basic understanding will be to create a System Serial Number field in each table a link it via relationships, but if i produce a report with everything i need would it input my details for all four nodes? If so how would i set this up?
I will be splitting the database once completed and then making a ACCDE file for my techs to use.
Hi, hope someone has an idea of where to start on this.
Our company offers projects to multiple contractors and we have to enter when we spoke to them and their response. I put a textbox with default of Now(), thinking these would be recorded in real time and it would be no effort at all. But the offers are frequently entered days later, and keying in dozens of specific dates/times in the long format is very irritating for users.
Does anyone know of something similar to the DatePicker or calendar popups that will let you use arrows or comboboxes or something to quickly enter a time? We record date, hour & minute (no seconds) because it updates the contractor's order on the rotation list (i.e., next call is made to the contractor who has been waiting the longest for an offer). No ActiveX please, we're in secured network environment.
I recently imported an excel file into access. Let's say it contained data for both domestic and international customers. With different things needed from each type of customer, once in Access, it became needed and easier to create separate tables- one for international and one for domestic. However, each customer needs their own unique id - and I need a way to link or relate the tables in a manner that it will not allow duplicate customer numbers between the two tables.
I have two tables called Hardware and Desk. They both have two fields called ChangeID and Priority.
What I would like to do is set it so that if we change the Priority in a record existing/created in 'Hardware', the Priority of the related record in 'Desk', also changes.
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 table with job info like: job #, job name, job addy. I would like my end query to look like this: job #, job name, job notes. I want the query to list all jobs, job names, and job notes for a particular week(we have a weekly meeting on the status of our jobs), so if I were to query the jobs for 6/26, i would get: job #, job name, and job notes for that date as fields in my query. I am not sure how to accomplish this. Should I make a seperate table that contains job notes? If so, how would I accomplish this and create a relationship between the job info table and the job notes table?
:confused: How can I link a table to one specific item in another table in order to avoid repeating information? (ex: school name that would be narrowed down to its different departments)
Excuse me for sounding extremely dumb, but I have a question that is really bugging me. I have created a database using two tables. The two tables are called User and Assets. In both tables I have set the primary key to be the field Username. I have also linked these two fields.
I have created a form that is my interface for the database and can add and delete from the table (User). I now want to insert a sub form into my form, that displays the contents of my second table (Assets) and reference's the field Username. Hopefully this will enable me to display my table (User) in the Form, whilst only displaying the relevent data in the subform (Assets) for the particular user.
I'm sorry if this sounds confusing, but I would really appreciate your help ...
I have a question which I think is probably very straight foward for those who know about these things. I have just created a new database, but there are obviously some problems with the design, because when I enter information into a form, I don't seem to be able to access the information through queries or reports.
I have 11 tables, and one single form (I created the tables, and then used the wizard to create the form). The main table is called 'Client Information,' and the key in that table is the CaseNumber. They key in every other table is called something along the lines of 'ClientDetailID,' 'IncidentDetailID,' etc. When I look at the relationships between the tables, the 'ID' field in each table is linked to the 'CaseNumber' field in the Client Information table. However, I don't have any fields actually duplicated between the tables. Do I need to do this? (ie, should I have the 'CaseNumber' field appear in each table, and then the 'ID' field from every other table appear in the 'ClientInformation' table?)
thanks so much, this is probably a really simple problem but I don't think I really understand the whole relationships and linking tables thing.
I have tables in MS Access and the same tables in SQL Server. I want to link the appropriate tables up so that when i update the table in MS Access, it will automatically update the table in SQL Server. I want to do this using an ODBC link.
Anyone with any suggestions of how to go about this?
Hi I need some help linking some tables... Does anyone knoe how i would go about taking several years of contact information and compine it all into one table
I have 5 or 6 questionnaires and each of these is linked together by a person Identifier number. How can I do it so that all the questionnaires recognise that these tables are all linked by the same number.
Secondly, if I wanted to say, do a search on a particular number, is there a way to do that if i say, searched for that number in a query, i'd get all 5 questionnaires come up rather than have to do a search for each table (ie. one search brings all the results from all the tables up or at least a link to them
What would be great is if you could do it so that you could do a search on say, all who were a nationality and it brings you up all the results for all the different questionnaires.
I have a 'main' database named(A), and I have 2 tables that I have linked from two other databases(B and C). NOTE: I kept all this 3 databases(A,B,C) in one folder named 'Joe' on my workstation.
Here is the issue, every so often when I open my 'main' database, and I need to query the tables in B or C(which I have already linked to in my main database), I get an error message saying something like "path to c:...B is not found." To fix this issue, I have to use the "Linked Tables Manager" tool to relink all the tables again. As you can this, this is causing me a lot of unnecessary wasted time. What will i have to do to have these tables linked permantely so that I don't have to relink everything?
Each person can only be either a Host, Sponsor, or Organizer. I have established the proper relationships in Access, but my problem is if i make a form to create Hosts say, it will create a person of type host, but it will not make an entry into the Hosts table linking the person's id. I presume this is because it doesn't know that the type field specifys which subclass the person is.
My question is how to get around this problem, can I somehow tell it that type indicates what other table to use, or can I manually insert them? (Since I have an individual form for each subclass)
