I've been asked to design a database to hold information from a proforma based on tracheostomy insertions. The information breaks down into two components; patients and tracheostomy details.
From this I have designed two tables:
1) tblPatientDetails
- PatientID (autonumber) - [primary key]
- 12 fields (text / numbers) - relating to demographics
Each patient can have more than one tracheostomy inserted over the course of their care, so there exists a one:many relationship between the tables. The two tables are therefore linked (one:many) from tblPatientDetails:tblTracheostomyDetails based on the PatientID fields in both tables.
OK here's my question; I want to make the inputting of these proformas as easy as possible for the person doing the data entry. Ideally I would like:
a) a single form which collects the data and enters it into the two tables.
b) a method of searching the patient details upon name entry and if they already exist in tblPatientDetails, to autopopulate the rest of their demographic information in the fields, but to leave the tracheostomy details blank (as it would be a new entry for that table).
My attempts at using the following solutions failed miserably:
a) a form based on a query of both tables - this allowed me to put the fields from both tables on the same form and enter information, but when I searched for a patient's details to enter a new proforma - it populated both the patient & tracheostomy details as well (i.e. didn't really let me add a new 'many' to the 'one' record).
b) two separate forms (patient & tracheostomy), with a command button to go from the patient form to the tracheostomy form. This didn't work because I couldn't pass on the PatientID variable between the forms to form a link between the tables - and there ended up being two separate tables of information. :S
c) from looking around at the various forums, I understand that a sub-form is useful in these cases but I can't seem to get the formatting right (I would like the form to appear as similar to the paper proforma as possible), but my recent attempts at a sub-form resulted in a datasheet view of the tblTracheostomyDetails, which isn't very useful as it contains so many fields.
I'm now left at a loss what to do, so any help would be gratefully received.
Hi all Its my first time on this forum so please be gentle with me ! I have a list of numbers stored in a field (wieght). I have another field that is the product field. I also have a list of letters (A-D) Now, the weight can be 1kg (through to 20kg) A-D hence four different values in my product field. I need to have a form that the user can choose a letter then enter the weight to show the correct product. I can get a dllookup function to work fine for the fields involved but i need a control to filter the different letter combinations Anybody point me in the right direction
I have a list of about 200 crops on a column, and for each crop I have 6 information (income, amount of land, season, etc.) in the following 6 columns. I have this information for about 100 households in 6 villages.
I am now starting to put these info in the computer, and it is an enourmous task. I thought that one way to do it is design a form in Access, and have one file per village. I then want to analyse the data at the household level for each village, and also analyse the data at the village level (after calculating village averages).
I want to recreate the form I used to interview farmers in Access, but I can't figure out how to make Access automatelly have 7 columns in form view (the first with the name of the crop, the next 6 with the information: income, etc.). The idea is to reproduce as closely as possible the form I used to interview the farmers, so as to facilitate the data-entry.
Does anybody know how to let Access automatically sort all the fields into 7 columns? Of course I can design the form manually (putting each field individually in its right place), but then I have 7 x200 fields to move, and it would take a huge amount of time. I can't believe that Access can't do this automatically, but I can't figure out how.
I then plan to transfer the info for analysis to SPSS. Does anybody foresee a problem?
Do I make sense? If anybody can help, I would be very grateful. This is actually very urgent, because I am going to pay somebody to do it, but I have to tell her how to do it over the week-end. Thank you very much!!
I have a access form that has a text box that holds a search term. The search term is used as a variable in a query. The query results populate a list box. Selecting a result in the list box populates the rest of the form where the record can be edited. All of that works great...sort of. When first opening the form you cannot view any records. The search function works fine and the list box populates but selecting a record does not populate the rest of the form. Attempt to navigate (next, first, etc.) and you get a "You can't go to the specified record" dialog. There is a simple fix for this. Open the form as normal. Then switch to design mode. Do absolutely nothing. Switch back to form mode. All is well.
I'm working on a login form and *not thinking* I removed access to the submenu. I had already set the form up so that you either login correctly or exit the program. I'm working in code for the form and I know it's possible to reactivate the menu in code, I just don't know how. And I can't switch to design mode. The form is set up to not allow anything to happen until the correct login has been entered, and I either had a typo when I set up my test login or the program is bugged and only says it's incorrect....
Why the ability to view the properties of an object within a form is not available when you double click on it in design view?
I was happily working away double clicking on a command button to edit some code when for some reason the next time I tried to edit it did not open up for me.
I was unable to access it even by right clicking on the object & selecting properties as that also appears to be disabled, not greyed out or anything but just does nothing when selected.
Have I inadvertently changed a setting somewhere that prevents the properties from being displayed?
I'm trying to create a validation rule in a microsoft access table that would require the entering of data in a "comments" field is another field's check box has been checked. What is the best way to go about doing this?
I have made a table(picks) with fields PickID(primary AN) Username Weeknumber winninningteam pointvalue
The data is entered through a web based(asp) form. It is for a football pool. The problem i am having is trying to figure out a way to show the picks that are made for each person. I would like to be able to show a table that has columns
Name Week1 week2 week3 ... week17
so that people can see the picks they have made all week. I tried creating a query using the wizard but I havent had any luck. I am guessing that this isnt a great way to structure the table but it is the only way I could figure it out. Any ideas on how to get this done?
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
Hello, I must say that I am not very experienced with Access.. hence why I most likely have ran into this problem. However I did try to read manuals and tutorials to get me through it. (Failed :) ) Hopefully somebody from these forums can help.
The database that I am trying to create is for a Door-to-Door marketing company. They have many employees(Reps) that are organized in teams(With team leaders). Moreover they sell more than one product. (SKY Television, 3G Contract Phones, 02 Contract Phones, BT Lines etc...) Hence what I tried doing is:
1. Create a common customer database M_Customers (At first I tried to what I think is "normalization", so I had C_Address and C_Bank_Details) but that lead me to some problems. This M_Customers table has all the needed information for all the products. (However some information is not needed for some of the products)
2. Create a common Employees database M_Employees, which lists all employees and their details. (Including Address and Bank Details) Moreover I have created a M_Commission database, to list all the levels of commission for different type of employees (on trial, mid level, top) and for all the different products.
3. Then I went ahead and created Quaries for Q_Phone_Deals and Q_Sky_Deals... but they did not input data into the database. (:mad: "The same record is needed in M_Customers" :mad: ) They linked a M_Customers with C_Phone_Deals and respectively C_Sky_Deals (Those two tables contained information about the phone deal or the sky deal... like "Network" for and "handset type" in C_Phone_Deals and "Install Date" and "Installed - Y/N" in C_Sky_Deals. ) These quaries (I thought) would be the main inputting devices, as they would also have a Employee_ID lookup in order to see who did the sale and then somehow calculate the commision that he or she deserves.
PROBLEM: As you can see I have made something horribly wrong with my relationships and probobly the whole design. As I cant get the common customer database to work, and link it to C_Phone_Deals and C_Sky_Deals and later to more products. Moreover I am not sure how to make the commission system work.
We upgraded to ms access 2003. We used to be able to make design changes on just about anything in access 97 IF a user was not in the particular record/form/object. Now it seems we can't do much of anything in access 2003 unless in exclusive mode. (and we have many many users 24 hours a day) so it's even hard to import changes made off-line.
Is there anyway around this in 2003? Any other suggestions? Other than calling all users on the phone to tell them to log off (and some are logged in and not even there), can we 'boot' everyone off (although this really is not a good option for us either). This is a production database.
It would be good if we could just make quick changes as needed when we want as it was in the version 97.
Ok, I am getting to know MS Access much better than now that I am asked to design a MS Access database...
We have a small company of 20 users. The reason for the database creation is to keep track of the hardware & software inventory that we have. Sometimes, users borrow softwares or laptops and we want to keep track of it. My boss also wants to know how many workstations and how much memory we have in there kind of things. You know, basic inventory...
So, we have 3 servers, 20 workstations, 5 printers, licensed softwares, few hubs, and router... Nothing fancy, a small shop.
So to design this database, I read some MS Access database design guidelines... However, I am still not clear whether I have set it up right because I learned that design part is the toughest part. If the design is good, then rest should be easy... At least right?
So I am thinking about creating 2 tables. Why? Our inventory really is hardware and software, I think. I don't think we have other things...
One table I am thinking of creating is Hardware_Inventory and the other Software_Inventory since that is our goal to create an inventory to keep track of what we have, who has it, where is it, when did we purchase it, how much is it, and what is in the hardwares?
So I created in each table - fields, but I'm not sure whether this is done correctly. Again, I wanted to answer few things if someone were to look at the database: what is it, where is it, who has it, when did we purchase it, how much is it for soft and hardwares, and what is in the hardwares?
Note: Property_Code is going to be a key number or a property tag I will get to put on it and have it as a Primary Key.
Hardware_Inventory_Table: Property_Code Description Make Model Model_Number Year Serial_Number Purchase_Date Purchase_Price Warranty Department Location On_Loan_To Duration Checkout_Date Checkin_Date Comment
Software_Inventory_Table: Property_Code Description Vendor Version Product_Key Year Purchase_Date Purchase_Price Warrnaty Department Location On_Loan_To Duration Checkout_Date Checkin_Date Comment
Then I was going to link the Year, Purchase_Date, Purchase_Price, Warranty, Department, Location, On_Loan_To, Duration, Checkout_Date, Checkin_Date, and Comment....
I need some expert advice whether I have it set up right in the design, whether I have chosen the correct fields, and whether I have made the correct link? Or anything that I need to address or re-design...
Hi all. I'm trying to figure out how to make a SQL query require ALL of the data in one record match ALL of the data in another record. I may be using the wrong term when I say record.
What I have is a job order form where I list order details. I list location, start date, client all of the skills the job requires. I also have a candidate form that lists candidate locations all the skills they possess. The skills for each form are drawn from a SkillsInventory table. Once I enter all of the job info into the job order form, a subform lists all matching candidates based on state, availability/start dates, skills and a couple of other items. Everything works fine with the exception that I am returning any candidates with any of the skills in the job order verses only those candidates with ALL of the skills I select for the job order.
I would assume I would enter something under one of the columns in my sql builder/design mode. If I view SQL view, here is what the statement says:
SELECT [EmployeeSkills].[SkillID], [JobOrderSkillsList].[SkillID], [Employees].[LastName], [Employees].[FirstName], [Employees].[MiddleInitial], [Employees].[EndDate], [JobOrders].[OrderID], [Employees].[City], [Employees].[DoNotCall] FROM ((JobOrders INNER JOIN Employees ON [JobOrders].[StateID]=[Employees].[StateID]) INNER JOIN JobOrderSkillsList ON [JobOrders].[OrderID]=[JobOrderSkillsList].[OrderID]) INNER JOIN EmployeeSkills ON ([JobOrderSkillsList].[SkillID]=[EmployeeSkills].[SkillID]) AND ([Employees].[ContactID]=[EmployeeSkills].[ContactID]) WHERE ((([Employees].[EndDate])<=Date()+14) And (([Employees].[DoNotCall])=False)) ORDER BY [Employees].[EndDate];
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
Basically I need to design form that looks very similar to either Split Form or Subform. I have attached a print screen of what I roughly need. The form is split into two parts. In the first/top part there are some List Box/Date fields that act as filters to the bottom form, so the bottom form displays only records that match values in red. The second part of the top form has some field that require input from user and then these records are added to the bottom form. I was trying to use both Split Form and Subform and none of it is working,I have attached an example of database and this form would be based on data from Query1.
Pl. help me in designing a small application..This app. is to be used by 12 users. We need an application to log time spent by the team in office.so this is a time sheet system. I know how to design a table, a form etc..My question is how can many users share the same database?
Let us say that on my computer I have created the database as db1.mdb. (it has all the tables , forms etc) Now for other users to use this what do I have to do?
Also can MS Access be used to make an application which supports simultaneous use by 12 persons?I mean can it support such an app?
Will be most thankful for your inputs..This is urgent.
I have an application that when in design master shuts down when I attempt to open a certain form. I have traced the problem to a linked field from another table, by creating a new form and ommitting the said field, problem is its a required field!
If I copy the database (Backup) and run it as a replica it does not happen, if I then convert the replica to design master it happens.
I'm struggling with a table design. My problem is how do I keep the history for one sports franchise no matter how many times they move or change their name? I want individual team info as well as franchise team info.
i.e.
Team #1 Brooklyn Dodgers has many transactions
Brooklyn moves to LA
Team #2 is Los Angeles and they have many transactions
etc, etc.
How would I build my tables if I wanted to write SQL statements to give me all transacation from the entire franchise? I only need help with the teams. I already have a junction for the transactions.
ie All Transactions for all teams who started with Brooklyn. This will grow to 5-6 teams eventually.
My initial idea was a self join in the teams table
Code:tblTeamsTeamID PKTeamNameHistoricalTeamID
TeamID = 1 TeamName = Brooklyn HistoricalTeam ID = 1
I'm working on an Access 2007 database and any changes I make to a form or control will not save. I click the save button, and try keyboard shortcuts but when I close the form I get a prompt "Do you want to save changes to <whatever>":
If I click "Yes" the changes still do not save and I when I close the form it starts the cycle over.If I click "No" the form closes and the changes are not saved (obviously).
There is no VBA in the project but I've still tried a recompile as suggested for older versions of Access but no luck. There are quite a few embedded macros but I'm not sure how they could be causing this.
Access 2013 on Windows 7 ..I have a Main Switchboard form which is Pop-up and Modal. Everything on it is working as required, but if I am in Design View and try to switch directly to Form view, the form disappears and the Access screen freezes - the mouse moves, but no menus or objects can be selected. If I have the VBA window open, I can carry on working in it, saving the modules and closing that window as usual. But the only way to open a form or do anything else in the main screen is to kill Access by closing the Access window in the system tray (or using the Task Manager).
If I close the Switchboard when in Design view and then open it in Form view, everything's fine.
The only recent change I can think of is upgrading from a 32-bit implementation of Access to 64-bit.
employee personal recordemployee job statusleave recordinventory record issue to employeejob performanceinventory control of equipmentforms, queries, tables, login forms.
