Tables :: Accessing Data From Other Fields In Another Table
Sep 19, 2014
I have a database with a number of tables 2 of which are:
# Personal Members Table (with all their details and addresses and contact details)
# Organisation Table (including who are members of this Org)
My conundrum is this; In the Org table I have just created a 'Treasurer' Field and want to choose a name from the Members Table so that I do not have to put the data in twice (which is what has happened in the past.)
I have linked the tables and but within the Members table the Field names for the Personal Name are as follows <FirstName> <LastName> and there is another for the <Org> they are connected with...
In the Members table it would also be good to have the ability to hover the mouse over the <org> field (or click) on it and then underneath a box appears with everyone who is a member of that org. I have seen this working on another database but cannot seem to replicate it here for some reason?
How do I use fields in related tables to create a calc field? The wizard only shows me the current table's fields. Can I do create me desired field by manually creating the calculation and bypassing the wizard? Or is this another restriction of Access?
I work in an office with Office2000. One branch of our non-profit organisation had a volunteer design a database in Access to track the usage of rented storage bins.The database works great for its intended purpose, however the volunteer is not available and the front-end is so locked down that I can't get into the table structure to run some usage queries I need for my own job, which is Resource Development (donations).
I don't know what to say to give more info, because the computer is a stand-alone in a different department detached from the network, so I have to physically go to the other end of the building & work in it, I can't get screencaps etc easily, and most of the menus have been disabled so it doesn't even give me the info about the version of Access etc.
I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.
I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.
This database is for quality control checking purposes.
First off,
Database 1: Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.
Database 2: This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.
What basically ends up happening is that the data in Database1 become Table fields in database2.
Example:
Database1: Radio Check Audit Standard. Needs to check following: Process Specs EDMS Drawings Manuals SoP / Policy Used Radio callback Used radio during emergency Etc.
Another Random Check Audit Standard Needs to check the following Process Specs EDMS Drawings Manuals SoP / Policy Did something specific for this Audit Didn't do something else, also specific for this Audit. Etc.
Continue on with other Audits.
Database2: Will keep track of all the quality checks that have been preformed.
Today we will check Radio Protocol Did they: Process Specs EDMS Drawings Manuals SoP / Policy Used Radio callback Used radio during emergency Etc.
Yes, No as necessary.
2 Days later, we need to do another QC on the Radio Protocol Need to check if they did: Process Specs EDMS Drawings Manuals SoP / Policy Used Radio callback Used radio during emergency Etc.
Etc.
But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.
Right now, i see 2 options:
Option1 Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc. The fields in Database1 are automatically put into this specific instance of Quality Check, etc.
Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.
Option2: When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.
Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.
I'm seen the first option as the only viable one.
Am I missing this, is there some other way to do this that would be better / wiser? Etc?
...TableA: 1 indexed field "Name" and many data fields in ~ 1000 records ...TableB: 1 indexed field "Name" and 2 data fields (DataB1 and DataB2) in about ~ 50 records
Tables currently have NO relationship set.
Want to add TableB data fields to TableA if Name are identical.
DataB1 and DataB2 fields are Not currently in TableA but could be added very easily.
Update (or whatever) will be done weekly where all previous data for DataB1 and DataB2 will be cleared from TableA before job is run.
I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.
So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.
E.g:
Say I have 2 records in my access:
ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3 1 Fab1 193 NY Bug OC DC MC 2 Fab2 641 DRS Error AC KC FC
Now when I am importing data from Excel with the following records:
So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.
And I need this to be written as a macro, so that user just has to choose the file, which he has to import.
I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
Basically in my order details table i have the following fields
Product Unit Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT Grasshopper Box1000 Adult Grasshopper Box1000 Subadult Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I currently have a subform which relates to the primary key of the main form.
This subform contains possible 'Answers' which related back the main form/table 'Question'. Each possible answer has a true of false field to show whether or not it is the correct possible answer from the question field.
Currently the subform performs correctly, in that it shows up the 3 possible answers for each question... (in continuous form view)ie all three appear at once in 'form view' - but in the subform the only one answer and one true or false attribute appear at once.
Here is where the problem is - i need to be able to access the individual field values for these 3possible answers (in VB code). I cant work out how to do this? Does anyone know how access calls them? What will the checkboxname be? The problem is because there is only one checkbox on the form in 'Design View' (to see if the answer is the correct one), but then 3 in 'Form View', I cant work out how access distinguishes between them.
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I want to run a query that access a table behind a form. I would like the query to accept data that the user has typed into a text box within the form. What code do I use for the query to access this text in the text box? I want the form to then display the results of the query in a List Box. Also what code will I need to change data in a table by using a form?
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
i have a slight problem. In my tool i'm creating, i have a workproduct form and a button that takes me to another form that allows me to enter in defects to that work product (which works). The thing is, if the defect is found in a 'coded' work product, some fields will appear for new data, and other fields will disappear. On the work product form, there will be a field that displays if the work product is code or not (taken from a table) and the fields that will appear (and disappear) are on the defect form. I want the tool to look on that work product form to see if the 'phase' field is 'code', if it is, then show/hide fields on the defect form. Heres my way of interpreting what i want in code form. I hope you can gather whats going on from this:
Private Sub Form_Open(Cancel As Integer) If !Form.preMeetingForm2.phaseName = "Code" Then lineNumber.Visible = True lineNumber_Label.Visible True function2.Visible = True function_Label.Visible = True page.Visible = False page_Label.Visible = False section2.Visible = False section_Label.Visible = False End If
Note: this Form_Open event is on the defect form (where the fields will appear and disappear)
I Created a database that allows the user to create a jobsheet (Sign off Sheet) with inputting minimal information.I am having some problems accessing the data from my form to a report. I managed to get the full name of my AssignedTo and OpenedBy come up with this
But this is only bringing up the information in my tblContacts and not actually assigning itself to the jobsheet. I have tried to just put in the "assigned to" but then this comes up as the ID number rather then the full name. And I have also tried to do this via the join table tblJobSheetContact but this only brings up Mr.
I need to match the data in all the fields between 2 tables.
eg. Field 1 (Table 1) = Field 2 (Table 2) Field 2 (Table 1) = Field 2 (Table 2) so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
I have a field that has a description of the project I'm working on, which has client information on it. I want to take all 600 of those records and copy them to fields in the same table, so that I can remove the client information, which will allow me to pull whichever of those two I need when I run a query.
Now i want to make a thrid table that contains a field for all purchases in table 1 & 2, (P1,P2,P3,L1,L2,L3,L4,.....), if i make a lookup wizard that will retrieve only from one table but i want to retrieve data from the two tables.
need to create a database for work. there would be a way to select an id based on a previous table and have parts of it populate in this new table.
For example:
Table A - Webinars (Webinar ID, Webinar Title and Client) Table B - Sessions (Session ID, Date, Webinar Title, Time, Facilitator, Session Occurred, Reason, Conference Call) [Webinar Title I used a look up field and just select the corresponding Webinar Title and it works nicely] Table C - Users (User ID, Status, Prefix, Last Name, First Name, Email Address, Component, Role and State) Table D - Registrants (User ID, Session Date, Webinar Title, Attended).
So in Table D I would like to add Role and Component, so when I select User ID #1 the Role and Component Field populate with the info based on Table C.
right all tables are like a 2d array so does any body know how i can access a field in a row of a table ie like in most languages it goes a bit like this ArrayName(1,1) with ArrayName being the name of the table and that access the first field of the first row. im new to Vba and access 2000 but all i want to do is access the fields in a column of a sub-form displayed as a table add them all up and place the answer in a text box on the main form stick all this in a function so i can run it inside a macro
I'm making a test database for DVD rentals. When A loan is made I have an update query that sets some fields in the DVD copy table. One of these fields needs to be set to the primary key of the loan made. This would be the last entry on the loans table. I don't have any idea what to put in the 'Update To' box in the query though.
Wrong code here, but this may help explain what I am trying to get: Tables.Loans.LastLoan.Loan ID
Also heres an image of my query: http://i92.photobucket.com/albums/l15/mikegrundel/Accessproblem.png
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.
I can not, or not allowed to enter data in fields in a query built from three tables that are jointed by a common field with the same name. The parent table is linked to another Access data base as is one child table the other is local data. Also, I am not able to view the child table data when viewing the parent table.
