Ok, this is only a very general question, but I'm hoping somebody here can give me some general advice.
Basically, I have three separate database which I've written for my company. They were all written at different times, hence the fact there are three and not one that does three things. The first is used to raise, edit, delete, and track RMA records for returned merchandise. The second is used to raise, edit, and track Training data for people who have undergone my training course. The third is my main database that contains all of my clients' details, plus a log of all calls taken from them. This is the largest and most complex of the databases and has some fairly advanced relationships setup to enabled hundreds of call logs to be linked to a single record of necessary.
Now all three databases have been written using the same kind of structure. They all use popup modal forms to prevent malicious/accidental damage to the database by the staff who use them. They are all locked down and Access cannot be broken into whilst they are running. they all also have their own primary keys.
All these databases work just fine, but I'm now a little tired of each staff member having three shortcuts on their desktops, depending on which database they need to use. what I'd really like to do is somehow merge all three of these databases into one single database, and alter the switchboard accordingly to allow users to access any of the three parts of it.
The problems - as I see them - are: (a) that my main database uses a lot of relationship links to maintain certain records, whilst the other two use none, and, (b) that each database, having been written separately, has records with their own ID keys and autonumbers.
I did toy with the idea of having some kind of function on my main switchboard that would allow me to close the current database being used and open another, all without closing Access, but I haven't been too successful in my tests...
...so, my question is, does anybody have any bright ideas as to how I can go about merging these three, or, at very least, centralising access to all three?
I am new to Acess, I am trying to merge 2 Access DBs and am not sure of the steps to accomplish this, if it can be accomplished and/or are there any tools to reach the same goal.
We are using an Access database for off-site data entry. We therefore have one master database we keep in the office and then a "template" version -which is identical to the master version except no records - we bring to each site, do the data entry and then merge that data back into the Master database.
Can anyone help suggest ways to make the data merging process more efficient. The database has approx 12 tables, so I dont' want to create an append query for each one every time. Is there a way to just "merge" two identical databases??
Sorry if this is in the wrong place - I am very new to this site
I have asked this on another forum but have had no replies - hoping someone here can help
I am using Acess 2000 and I am not that good at it
Is there any way to merge two of the sample databases which comes with Access together - I would like to have the Inventory Control and the Order Entry databases as one database - can this be done? There is no data in the databases yet
this table is from another db. Notice how WarehouseNum is '9' instead of '1' and itemNum is '000'. This is just one of many impediments I face. As well, none of the table fields are the same, I changed them here to make it easier to work with.
The goal here is to query the two simultaneously, perhaps with linked tables, type in an ItemNum using a parameter and know for instance that on the 26th, we received 250 item31 and shipped 200 for a remaining balance of 50. The 50 is what I'm after. Basically, my company uses one program for sales and another for purchasing and absolutely nothing for inventory management. I started with this company about 3 weeks ago and unless i can implement some kind of temporary solution, it will drive me insane.
Advice? Best approach? As well, this db will use data on two different servers Thanks for the help. As well, my access experience is limited and our company I.T. is seriously backlogged, besides which, I would like to learn how to do this myself. Thanks again
Friends own a book shoppe. They have closed up both their retail stores and are working on line only. A long time ago they bought a PC based program to keep their inventory and manage their on line sales. The program is called BookTrakker and still on sale today.
I called them to ask for assistance merging the access databases for but stores with their Master database. The owner sads they dont offer any assistance in combining databases.
I have had no trouble using the append command to combine the 40 odd tables in the database except for one small table called Keywords. This table only has 5 fields but the sallest has over 7,000 rows. This is one of the more important tables because Keywords are used by the search engines on-line for potential buyers find their interests.
The error message I get from Access is "Microsoft Outlook Access set 0 field(s) to nulll due to a type conversation failure, and it didn't add (number of rows in table) to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations"
The table structure is identical. The only difference is the data in the rows.
I tried removing both Primary Keys for both the tables (in and out). Nothing, same error message.
Next I tried appending the tables without the entire Keyword_id (Primary Key) field. Nothing same error message.
Currently am trying to get together two separate Access Databases into one, which is the way it was always supposed to be.
Basically, this person accidentally created a duplicate of the database in question, and has been updating both independently at different times, meaning that whilst a lot of the database information is duplicate, there are some instances where each separate database copy has unique information on both existing contacts, and new unique contacts.
The database itself is about schools, and has tables concerning information such as calls made, contact details, school details, etc etc. Obviously all of this needs putting back together into one set of tables in one file.
I've been messing around with append and import functions, but am having issues with duplicate ID fields with append queries, and a general lack of direction on the best way to go about fixing this problem, if its even possible.
I'm merging a few databases together. They're fairly simple on their own but I'd like them as one big database. I read that I should import each access database into a new, blank one.
It all works fine, but none of the saved imports and exports come with them. Unfortunately, these imports and exports are relied upon quite heavily.
Is there a way to bring saved imports over from other databases, or will I have to rebuild each one?
I've currently come across the issue where an end user copied the database locally to their computer and has been using it for months and months. After fixing an issue in the database on the server and then finding that someone still had a database bug is how I figured this out. Anyway, what would be the best way for me to merge the table 1 from the database she was using to table 1 in the database on the server? Is there a way to export a table so I can then import the data? Like I said, the databases are identical other then the data.
I have data that splite by month, so january data separated from february data, an so on. can i display those data on one table by using query? if yes.how can i do that?
We've got a software that is creating a mdb file with one table and 10 columns. Every day we have around 30.000 entries in the table. At the end of the week we have 7 different mdb files and usually we copy/paste them into one single file to do the base research. Everything is fine until the mdb's file size is 2GB. I understand that mdb files start behaving strange with files over 2GB of size. Therefore after a couple of months we have several 2GB mdb files. When doing a history research we have to open each and every file to check and search in that file. That can take a lot of time if you have 20 or more mdb files.
What I am asking is ... if there is a possibility to merge all the 2GB files into one or maybe more so that the research wouldn't take so much time.
I try to call one database forms and tables into another database without creating tables and forms.
Database where (target) i need to open forms and tables: insurance.mdb (In the same database I click on TOOLS - REFERENCES and add accountreceivable.mdb as i need all forms and table from that database)
Database from where (source) i need forms and tables accountreceivable.mdb
I try to call forms and tables from accountreceivable.mdb into insurance.mdb. Do i use the below function in insurance database to open tables and forms from 2nd database. If i do where should i write this function i mean in the module of insurance database or in the module of accountreceivable database. How to i call that function
Function OpenForm(byref vformname as string) as boolean docmd.open vformname,,,,,acdialog
I'm in the process of creating a database to track campaign contributions, and I'm kind of stuck.
I've created tables for the citizens, the candidates, and the contributions to the candidates. Now I'm trying to populate them with an Excel spreadsheet. The problem is I have no clue on how to split the Excel file so the appropriate parts go into each table.
The spreadsheet contains: Name, address, etc. - This needs to go into the Citizens table Names of candidates individuals contributed to - This needs to go into the candidates table. Dollar amounts and dates of contributions - This needs to go into the contributions table.
Each citizen may have made multiple contributions to multiple candidates.
The easy way would be for everything to be in one table, but that would be a bad database design, right? Here's a shortened version of how my tables are designed:
Citizens: Name, contact info, etc. of citizens Candidates: Name, party affiliation, etc. of candidates Contributions: Candidate (fk is pk of Candidate table), Citizen (fk is pk of citizen table), contribution date, and contribution amount.
I appreciate the assistance, as I'm getting really frustrated.
I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.
The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.
I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.
I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.
The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.
I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.
Hi all, I'm not an Access programmer by trade, but unfortunately got handed a project that nobody else wanted.
I have a small database taking shape which will track the auditing of paperwork/files. Each file has a unique ID associated with it, in this case, an account number. Audits will take place by selecting the job area (department), then the type of file (Line, Loan, Increase). This will be used to sort which type of audit questions are asked.
I have the following tables created:
tblQuestions: ID, Question, Product question applies to, Function question applies to, Comment field, and result field. tblTeamMember: MemberID, TeamMember, Manager, PrimaryFunction tblAuditResult: AuditID, MemberID, AccountNumber, Question, Result, Comment, Date of Audit tblProduct: List of product types (line, loan, increase) tblFunction: list of job functions
Currently a form is displayed so the manager can select the job function, then the file type. This will display:
frmAuditCreate - this form contains the fields: txtAccount, cmbManager, cmbTeamMember, txtAuditDate Sub form on frmAuditCreate - frmQuestionSub - this form contains the fields: Question, Comment, Result
The frmQuestionSub is built off a query which uses the function and product values to determine which questions to filter and display.
Now, once the user is done flipping through each question in the sub form and input the data into the comment and result field associated with that question, I need that information along with the information on the frmAuditCreate to be stored in the table tblAuditResult. This is the problem I'm having difficulty with. I can't find a way to copy the question, comment, and result and insert it into the same record as the rest of the data.
Unfortunatlly I can't strip the database down enough to meet the 100k or less attachment limit.
I have a table which has each claim as a single record. A client wishes us to provide the data in a format that lists the each procedure on its own line. I have attached a highlevel example of what the data currently looks like and what the query results should look like.So I need to create a query that would repeat the Claim Number and place all the Procedure fields into one field.
Hoping someone may be able to point me in the right direction.
I've got several databases used for different tasks, in which I just added an hyperlink to them from a front end database. All my team could access any of the data bases at once. It was using the WINNT/MS OFFICE mdw file.
Since my last question, I've now managed to set up a MS Workgroup Administrator file, which is on a shared drive. Everyone has joined this workgroup, and when people logon it now greets them with their name and records their name against any tasks. Thanks to everyone for their help with setting this up.
But since I created this new workgroup, we can't access more than one database at at time. It comes up saying 'mdw file is in use'. However, one person in the office can. I've looked at all the setups for individuals, and can't see any difference between ours & this one individual.
I'm hoping someone out there can help me out with a problem. I work for a company that creates websites for car dealerships. The process involves me inputing data to a standalone program which creates a database for each dealer. When I upload the information, the program I use creates an inventory.mdb which is accessed through the asp pages I have. examples of finished product are: http://maconsales.com/freedom/inventory.asp http://maconsales.com/freedomnew/inventory.asp
We currently host about 13 dealers. What my boss is wanting me to do, and I'm having problems doing, is making it possible for someone online to visit our main site and view all inventory from all dealers without having to leave our site to look at each individual inventory. And I would like to do it without having to manually create a central database with all the inventory we have, about 1400 vehicles.
Is what I'm trying even possible? or am I looking at a long night of data entry?
I am linking to an SQL database that is designed in such a way that the problem description is recorded in multiple records. See a simplified example below. I want to run a query that will merge the information in the PROBLEM_DESCRIPTION and return only one record for PROB_ID 55678.
PROB_ID ORIGINATOR DATE PROBLEM_DESCRIPTION 55678 Jim 01/01/2015 While cleaning my computer, I noticed 55678 Jim 01/01/2015 the screen was cracked. I called 55678 Jim 01/01/2015 the IT department and they said I had 55678 Jim 01/01/2015 to write a work order. However it 55678 Jim 01/01/2015 was rejected without reason.
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectB UserA - Hours - Date - ProjectB UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
I've inherited an Access Application which has (1) a number of linked tables that are used to seed the underlying SQL Server database.
This system has been set up and is working in Development. My task is to move it to UAT but with regards to point (1) above I can't see any alternative but to copy the Access Database, create a new System DSN and re-import the External Data from the new DSN. What I'd like to do is use the same Access Database to point towards DEV or UAT or PRD. Is this possible to do this or am I stuck with maintaining 3 different Access Databases ?
I am a developer with many years of experience but my Access knowledge is limited so any pointers would be helpful.
I inherited an interesting problem. I have 3700 tiny MDB files all of which contain the same table (same name, field structure, etc.), but different data. Each database has a slightly different name. It's the result of XML data mining.
I need to combine these 3700 tables from these different databases into one table in one database.
I can't see any way to automate this and as it's a one-time only project I don't know whether it's worth trying to automate it or if I should roll up my sleeves and just start apending tables.
I have around 10 mdbs and in each I have 4 tables, I want to add all the tables from 10 mdb to one mdb, finally I will have 4 toal tables, right now I am using a appendquery to add tables. Is there way using VBA I can do it on the form and with one click of a button I will be able to add. Thanks for any info..
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT"
The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
