I'm working on an Access project at work, and I was hoping I could get some pointers/tips on my data modeling. Basically, attorney information is being kept on an Excel spreadsheet, and I ported it over to an Access database. About half of the firms have a contact attorney, the other half doesn't, but regardless of attorneys there is still data for each firm.
In many cases, one firm can have multiple locations across the United States. Also, there are unique records that pertain to a firm that are spread out across each of their locations, and there is also information that is unique to each individual location within the same firm entity.
My thought process is the following. Each attorney has a location ID, pertaining to a location in tblFirmLocationAttributes (LocID). There is a FirmID key in tblFirmLocationAttributes that connects a location to the firm it belongs to, which is done via the FirmID key in tblGlobalFirmAttributes. Many attorneys can work in the same location, and there can be many locations that pertain to the same firm, and I set up my relationships accordingly.
Attached is a picture showing my relationships. If you guys can offer some tips and/or advice, it would be greatly appreciated! This is my first time using Access extensively, so i'm all ears for suggestions/constructive criticism. Thanks!
Hi all, like OMG for UML is there any org that controls , standardizes the data modeling techniques. I simply want to ask that what is the primary site whre i can get all the info about data modeling? simply the exact definitions of the terms that we use in data modeling? how many data modeling techniques are there and all about data modeling.
My query is really to do with the best way to add new records to their database.
The database has four tables:
[Species] which is linked to multiple loci in the [Locus] table. For each Locus their are multiple alleles in the [Allele] table. Additionally, there is the [Reference] table, each reference can be linked to one or more loci in [Locus].
Users could add a new species, it's loci, the alleles for each locus and select the reference for each locus from a list (or add a new reference) using the Form used to view the data. However, this is very time-consuming when there are a large number of species to add but is easiest for me, as it requires little or no extra coding.
The other way I was thinking, would be to do a kind of batch update from an excel file. This would suit the users better as their data are already in this format.
The problem is that I guess I can't do a simple import spreadsheet due to the one-to-many relationships, as there would be no primary/foriegn keys in the excel sheet.
Would the best way around this be to add the first species, then for this species add the first locus and its alleles, then the next locus and so on.. then the next species? This way I could use the keys as they are generated..
Alternatively, I could get excel to generate the keys, and query the Access database to make sure it is not generating keys already in use. Then I can do a more simple import procedure...
I can do either using VB. Which do you think would be 'best'? Or should I just tell the users they'll have to enter stuff by hand the long way?!
Hi. I am developing a db for juvenile salmon-focussed fishery survey data and have encountered something of a conundrum which I could use some advice on. Apologies in advance for the length of the post.
Background Juvenile salmon move from freshwater to saltwater. During this transition they require time to adapt physiologically and are thought to seek out nearshore areas with intermediate salinities, or with freshwater overlaying the saltwater. They also experience problems with elevated temperatures.
We are interested in tracking salinity and temperature information at each site where we sample for fish to aid in interpreting our catch results.
Data Collection Our convention is to collect temp/salinity at the surface and at 3-feet below the surface wherever we beach seine (or just at the surface if the site is shallower than 3-feet). However, we use a depth-temp-salinity data-logger attached to the lead-line of a lampera net for openwater sets. The logger provides measurements of depth/temp/salinity every 5 seconds during the set, down to depths of 20-30 feet.
So, for some 'sets' we have one or two measurements of depth/temp/salinity, and for other sets we might have over one hundred measurements.
Problem
1.How best to get that data entered into the db? 2.I'm just starting to get my toes wet with VBA
Ideally, I could directly enter the values into a subform for sets with only one or two measurements, but could instead 'import' the extensive data for those sets where the logger was used. Entering the logger data manually would be ridiculously time-consuming.
Existing DB Setup Records for temp/salinity subform/table linked to other set information by a unique Set_ID field.
Subform for depth-temp-salinity information bound to a dedicated depth-temp-salinity table. The subform is currently viewed as a continuous form.There would be one excel file for each set where a data logger was used, but no excel files for sets where no data logger was used..
My thoughts so far.
Somehow create a subform with the ability to enter up to two records manually or else click a button that imports the data from an excel file. One thought is to pop open a window to navigate to the excel file that contains the data for that set. However, I'm thinking that if I place all such excel files into a particular directory and name them using the appropriate Set_ID number convention, that maybe clicking the button with be able to find the file directly, without navigation required, and bring in the records automatically.
Is this possible? How would I go about creating a subform that provides both an 'import data' button and allows for manual data entry of up to two records?
Can anyone show me a similar example for both the data entry (form) and for how to automate the importing of data from excel files to append to an existing database table?
Aim: The eventual goal of this is to have a command button that could be clicked on the form/subform that would produce a popup window containg a scatterplot graph of salinty versus depth. another button to produce a scatterplot of temperature versus depth. A third button to open a line graph with time on the x-axis, and temperature/salinity series on the y-axis. Before I can get there, however, I need to get the data into the table somehow.
I would appreciate any input/advice on this matter, (especially custom code! ;) ) As, I mentioned, I'm just starting out in VBA and I have a lot to learn. I know how to open a MsgBox, but have no clue on what the command is to open an explorer 'window'.
I hope the problem is sufficiently interesting to generate some response.
Hi, I'm currently putting together a database for a medical NGO in Cambodia (http://www.medicorps.com/updates/cambodia.html) and am looking for some advice. The simple database is for logging client referral cases by Cambodian doctors to a team of international doctors. I haven't used access in 10 months and despite programming in access for 5 years progress is very slow. At the moment I'm designing the input and search forms. I was thinking that a more logical approach would be to convert the forms to access data pages and put the database online. I haven't used data access pages but from what i know their fairly limited? The goal would be the ability to log/search the data with auto updated pull downlist based on the actual data. Ultimately I want the data compiled and emailed to a email list from withing the website. The trouble is I have no idea how to do it.
Not sure if anyone can help but I have an issue I would love to sort out.
Each week I load several excel spreadsheets into an access database (one table) in order that I can check for duplicates across previous weeks and that week (with in excess of 20,000 records on each excel sheet). I created a find duplicate query to identify the records so I can use it to obtain credits. Unfortuantely I am not in control of the data coming to me (or else I would prevent duplicates at source)
Im not sure if this is the best way to try and do this or not. Any comments are greatly appreciated.
Im in the process of building a database for a friends business, and im a bit of a newbie with access. Id like to get some opinions on structure and overall how i should build the Database. My goal is to have two types of clients ... donors and buyers. A client can be both a donor, a buyer or both. When a client is a donor, they get a certain amount of credits added to their account. When a client is a buyer, they will be purchasing those credits from a donor. heres an example of what i want to accomplish; John smith donates 500 credits; I enter John Smiths info and credits into his profile; Jim Doe buys 100 of John Smiths credits; I want the DB to automatically update Mr. Smiths Credits, and then add 100 credits to Jim Does profile. Also, I want John Smith to be able to purchase credits from Jane Johnson, and again, have the credits added to John Smith and deducted from Jane Johnson automatically. Get my meaning here? The tables will also contain the typical client info ...ie; Name, Address, Phone, SSN etc... Can i/Should i do a seperate table just for credits and link it to the client tables? Should i create seperate tables for Buyers and Donors?
Also, I have an excel spreadsheet with formulas to do credits already, but when i tried to import it into a table in access, it didnt work so well. Any opinions on table structure, design etc would be greatly appreciated Thanks all for lookin in
Hello to all, i have a non-windows application and i would like to create a vb program to print invoices. I would like to send to this program a txt file with all the values (qty, vat, customer name etc with vertical & horizontal positions in the form etc..) and then superpose all i need to print with an image (gif or jpg wich is the my customer invoice presentation. In fact i have 2 layers , one with all the value i print and another with the invoice image background. I'm a beginer with VB, so i need advices to create this program, maybe someone did this already. Thx in advance VINCENT
Im fairly new to access and im having trouble constructing a stock control system that can create sales orders and adjust stock levels accordingly, hold customer details linked to sales orders. Ive spent about 20 hours trying to do this and its just pickled my brain, ive searched everywhere but sometimes im uncertain what exactly it is im looking for. Can anyone give me some pointers?
I have 7 tables at the mo but its 4 of the tables i need for the sales order:
tblcustomerdetails customerID,first name, last name (general customer details)
what im trying to do at the minute is contruct a subform for a form that would require entering the products into through a combo box selected by productname and then autofill the product description and listprice. Ive ended up deleting all my forms and queries because nothing seemed to work right. I will then add this sub form to a form containing all the customer information and the total price for the subform this then needs to be output to a report for printing, but i can figure that out later. Ive attached my database if anyone wants a look if you dont understand my jibberish.
I was wondering if this could be done in Access. Let me explain
I work at a candies manufacturer in Puerto Rico. Right now we are not tracking any kind of inventory. Is it possible to efficiently track our kind of inventory ( raw materials, work in process and Finished Goods) in Access?? Maybe using a bar code system??
I would like some advice or opinions from people who have worked with access and mysql.
Currently we run a large database in access which holds around 3500 records. It is actually running quite slow at the moment. What would you suggest to speed it up? ive heard running it on a sql server but i dont have the info to know if this would be correct.
Also i was thinking or changing the access database and getting it fully redone in mysql why would this be more advantageous?
Also i havent any knowledge on MySql is it easy to learn for a beginner? Do you have any information such as websites i could visit to learn or sample databases? Or would it not be worth me learning it? What would you see at the front end and back end?
My client wants me to make fields from different tables on the same form which he wants to use for input. This has made it very difficult for me as my queries have to involve a lot of outer joins and in some cases full joins.
I'm trying to set up a database, which I've done before on different programs, but I'm new to Access. I have a rather elaborate plan but am not sure it's actually possible.
I would like to set up a system that will effectively take input from the user within a record on the database. In simplest terms I'd like to set up a form on which the selection of a value for one field for a record affects the list of options available for a second field. As a basic example, say there are two fields: Input with possible values Red and Blue; Options, with possible values Red1, Red2, Blue1, Blue2. Ideally I would like to set up a form on which if Red is selected in Input, the options Blue1 and Blue2 don't appear in the Options box. Crucially you can also then select Red1 or Red2 as the value for 'Options' for that record (as opposed to just having a text box with the options written in it), as this provides the potential for a string, with the selection of a value for Options affecting another field.
Obviously in reality there will be many potential values for Options, and it won’t be obvious to the user which are compatible with each value for Input.
I wanted to use Program Flow functions with a combo box - say for the Record Source: IIf ( [Input]="red" , "red1;red2" , "blue1;blue2" ), though this would probably need to become a Switch/Case/Break command in the real database - but I don't think you can input equations into the Record Source.
I've also thought about trying to use queries, but can't see how it would work either, (the form for every record is the same, so the combo/list box for Options will always have the same properties. Switching between forms based on the value of Input seems impossible).
Then again perhaps I'm trying to make a database do something it wasn't really designed for, and should go back to basics and just display the possible options in a text box that is dependant on Input (but this way I won't be able to use the value of 'Options' in a further process).
I'd really appreciate any suggestions, especially since I'm pretty clumsy with the system still (first day using it, oh joy) and so could well be missing an obvious solution.
Hi, I'm still an amateur at using Access and have just recently been introduced to normalization.
I'm looking for some advice on how to proceed with a database I'm trying to create. I need the database to store vehicle information (name, make, model, color, license plate), along with parking information (date, time, place, who issued the notice)
My biggest question so far, is finding an efficient way to list a vehicle with what would be an undetermined number of parking slips. and then of course being able to retrieve that information on one form.
I tried using a from for VehicleInfo with a subform for ParkingInfo but I'm not getting the relationships right, the parkinginfo form is not displaying all the information connected to the license plate when the main form shows the vehicle information..
if that makes sense, any help or advice on how to proceed (or begin) would be greatly appreciated.
I have been developing a catering order system at work. A demo version has been in test and initial issues sorted. The users are very happy with the way it works and though far from perfect it does everything they asked for and then some.
Basically, each order for refreshments/food creates a record and order number. Orders feed through to a daily 'jobs' diary sorted by date/time which the catering staff work from.
However, what they are asking for now is to be able to link some records together for collation/charging purposes. Grouping using the customer ID and the order Date doesn't work as customers could have many orders across many dates , and some of the orders by the same customer won't need to be collated together. My initial thoughts are to add a unique code to each order that needs to be linked , has anyone any ideas on this , is there an easy way to generate a code (perhaps CustID, OrderID , Date, other?) which can be added to other records to 'link' them.
I would be grateful for any suggestions.(other than a complete redesign :eek:)
I was hoping someone could offer some advice on how I would design the following project:
Student Table - ID - Name - Unit (each student belongs to one specific unit) - License type (each student could have multiple license types)
Unit Table - Unit Name (string)
License Table - License Type (string)
I have created a report that dynamically updates information according to what unit the student belongs to via a drop down box, i.e. while the report is open, select a unit from a drop down, press a button to apply the filter and the report automatically updates. I want to add the same kind of functionallity to the report based off of licenses as well. My original design had all license types in the Student Table as a yes/no option. I couldn't get the filter to work properly so I moved license types to its own table (which makes more sense anyways...) But, unlike the Units Table, any One student is allowed to have many licenses so this creates a bit of a problem. If anybody has some insight on this I would much appreciate it. If you're not following, please let me know and I will try to be more specific. Thanks.
Hi. I just recently started studying Access independently since my school never taught it to me and I'm trying to design a invoice type of database as a summer project. I'm stumped on queries because the office 2000 guide I have only briefly goes over it.Basically, what i'm trying to do is create an automated value like in excel so that the "Net" column i have will subtract with the "sales" column to automatically enter a value for the "profit" column. I can't find any place for me to enter anything like [profit] = [net] - [sale]. i tried to use the input mask but since my data is in currency, it won't allow me to do it. can anyone please tell me where to start or what i've been doing wrong? thanks.btw, i'm also trying to do the same thing with the y/n feature of access. i'm also trying to find a way so that if i type y/n for a column, it will copy the value from a different colum so say i put yes on "account R" then i want the "AR$" column to copy the value from the "sale" column automatically. if i can solve this problem the same way as the previous problem then please ignore this (i THINK this can all be solved with queries.)
I have an assigment and have to create a database, i'm just starting to learn how to use access properly.
there's a screen of a form I made, if anyone has some advice to make look better it would be welcomed. As you can see it is very basic.
I also would like to know if it possible to create a search bar, for example typing in "sales" and the list of all the candidates working in sales comes up (I know how to do this in a query, but how do you transpose it into a form).
For now I have 20 comboboxes on my form each bound to a field from my sourcetable. Since ya can only choose 1 value in a combobox, the users want to to choose multiple values in each box. How should I implement this? I can't use 20 listboxes because I haven't got any free space left on my form.
I am creating an incident database for students at a high school. At the moment I have one table for the students with a studentID (Autonumber) that links to an incident table in a one to many relationship.
My question is as I have many different types of incidents taking place, e.g. student on report, phone call, Referal from teacher, medical incident, exclusion etc... would it be better to have a table for each type of incident or keep it as at present.
Hi there, Being new to Access and table relationships I need advice on the table design I have so far. A jpeg image of the table relationships can be viewed at www.joyceandstevieb.com/dbasemap.htm Do I need to include foriegn keys to counties and countries in the address tables? Or will the connection from city to county and then country suffice? Also, could I trim down the address and contact tables to just one of each? I don't know how I could differentiate which is customer supplier and haulier though. Any help would be appreciated.
I have created an MS Access 2000 solution for a company that utilises replication and remote synchronization. The company have about 12 people working out in the field (on laptops) who use replicas of the database (held on the company's server). The solution has become quite unstable and the amount of database conflicts is growing daily.
Could anyone suggest a more robust solution for the senario described? Would MySQL or MSDE be a more stable option? Is there anything I can do to make the MS Access solution work?!!
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.
Hi. Your help is very appreciated. I want to upsize large MS ACCESS(2002) app to MS SQL SERVER 2000. [ By "large", I mean 250 queries, 78 tables, 110 forms]. The app will be used by 25 concurrent users.(therefore the need to upsize).
I have time constraints of 2 weeks to deliver.
My questions:
1. In order to finish it asap, upsizing ONLY the tables - might be a good solution. However, will it work with a workload of 25 concurrent users?(read/write).
2. If upsizing all the tables, would it be possible to upsize SOME of the queries and leave the rest untouched? If yes, what is the process to do it? That will save me lots of QA time (there are 250 queries). Mind you it's not simple, since the forms need to reference Stored Procs as well as ACCESS' SQL queries.
3. In the upsizing documentation, it says that there might be situation that the query will be upsized , no errors will appear in the log BUT it won't work anymore :mad: . Do you have any methodology for QA the upszied queries in order to ensure the system's robustness?
