Identifying Table Links/relationships
Mar 22, 2007I am new to Access and just inherited a very large database to work with. Is there an easy way to figure out the relationship between each table and how they are linked? Thanks.
View Replies
ADVERTISEMENT
Removing Table Links
Mar 2, 2007This may seem like a silly question, but I can’t find the answer to it on this forum. People only say 'Remove the table links' but never how. I can’t seem to be able to remove the table links completely, so I only have one database file (no backend or frontend). I can’t seem to do it though the Table link manager or any other way. Any help?
Thanks in advance
Table Name And Links ---URgent
Feb 28, 2008i have 2 mdb and both mdb have table link
in one of the mdb , they had a link to dbf (paradox) and they have lot of queries and reports created.
Now the functionality has changed so we have created a access table and need to replace the dbf with the table.
The problem is we need to retain all the queries and reports as such but the table will be access table instead of dbf.
Example
First.mdb has test table
Second.mdb has a table called old.dbf. and associated quries
but now test table is been linked to second.mdb and we got to rename or delete old.dbf and associated all queries and reports pointing to test table
is there a way to do it automatically or should it be done manually?
Please let me know as it is urgent
Combo Box That Links To A Table
Jul 31, 2005I wonder if you can help. I am trying to make a combo box with peoples ID numbers in which links to a table with that persons details. I have made the combo box with the names in but now trying to link the table to it.
For example: Fred is in the combo box with ID number 1 and the user selects him and wants to see his details about him after clicking continue. The form has autonumber 1 showing Fred's details after clcking this.
Any help
Cheers
John :cool:
Automatically Change Table Links
Feb 13, 2006I'm creating a leave (holiday) database but each year must be kept seperate for analysis. (I have split the front and backend of the database) I thought of using a dropdown on the main page which could be used to select the year which relates to the leave being dealt with. Depending on the choice selected (present financial year as default) the links would update to the required database.
Any ideas?
Identifying The User
Feb 14, 2007I have made some accounts with the user-level security wizard, and i was wondering is there a way to know who logs in?
Im wanting to give a person preset information on forms and stuff.
Identifying Updated Records
Oct 23, 2006Hi
I have my table set up to add today's date as a "date last updated" field. this works for all new records but I need to flag instances where a user has amended any existing records, so that on a daily basis I can filter out anything new based on the "date last updated".
Any ideas how I can flag changes to existing records?
Apologies if this is a straight forward question I'm fairly new to Access
Identifying Duplicate Records
Jun 26, 2005Be advised that I'm a complete novice with Access, and have used it only to view Excel files with more than 64,000 records. Any help would be appreciated.
I have an Excel file of 10,000 records entered by salesmen of possible prospects, with salesmen, prospect name, address fields. Some salespeople have entered the same prospect and address information; the only thing different in these records are the salesman name.
I know how to import the file into Access. But from this point, how do I construct a query and generate a report that shows me ONLY THE DUPLICATE RECORDS? We would like to use this report to identify the double entries and the salespeople so they can clean up the database. I know Access (I have Access 2003) can create this query, I don't have a clue on how to do it.
One hint: A post on another forum suggested " Make a group-by query (grouped on all fields except salesperson). Add a count field. Select if count >1. This should do the trick."
I'm sure this is the answer, but I don't know how to "make a group-by query", how to "add a field", etc. Like I said, I know how to import the data. That's it.
This is extremely frustrating to be this close to the answer and not know how to finish it. Any assistance would be appreciated. Thank you.
Identifying The Active Form
Jan 17, 2006Apologises if the answer is out there somewhere but I have searched to no avail.
I have a number of Forms which the user can jump around from one to the next. All the forms remain open with the form they are currently viewing showing on top. If they make a change on one particular form then when they return to a previously viewed form I want to disable or hide certain certain controls etc.
I know this would be simple to do on the on open command but as previously mentioned as I am not closing the forms after they are viewed, if the user returns to a previously opened form then this is not picked up.
Soooo, basically I'm after something like
When the user moves from one form to another already opened form for the new form to immediately know that it now has the focus and for it to do something along the lines of on open.
Any help will be appreciated
Identifying User's Workgroup
Mar 13, 2007Is there any function or code that would display the workgroup in which the current user belongs simular to the function "currentuser()" which displays the user logged onto the db?
View 9 Replies View RelatedQueries :: Identifying Whole Numbers Vs Integers
May 2, 2013I currently have a calculated field that will result in either a whole number or a decimal. From there, I need to set up a field that identifies if the previous field is either a whole number or a decimal.I have tried several different formulas, but always seem to get an error or a query that does not return any results.
View 3 Replies View RelatedForms :: Identifying Which Control Is Being Pointed At?
Jan 5, 2015I'm familiar with screen.activecontrol but what I am trying to do is to activate an event relevant to the control currently triggering the mousemove event.
In the attached db, if you open the MMHighlight form and move the mouse over the boxes, as they are moved over they go yellow (mousemove event), whilst still hovering, click on a box and the first box displays the first and last boxes selected (mousedown event). release the click to trigger the mouse up event which clears the boxes. Broadly this is working as intended
The problem is the mousemove event which has to be 'personalised' for each control - highlighted in yellow
In the real application there are several hundred controls, so I'm looking for a timesaver - one option being to build a form via vba, but ideally some equivalent to screen.activecontrol and perhaps a third where I can replace [text0] with a generic name - a bit like 'Me' to refer to the form.
Table Relationships
Oct 14, 2005I am trying to put together a sales/purchase/stock control/accounting program using Access. I have put together a number of tables that I feel will be required and now I'm trying to set the relationships between them. The primary end document to be printed will be a Sales Order and a Purchase Order, likely generated from the reports module, so I have a table called Purchase Order and another called Sales Orders, related to Purchase Orders will be a table called Suppliers and another called Customers will be related to Sales Orders. Another table is called products.
For the Purchase Orders table, do I only add fields that are the primary key from the Suppliers table in order to list the full customer name and address stored only in Suppliers table. Would the supplier table be the parent and the Purchase Order the child ?
Table Relationships
Feb 15, 2006I have setup a number of tables for a booking system. The tables are as follow:
tblCustomers
tblBookings
tblRegularCustomers
tblRegularBookings
When a booking is made for just any time, it is in the tblBookings table. Regular bookings for example, every week, are put in the tblRegularBookings. In each of these tables, a booking cannot be made for the same date and time period. I am wanting to link the tables, so that when a normal booking is made in the one table, it cannot be the same date and time period as a regular booking as well.
The fields in each are as follow:
tblBookings:
Booking ID
Customer ID (linked to tblCustomers)
Date for Booking *
Time Period *
Weddings/Birthdays (yes/no)
Extension (yes/no)
Cost
tblRegularBookings
Regular Booking ID
RegCust ID
Date For *
Time Period *
Extension
Cost
What should i link to allow me to not create a normal booking on the same date as a regular?
Thanks
Need Help With Relationships In Table
Nov 25, 2004Here are my relationships for my 5 tables I created. My database is something that a turn key automotive performance shop would use. Tell me if my relationships are correct and what I need to do. I think I got a good start but I am not sure on a few things. Also what do I need to do for my validation and look up tables. Any help on that would be great. Thanks.
http://www.mustangmods.com/data/1030/relationships.jpg
Table Relationships
Feb 9, 2005This is a simple video library database, 1 member can have many loans, 1 video can have many loans and 1 loan can have many videos, so with that in mind woud anyone be able to make my relationships right? because at the moment you cant take out more than one video on each loan?
thanks in advance
http://www.the22nd.com/forum/uploads/post-12-1107976078.jpg
3NF & Table Relationships...
Apr 1, 2005I have restructed my original tables into 3NF and added relationships. But I think I am missing a few more items to go on to next step. Help me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel.
I have the following fields on this form as follows:
1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date.
2. Employee Number: combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number.
3. Employee Last Name: automatically populates when Employee Number is selected.
4. Employee First Name: automatically populates when Employee Number is selected.
5. Acct Id: combo box containing a list of labor description that has associated fields as follows.
6. Description: Text Box will populate after selecting Acct Id.
7. Cost Center: Text Box will populate after selecting Acct Id.
8. Acct: Text Box will populate after selecting Acct Id.
9. Category: Text Box will populate after selecting Acct Id.
10 Pay Type Id: combo box that will populate Pay Type.
10. Pay Type: automatically populates when Pay Type Id is selected.
11. Allocation: Free form, to type comments.
12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
13. Total Wk Hrs: total hours for the week for per line of Acct Id.
Here are my tables:
1. tblEmployees:
1) pkeyEmployeeId = AutoNumber
2) strLastName = Text
3) intStaffNumber = Number
4) fkeyEmployeeTypeId = Number
2. tblEmployeeType:
1) pkeyEmployeeTypeId = AutoNumber
2) strEmployeeClass = Text
3) strEmployeeType = Text
3. tblAccounts:
1) pkeyAcctId = AutoNumber
2) intCostCenter = Number
3) intAcctNo = Number
4) intCategory = Text
5) strAcctName = Text
6) strAcctDescription = Text
3. tblPayType:
1) pkeyPayTypeId = AutoNumber
2) strPayType = Text
3) strDescription = Text
4. tblPayroll Schedule:
1) pkeyPayrollScheduleId = AutoNumber
2) intPayPeriodId = Number
3) dtmPayStartDate = Date/Time
4) dtmPayEndDate = Date/Time
5) dtmCheckDate = Date/Time
5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayPeriodId = Number
6. tblTimecardHours:
1) pkeyTimecardDetailId = AutoNumber
2) fkeyTimecardId = Number
3) fkeyAcctId = Number
4) fkeyPayTypeId = Number
5) strAllocation = Text
6) intSat = Number
7) intSun = Number
8) intMon = Number
9) intTue = Number
10) intWed = Number
11) intThu = Number
12) intFri = Number
Here is the layout of my form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name
6. Acct Id
7. Description
8. Cost Center
9. Acct
10. Category
11. Pay Type Id
12. Pay Type
13. Allocation
14. SAT
15. SUN
16. MON
17. TUE
18. WED
19. THU
20. FRI
18. Wk Hrs
Question 1: Please review table relationships to see if I overlooked any tables that can be broken down or named its column differently?
Question 2: I have restructured these tables into 3NF, please see my previous posts under AccessRookie (although before 3/2005, someone used this ID).
Question 3: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending).
Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not populating? I think it is my table relationship: tblTimecard & tblTimecardHours.
Question 5: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI).
No need to store total since it is only need to display in data entry form (subform) and printing report.
I need your assistance since sometimes, it just takes another pair of eyes to review another peers' work. It's been ages since I've done any development from scratch. Help!!! Is there any way, I can attach my zipped database? It is 217KB zipped but this site only allow 100KB attachments.
Sincerely,
AccessRookie =)
Table Relationships
Oct 5, 2005I Have These Tables:
Case, Agents, Forwarders, Orders.
1 Case Has Each Time 1 Agent, Many Forwarders And Each Forwarder Many Orders
1 Agent Many Forwarders
Can Someone Suggest The Best Way To Link Them?
Table Relationships
Jul 11, 2006Can anyone look at this pdf and let me know if my relations are correct, i know i haven't marked on if its one to many etc but i just need to know if it'll work?
View 3 Replies View RelatedHelp With Table Relationships
Jul 15, 2006Ok im extremely new to this whole Access thing so I have several questions. First, let me give you an idea of what I am attempting: I am a member of a committee and we are involved in multiple projects. Each person is either inactive (working on no projects) or active (working on 1 or more projects). I want to set up a table that lists the member names, contact info, and which project(s) they are currently working on. I set up the basic table (not sure if its set up correctly or not though) but since some people are on more than one project, how do i make it so i can enter multiple values for the "Current Project" field? Also, I would like to make another table that lists Project Names, members (the members that are working on each specific project), and project description. How do I set it up so that under members (on the project table), it will gather the info from Member table (the one with contact info and such) and automatically list which members are working on each project? Also, There is one Leader per project and the rest working on it are just normal members. Can anyone suggest a good way to display who is leader on each project?
if this is confusing, just ask and i'll try to clarify for you
thanks, TRC
Table Relationships
Sep 22, 2006Hello all,
I have tables related by key fields. Is there a way to enter data into one table and have the key fields of the related table automatically enter into the key field of the related table?
Also, can you make custom drop down menus in a field? For instance, I have field that I would like to have a text in, there are only three values that are needed but spelling is important so I would just like to pull a list down to enter the data.
Thanks for any help!
Table Relationships
Mar 6, 2008im using access for the first time in years as iv not used it since college and im having problems with table relationships. i have 3 tables "cusomer details", "product details" and "purchases".
i have created a relationship between customer_id (primary key) in customer details and customer_id in purchases and product_id (primary key) in product details to product_id in purchases.
the idea is every time a customer makes a purchase i enter their customer_id into purchases and it will automaticaly bring up their name and address and enter the product_id and it will automaticaly bring up the product name in that table (purchases)
this does not appear to happen???????
i know its been a few years since i last used access but im sure iv done it right, what am i doing wrong?
TABLES BELOW:
Customer Details
customer_id
surname
forname
address
telephone
email
reg_date
Product details
product_id
product_name
product_category
price
Purchases
purchase_id
customer_id
surname
forname
address
product_id
product_name
quantity
Table Relationships
Aug 9, 2006I am new at Access. I am doing a database tracking the test results of eight different grades of students. The primary key is the name. When I try to add the 3rd test score table, I lose all information of all of the tables. I have tried changing the relationships, and cannot make this work. Any suggestions, please help.
View 1 Replies View RelatedTable Relationships...
Mar 29, 2007I have 2 tables. One is JOB TRACKING and has all the information about a certain job and all the trades involved. The other is a table of email address for the managers of each of the trades. I have them connected (with relationships) so the trades match up.
There is a form that I fill out each time a new job comes in and I check off the trades involved on that form. Is there a way for that form to pick up the email addresses of the managers of the trades checked off? So then I can create a button some how to send an email to them containing that particular form...
thanks!!
General :: Identifying In And Out Work Times Records
Feb 1, 2015I have a table (Imported from Excel) with In and Out records.
Here are the problems I have:Each record has either In or Out data. I have an identifier to know which ones are In and which ones are Out
It might have some misses, either no In or no Out record for the specific work time. A worker can get in at the evening and get out at the morning of the next day. Might have more then one entrance/exit on the same date.
I want to have the record sorted so I'll have an In and Out on the same row (record) including holes for the missing ones.
Queries :: Identifying Feeder Query That Has Zero Results
Oct 2, 2014I have a query (A) as the data source for a report. That query (A) has a another query (B) as one of it source files.
How can I tell if query (B) has no output, so I can stop query (A) and the report attempting to run.