I am fiddling with a DB right now just to try to work out some table relationships. I have a PK, Name, it is accompanied by a year in school. I then want to relate the year in school in a different table with the requirements for that year.
such as
Year Requirement1 requirement2 requirement 3
these would be check boxes (yes/no), and I could accomplish it in this format, but I am trying to have a normalized DB so
I realize it should be like this
1st year requirement
1st year requirement
1st year requirement
but I do not know how to link this back to the PK name when switching it to this format, so that I can pull up a form that shows if the person has completed the requirements for the year they are in. I am I going about this wrong? I have been looking at normalizing DBs and relationships but can't figure out the logic for my DB thought.
I have a table of guests that holds all of their details such as name; address; medical info; family background; etc. What i need to do is to hold on the database if they would like to share a room and if so, who with.
I thought it would be possible to lookup the names of guests in the same table but access tells me otherwise.
I thought about creating a linked table of my guests table but when I link person A to person B then person B is not linked to person A which I would like to be the case.
I thought about creating a table called tblShare to hold 2 names from tblGuest but im not to sure how I would make this work. I think I may have the same situation as above. If I go to person A and link them to person B would this show up on Person B? I'm not too sure.
I will try this out now. If anyone has any other suggestions or some guidence on this I would be very gratefull.
I want to relate two records in one table, a parent child relationship. I can accomplish this task with two tables I realize. I'm wanting to write a database that will map a family tree. Internal to one table I want to be able to establish a series of relationships, parent-child, sibling, et cetra. Can this be done?
I have 3 tables joined as attached and Im trying to use the PO from the PO_Detail table to display the Description from the Material_Req Table. The two values are linked as the description in the Material_Req Table is for the PO in the PO_Detail table but I just cant get the results to show this.
I have tried several ways to validate my data with existing table I've developed.
Actually, I want the access to validate the data (Cellphone number) I've entered in the textbox by looking it in the existing table. So, if the data is there in the table, the message will pop up such as.. "Please continue". But if not, the message should be "this number was not registered yet.." and the textbox will be empty and ready for the new number...
I am using Access 2007 and have inherited a database. It has linked tables of Excel spreadsheets that are information extracted from our main system. The user wants developing a much faster and customized approach to billing the customer based on payroll related information.
There is a table of payroll billing transactions and it has the following record lay-out,
job_num, employee number, employee name, union group, rank, regular hours, overtime hours, other hours, trans_date, seq_num
On December 1st John Doe worked 8.0 regular hours as a pipefitter foreman on job number 6430 and this is record (sequence) number 254333.
There is another table that defines the union group. I am not concerned with this information at this time. The seq_num is a sequentially and uniquely assigned number given to each billing record in the main system.
There is also a Rate Master table and it has the following record layout,
job_num, union group, rank, regular_billing_rate, overtime_billing_rate, other_billing_rate
I am struggling as to how to relate these two tables so that I can develop some queries from it.
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
Hello. I have created a database to keep track of clients and their training requests. I have already created the tables and have some data entered. It is just now that when attempting to create a query with the two tables that I realized I have problems as I am getting a Type Mismatch error.
The first table keeps track of clients. Each client has a unique client number field which is an AutoNumber. The second table is a Training Request table that keeps track of the training requests for each client. Each client may have many training requests. I entered a Client Number field in this table too and that is how the two are linked by relationship. I set the Client Number field in the Training Requests table to be a Text field. I realize they are different data types but I didn't think I could set it to be an AutoNumber in the Training Requests table.
How can I fix this table design? Would I just be able to change a data type? I am very unsure of what route to follow.
Thank you in advance for any help that can be provided.
I have a database that acts as a front end for two different back-end databases. What I want to do is be able to copy a table from back-end #1 into back-end #2.
My first stab was to try to do a TransferDatabase on the linked table from b-e #1, but that just put a link in b-e #2, and not the actual data.
I created my database a few months ago and split the database into a front and back end. I recently added a new table on the front end. How can I move this table to the back end and re-link it?
my problem is that when i make a booking on my booking page it all saves, but when i go to the table where the bookings should all be kept, they all dissappear and only the recent booking i have made shows.
I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.
Can I create a table in the back end of my database using VBA in the front end? My client is using this system and I can't easily go to their location. I need to create a table on the back end and have it available in the front end applications. My thought is to create a front end only for the purpose of updating the backend with this new table. My idea is now to have it open with a button for the user to click - this will add the table to the backend with the required fields, confirm the update was completed and then close. This app then would be trashed so it would not be run again. (Though I would put a check in the VBA that if the table existed, the user would get a message box and the coding stopped to avoid overwriting the existing file.)
And once the table is created, is there a way to programmatically link it to the front end. I will be providing a new front end with this update so I could add something in the new version.
I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself.
I have a table with the following fields: Login ID, Description Password, Login Type, Location, Status. For every Login ID there are some old passwords that had different effective dates. I want my form or table to behave so that I can enter all the old passwords with the effective dates and keep adding to this record. I know it has something to do with subforms but I don't know how to do it. Please help. Thank you Yusuf
I have a main form, Contacts, for the user to input all the necessary data about a contact. What I'm ultimately trying to do is also include a subform which contains the name of each program (there are 12 or so) with a true/false checkbox. I'd like it to work so that if a program is added, it automatically gets added to the subform with a checkbox.
Also, I'd like the results of that subform to get dumped into tblProgram_Contacts.
Sigh...
A great vision... but I really am not sure on how to do any of this (specifically, properly defining the relationships and properly constructing the subform). Any suggestions would be greatly appreciated!
Hi all. This is my first post on this particular message board. I'm having a little problem getting Access to do what I want and hopefully someone here can help me out.
I have 2 forms. One larger form called RTV and one smaller one called Product Line. Now, most of the fields that are in the Product Line form are also in the larger RTV form. What I want, is for the product line people to enter information into the product line form. Once they're done, they exit the database and then, later, their managers look at this information in the product line, verify that it's correct, and then hit a command button that transfers all the fields that are the same on both forms, to the RTV form.
For instance, a prod line person would come in, fill out a date, part number and PO number and hit add record. Then, a manager would get on that same form, make sure everything is ok, and send the data over to the end of the RTV table.
Also, the product line people should not be able to see or edit the RTV form. Only a few people should have the priveldge of seeing both forms. Anyone have any ideas?
I did a search in here as well as Google to figure out why when I relinked the front end to the back end after adding 2 tables to the back end that the tables did NOT show up on the front end. Only the original tables that I began with.From what I have read there are different ways to do it. One suggestion on here was to create the table on the front end and export it to the back end. I guess I will have to do this going forward.
But my question is, after searching pages upon pages with no luck, since I already have the tables in the back end how do I tell the front end to find them and link them?
Hello, I have a pretty basic question about creating relationships in Access. When in the relationship window that shows all tables and associated relationships, I am a bit confused as to whether you are to drag the parent field to the child field or vice versa. When trying both, it appears to create the 1 to many relationship in the same direction no matter what way I create the relationship. So I am hoping someone will explain this a bit further to me or at least direct me to a reasource to clear up my, and possibly others, confusion. Sorry for my ignorance on this one! :o Thanks a lot, Dana S.
Hi I have tried searching for an answer with no luck. I am writing a table to do a filing cabinet archive.
I have a table with 3 fields in one is box no, second is item no and third is a description.
I have the first 2 linked as a primary key. The description is just text. I want to be able to enter a box no and to have the second (item) to enter a number automatically following a sequential no which is relevant to that box. ie box no Item No 1...........1 1...........2 1...........3 2...........1 2...........2 3...........1 1...........4 this one takes the next no available for box no 1 2...........3 and this one does the same for box no 2
if you go back to a box and start reinputting it picks up the next number. should i sack trying to do it with the autonumber?
I'm having a bit of a problem figuring out how to do this...
I have a form (single form) and a subform (datasheet which is based on a query which includes the data elements [via outer join] in the single form). What I'm trying to do is have the data sheet form include (view only) all records from the query and have the single form display selected fields for edit/entry (will update a table that is the outer join in the query).
Example:
Table A has "Policy Number", "Line of Coverage", and "Insured ID". Table B has "Policy Number", "Insured ID", and "Insured Name".
Query 1: SELECT A."Policy Number", A."Line of Coverage", A."Insured ID", B."Insured Name" FROM "Table A" A, "Table B" B WHERE A."Policy Number" = B."Policy Number" (+) AND A."Insured ID" = B."Insured ID" (+)
(+) means outer join (sorry i'm used to using oracle).
So in the form I have a single form (FORM X) bound to Table B and a datasheet subform (FORM Y) bound to Query 1. I would like the user to be able to edit (or add if it doesn't exist) the "Insured Name" in FORM X based on the record currently selected in FORM Y.
Other things that may monkey with the process are the fact that even though Table B will have a unique key based on "Policy Number" and "Insured ID" Query 1 (and Table A) may have multiple "Line of Coverage" based on a single "Policy Number" and "Insured ID" combination.
Alternatively not relating the two forms, but simply populating the FORM X based on the record selected in FORM Y would be sufficient. Would this involve populating FORM X using the "on focus" function and how do I use vb to reference to a specific record in my subform?
Please let me know if I am not making any sense and thank you for your help.
I've got a query that uses a key from one table to pull back records from another.
My question is...
On the table with the key there is another field that I want to use to pull back data from the same table from above.
A bit more info...
Table one holds colleagues details Table two holds records
Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?
I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on. Any query or any code that can pull back end table stats?
