We are currently using Access 2007 for all our database needs but so many different people have had access to changes things that now they are so convoluted that my boss is wanting me to build a brand new database... Is there a way to take all the tables in one database and migrate them over to the new database that I am building?
View RepliesIn the past I have only done data input into Access databases that have already been created. They have been simple text fields only. I was hired on contract to finish data input for a database that serves to catalogue material resources. The database itself was supposed to have been completed by a former contractor but I'm discovering bits and pieces that weren't finished. I don't have any background in IT or coding.
So, this database eventually has to be shared in a "locked" format so that other entities can view our resources (like a catalogue) without changing them. My next step is to add images to each of the items being catalogued (there are about 100 images). The former contractor began a table labeled "Pictures" in which there are 2 columns: PictureID and PicturePath, the latter being a path starting with C:. The issue is, his picture paths include the hard drive name so that it looks like this:C:HardDrivePicturesPictureID.jpg
I need the database and its images to be share-able on other computers. And I need to use relative paths to connect the images to the database so I'm not tremendously increasing the size of the database.does the location/path of the database need to be changed so it can be shared, and if so how do I do that?
Second question: I have all the images I will be using in a folder on my desktop; how do I save them in a "fixed subdirectory" of the database so that they get moved with it whenever it is sent out to another user?
If I change the database name as I'm working on it (I've been saving a new database each day, so that if I screw up something I can go back to the most recent working one) will that mess up my picture paths?
I want to add 3 new fields to the structure of around 80 tables (all with identical structures). Is there a way to automate this process?
Paul.
I'm altering a database to have certain fields be recorded monthly.For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".
I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).
I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.
the company doesn't track when the transactions occurred; I'm unable to group them along those lines.I'm new to Access & don't want to make a mistake.
Im building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
I would like to get a third party to edit a database and record those edits by changing the colour of the font. I know this can be done in layout mode, but it changes the font in all records, I only wish to change the font colour in one record, how I can do this, without changing all the records in the database.
View 3 Replies View RelatedI am new to access.
On occasion my access database all of a sudden begins to operate very slowly, I notice the queries take a much longer time to run.
Last week it suddenly began to operate very slowly after idling in excess of 30 mins
Today when I change the screen to design view and then back to form view once again it begins to operate very slowly. (yesterday was working fine on my desktop at home, I have not made any changes to the database with exception to saving and using the file this morning on the shared drive at work) If I shut down and re open it operates well but once again changing to design view causes the same issue. Sometimes compact and repair rectifies sometimes not.
Is there any way to keep track of a database structure in access? For instance, which query relates to which report? Sometimes I create queries that are no longer needed but if there are a lot (which there are!) it can be easy to delete one that's needed.
View 5 Replies View RelatedI created a database to track tardies and absences of my employees. I would like to be able to sum the number of absences and tardies for each person per month and graph it.
Per our attendance policy, 6 tardies = 1 absence. I need to take the total number of tardies that month/6 and add to the total absences that month to equal the total attendance for the month.
I need help. Can you please view my database and give me some ideas?
Thanks,
Jason
I am designing an application that tracks information on Choir membership and sheet music that we have on file. I am starting with a database of church members. There are four different choirs and choirs share some members. Some members of some of the choirs are also not members of our church so I will have to place non members in the member table. I am new to database design and would like the collective wisdom of this list to tell me of any problems I may encounter before I start doing any detail work.
What is the best way to deal with someone who is a member of more than one choir and may belong to a different section in this other choir (Tenor in one and Bass in another). An individual may also hold different offices in various choirs.
The table structure I have is as follows
CHURCH MEMBERSHIP DB:
MemberId Autonumber (pk)
FirstName, Text
MiddleName, Text
LastName, Text
DateJoined, Date
Phone, Text
Address, Text
City , Text
Zip, Text
EmailAddress
BirthDate, Date
Member, Boolean
CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs)
MemberId, FK
ChoirId, FK
FolderNo
RobeNo
Section
ChoirOfficeId, Fk
CHOIRS DB (This lists the various choirs in the Church)
ChoirId, pk
ChoirName, Text
DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)
MUSIC DB
CatalogId, PK
Title
Composer
Arranger
Type (Single Copy/octavo or book/collection)
PublisherId, FK
PublisherNumber
VoicingId, FK (From table with possible voicing)
NumCopies
UsageId, FK (Where in the service is it appropriate
Location, Text (Where in the filing system, or off site)
ClassificationId, FK (List of classification/genre in table so can update)
PUBLISHER DB
PublisherId, PK
PublisherName
PublisherAddress
PublisherPhone
PublisherWeb
PublisherContact
MUSIC CLASSIFICATION DB
ClassificationId, PK
Classification, Text (Christmas, Easter, general anthem etc)
To be able to track performances and plan services and performances I have the following table.
PERFORMANCE DB (This is to keep track of and plan the regular service)
PerformanceId, PK
Pdate, date (Date of Past/Planned performance. Possibly more than one per day)
ServiceTypeId, FK (From table of types of performances – morning service, evening, etc)
Location
Speaker
Pianist
Organist
Introit
Invocation
Anthem
Meditation
Benediction
(etc)
I would also like to be able to prepare mailing labels for the various choirs as well as the general membership from this DB. My primary focus will be on the music. I would like to have an efficient music DB that I may find out what music I do have and when I last performed them, what options for performance (usage and classification)
Would be grateful for your comments, Thanks!!
Rmiller
I am trying to figure out how to store data into a database to be use later in a web app. So I have a shirt and it can come in four different colors. What would be the best way to enter this into the database. I'll have shirts, jackets and accessories which I have give each their own table. The shirt one is giving me trouble though, I just don't know how to organize it.
Thanks for the help
Dear All,
is there any way to look-down the structure of tables within a database so users cannot change tables?
Is it possible to make this password protected so only certain users have rights to change the layout of the tables?
Would this still allow users to create queries and enter data?
Thanks, Steve
I'm doing some changes to a database in work, but the person who created it doesnt work there anymore and so nobody knows anything about it. The database has been split into a front and back end, which is fine, but there appears to be 3 seperate databases linked to the main one, each with a .mde file for security. Depending on which department you're in, you access your database. Any information being added is updated in the main front end database. Does anybody know how that will be linked? I'm just probably going to have problems when encorporating my changes.
View 5 Replies View RelatedHello,
I am looking for someone's professional opinion to help me clear up some unresolved technical issues in my mind... =P
Ok, I have a contact management database that would seem really straightforward to me except for the fact that I am dealing with 2 primary entities. In this I mean I have an Individual table and Organization table. These two tables have primarily the same field data, such as both having address data, phone (contact info) data. There are some unique fields to one entity that aren't contained in the other entity though, and visa versa. One important issue is that 0, 1, or more individuals can be related to an Organization. Therefore I now have linking tables for IndividualOrganization, as well as linking tables for Phone and Address data.
My primary complication is that since both Individuals and Organizations have address and phone data, the respective linking tables for them contain: an addressID/phoneID key, for the appropriate linking table, with an OrganizationID key and an IndividualID key in each table. So, the way I am thinking is that if the record pertains to an Individual, the appropriate ID will be in IndividualID and the OrganizationID will just be 0, or empty. This brings up issues with primary keys being blank.
I am wondering if there is a better way to do this that I am overlooking or if I am in fact on the right track? I have contemplated combining the Organization and Individual tables into one but that really doesn't seem like the best solution. If anybody has any ideas then I would GREATLY appreciate it. I had a working database the other day but now I am separating the phone and address info out of the primary tables and have really got a mess on my hands. =P If anybody would like to look at my database structure I'd be more than happy to post the back_end and the front_end for you to give me your honest opinion about any trouble areas.
Thanks BIG TIME in advance to any nice souls willing to help out a struggling programmer who doesn't have any close support on this one as I am working alone, :rolleyes:
Dana S.
I have 7 tables:
Booking - BookingID
Course - CourseID
Company - CompanyID
Materials
Materials_on_course (had to have this in order to avoid many-to-many.)
Employee - EmployeeID
Employee_runs_course (had to have this in order to avoid many-to-many.)
I'm stuck with the Course and employee thing. Each employee has an expertise or two, and therefore each course has an expertise needed to run it (a specialist if you like).. so the PK in 'course table' is Course ID, you cannot have two PKs in one, so would it be right to say that the 'expertiseID' would have to go in 'Employee_runs_course' ?.. what would i do then? place 'expertise ID' as an FK in both employee and course tables?
Extra info:
Clients (tblCompany) can book one or many courses
A course can have one or many bookings made for it
A booking has one or many employees/courses
An employee can work on many bookings (obv if available)
Each employee has one or two expertise (usually only one)
and therefore a course has on expertise attached to it.
__
If you have a better way of designing it, shoot!
Attachment: ERD (kinda) of first draft.
Hi People,
I am building a database of in-car audio systems. Stored in this are simple things like vehicle information, audio brand and other general information. The main information I need to store includes details about speakers (position, quantity, material, range, size etc.) and amplifiers (power output, no. of channels etc.). Sounds simple I hope.
I have been thinking about this extensively and have come up with two solutions, each with its own pros and cons. My primary concern here is how the speaker and amplifier information is stored:
1) Tables specific to car regions (e.g. Rear Speakers, Front Speaker, Surround Speakers) have the fields 'Range', 'Material', Size, etc. Range and material can read their values from other look-up tables no probs. These would be linked to the main table via it's primary key in a 1-M relationship.
-Pros:
Ease of construction and general handling of data
Flexibilty/expandability (in terms of speaker quantity)
-Cons:
When it comes to data entry the form would need to be continuous and continuous forms I have found to be a pain in terms of referring to individual records!
I want some of these controls to conrol a diagram for my report (and input form) - such that they show/hide speaker pictures on a car diagram. Also, I have not yet figured out how to validate a continuous form in this situation or delete records from it either (should the user makes an error).
2) Again, tables specific to car regions but this time with a field for each speaker range (mid-range, tweeter, woofer, etc.) and it's associated data, tied to the main table via the main table ID in 1:M relationships.
-Pros:
Easy to manipulate form/report properties because every speaker has its own field
Easier to remove erroneous data (I imagine)
-Cons:
More complex construction (and less efficient) - e.g. material occurs more than once for each record so to look up material form a central pool requires an intermediate table
Many many more fields!
Non-expandable in terms of speaker quantity
Similar principals can be applied to amplifiers so I haven't mentioned those.
So what do you think? Or is there another way...
Hope that all makes sense!
Thanks for your time,
Bobadopolis
Hi ,
I am developing an access application that will be used by our Marketing Dept to create BRIEFS for our AD agencies.
The Brief has the following structure
1. Option1
1.1 A1
1.2 A2
1.3 A3
1.3.1 AA1
1.3.2 AA2
1.4 A4
2. Option2
2.1 B1
2.2 B2
2.2.1 BB1
2.2.2 BB2
2.3 B3
2.3.1 BB3
3. Option3
........................
........................
........................
The users will be basically selecting from a list of Categories,Sub Categories and Sub-Sub Categories.
I am a bit confused as to what kind of Database Structure should I use for this kind of requirement.
Right now I have a table tblBRIEF that conatins the basic information about the BRIEF like
Project Name
Company Name
Date
Responsible etc etc
My question is , where and how should I store the selections made by the user for each of the Categories , subcategories and su-sub categories.
Should I have a table for each of the Categories and Subcategories and have a relation with the BRIEF table ???
You advice will be apprecaited?
Access 2000: How can I populate a two field table (1. Table Name 2. Field Name) with the name of ever field within every table in my database using VBA code?
Regards,
Dalien51
Hi all,
This is a fairly simple one, but im not sure exactly how to go about it...
Lets say a Company can have many Divisions. Each Division has 1 Division Manager (CGM), and manages many Cost Centres.
Each Cost Center has 1 Cost Centre Manager (CCM) and employs many Employees.
Im thinking of 3 tables:
Divisions, Cost Centres, and Employees. And if you hadnt already guessed, CGM's and CCM's are part of the Employees Table.
What is the appropriate table structure and relationships?
Thank you in advance...
I would like to build a database to keep track of tardies and absences in my dept (~70 employees).
I need Date, Name, tardy or absent,
My current table:
TblEmployees
EmployeeID PK
Last Name
First Name
Team
TblEvent
EventID PK
Event (Tardy or absent)
TblAttendance
Date
EmployeeID
EventID
Is this a good structure? I need to be able to run a query that will sum the total number of tardies and divide by 6. That number will then be added to the total # of tardies. The query needs to only show the values over the last 6 months.
Any help is appreciated.
Thanks,
jason
Hi i have a quick question
Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.
My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?
Anyone have any views on this ?
Ur help will be much appreciated
Kind regards
Frans
Hi everyone. This is my first post here, I hope I'm making it to the right forum!
I'm currently experimenting with databases with an idea to move them online in order to create SQL & PhP driven websites. I'm a long way off actually making them live, though, and have become stuck today on trying to create the best structure for a specific database.
I've searched for example databases to see how other people might have solved this problem, but it's very hard to find another database with exactly the same issue.
I hope someone can tell me the best way organise my data! I'm racking my brain trying to reduce data redundancy and make the most logical database I can. I'd appreciate any help, thanks!
Basically my current database involves cataloguing a particular video game series. Each game title in the series has information attached to it (eg. platform, developer, trivia, data released etc.), but some games have spin-off titles with the same information stored about them.
If someone is viewing a spin-off title I want them to be able to see that's it's connected to a "parent" title and vise versa (someone looking at a "parent" title can see all the spin-offs).
This database is as much an exercise in creating a completely "correct" database as anything, so I want to reduce data duplication as much as possible (completely?).
I've attached a picture of my database as it stands at the moment (with no spin-off information added), I hope this might help someone understand my problem.
Thanks for any help! It's much appreciated! Databases make my brain hurt sometimes, and it's hard to know then "best" way to do something!
- Johnny W
PS - Here's the "key" to the diagram.
tblTitles - Game information
tblAlternates - The same game on different platforms (eg. PC, PlayStation, Nintendo Gameboy etc)
Actually, I think the rest is pretty self-explanatory! Thanks for any help! -J
i understand the necessity of foreign keys to link tables and create dynamic data structures..
believe it or not i have a degree in this subject, but am finding one little issue quite confusing...i shall illustrate this with an example...
THE RIGHT WAY TO DO SOMETHING - 2 tables.. table1(DRIVERS) table2 (JOBS)
DRIVERS
driverId
name
phone
JOBS
jobId
date
driverId
each job is carried out by one driver, and drivers can do many jobs throughout their lives...obviously. i learnt that in good practice, the above two fields should be used to link these 2 kinds of tables together..
to my knowledge.. to link the two tables like this in access.. i would view the 'Relationships' and drag one field to the next.. so the little line joins them and everyone is happy..
HOWEVER, i have created this database and have used this method...imagine my user adding a job to the database.. all i want them to do is choose the drivers name from a dropdown menu for each job. to acheive this i have used the 'lookup wizard'... this successfully associates the chosen driver to that job and when you query the database, the results are valid.
this link i have created has nothing to do with the Ids as illustrated in the above example. what i need to know is if i link the two tables in the 'proper' way using the Ids.. how can i allow my user to choose the drivers name when entering a job.. rather then choosing the drivers Id number on the form because that would be confusing...
i hope this post wasnt confusing..
am i mad...someone please explain to me???
I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?
View 2 Replies View RelatedIf I want to duplicate just a table, I can easily select "structure only" under paste options. Is there an easy way to do the same thing for an entire database (tables, queries, etc.) all at once?
In short, someone has a database. They're willing to share the database itself, but not necessarily its contents. The database has a user interface, so the people who use the database don't necessarily know much about Access. Therefore, I can ask them to follow a few basic, built-in Access menu options, but can't ask them to do something manually, such as copying the entire file and then manually emptying the tables. This seems like something that should be easy, but I can't find it. Any help?
Thanks,
Rhythm
Note: I put this in General original but it is rmore likely to be a table structure problem
I have a single table for storing the main data, this is fed by input from 3 forms. I have been asked if the forms can be used independantly and remotely as input forms.
My solution was to simply send out a database with just one form and created an append query so that when it is returned with completed information it would simply append the database to the main database. In itself this works fine however, what is happening, of course, is that when I get the other forms back I end up with triplicate records, that is instead of the information from the 3 forms being stored as one record it is now split over 3 records.
I have to be careful in what I do as the original database has been running for over 6 months now and has a lot of stored data and because of established queries/calculations/reports the last thing I want to do is change or split the main data table.
Is there anyway I can get the three records to concantenate on one line. The three forms all have separate fields as they were taken from the original database.