Multiple Tables Into A Table Or Form

Feb 7, 2005

I am currently designing a database to solve our organisation's training requirements. The db needs to be able to identify an individual member of staff at any particular site and list all the medical devices that they have training on and to what level of competency. So far I have five separate tables:

Staff details
Site
Devices
Competency/Risk
Equipment

By using unique identifiers within each table, we can, in theory, comply with the requirements.

Now here is the problem. Having talked it through with a colleague, we have two possible options.

A. Using a form based on the staff member, combine the salient details and, where needed, create additional fields (such as dates and tick boxes) to record odd items (this is my preference)

or

B. Based on the site, create a make-table query and form a table, sorted by site and then staff, listing all the equipment and competency levels. Given that some staff will work at multiple sites, the table will probably contain over 6500 records with lots of duplication (you can see my bias!)

Which solution is probably the best or should we be looking at solution that combines elements of both?

Thanks in advance

David

View Replies


ADVERTISEMENT

Tables :: Multiple Table Data To Save To A Different Table From A Form

Mar 6, 2014

I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?

How to get all the data on the reallocated form to save?

View 6 Replies View Related

Multiple Fields Of Multiple Tables To One Table Query Or Report

Apr 12, 2013

I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?

View 1 Replies View Related

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

May 28, 2015

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

View 8 Replies View Related

Table From Multiple Tables

Jul 7, 2006

Well I'm hoping someone here can point me in the right direction. This is my problem... We've got about 50 Access databases scattered across the network. Each network database contains the same tables and fields, but of course, each one contains different information (one network database is for DepartmentA, another network database is for DepartmentB, another network database is for DepartmentC, and so on).

What my boss wants is a single table/view that displays all the information contained in every network database table. The other part of this is that my boss then wants to be able to make changes in this single, all encompassing table/view and have those changes be pushed back out to the individual network databases. Is this possible? And if so, can someone help point me in the right direction? I'm still a little new at Access, but I can manipulate VBA code and all that if need be (I'm learning more and more as I get deeper and deeper into this project).

Thanks!
Lucid

View 2 Replies View Related

Tables :: Multiple Table Validations

Jul 18, 2014

I have to assign multiple table validations to my table but they are not even working separately.

1) I have two combo box controls: [Tocolytics] & [multiple_Tocolytics], 3 check boxes and 1 text box.

Rule:
IIf(([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes"),([Indomethacin]=True Or [Nifedipine]=True Or [Nitroglycerin]=True Or Len([Other])>0),"check one of the boxes")

2)Two combo boxes The answers are mutually exclusive. If one is yes then the other must be "No" or "unable to determine"

Rule:
IIf(Oxytocin Infusion - no PPH]="Yes",([Oxytocin Infusion - if PPH]="No" Or [Oxytocin Infusion - if PPH]="Unable to determine"),[Oxytocin Infusion - if PPH]="Yes")

View 14 Replies View Related

Tables :: Multiple Variables Table

Nov 26, 2012

I have a store database containing multiple values about a single product, and separate table on color that this product comes in and in another table are quantities this product is available in. How can I make relationships between the tables so it works and I can make queries from it?

View 2 Replies View Related

Tables :: Table With Multiple 1 To Many Relations

Apr 7, 2015

I have a table named [Brand] and several tables named [Brand@@@Models], one for each field in the Brand table.I want each field from the [Brand] table to be related to it's specific Model table. I thought like that so I don't have a Brand#1 related to Brand#2 models, etc.. I didn't want to have all the models for all the brands in a single table, was I right to think like this?

View 6 Replies View Related

How To Use Sections Of One Table In Multiple Tables

Dec 1, 2013

I have quite a limited knowledge of MS Access (2007).

My question is; How do you use sections of one table in other tables?

For example: In tbl_One I input a users Name and Age and then say Weight, Height, BMI, etc.

Then in tbl_Two it uses the same Name and Age in tbl_1 but has other information such as Job, Skills etc.

This is not my actual database, however this analogy works, and they must be in separate tables, even though above they could all just be in a single table.

View 1 Replies View Related

Multiple Tables From One Main Table

Apr 22, 2013

I have a big number of sales staff..and broken them into different teams.

I have one TABLE1 with all their names, i need to have table which links to TABLE 1

All the fields are the same except team field. Therefore i need table 2 to only show me all fields and TEAM ALPHA only.

In total i got 46 teams therefore it is big.

View 1 Replies View Related

Do Tables Have To Be Linked To Do A Multiple Table Query?

Sep 13, 2006

Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.

View 1 Replies View Related

Combining Rows From Multiple Tables Into A New Table

Nov 21, 2006

I have three tables, with the following data (fields separated by "-" here):
Unit - Customer Number - Customer Name - Type of A/R - Total A/R
Unit - Customer Number - Customer Name - Type of A/R - Total Overdue
Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance

I would like to combine these tables into one table with the following field names:
Unit - Customer Number - Customer Name - Type of A/R - Balance

When I append the data, it comes out like this:
Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance

I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!

View 1 Replies View Related

Tables :: Need Multiple Calculated Fields In One Table

Aug 8, 2014

I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:

1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill

Here are my excel formulas that for the life of me I can't get to work.

Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))

Can I have calculated fields that refer back to a calculated field?

View 10 Replies View Related

Tables :: Adding Multiple IDs In A Table With Time

Mar 11, 2014

I want to make a table where in I have to input atleast 10 numbers in one reference ID which there is a start time and end time.

A form with inputing one reference ID and then adding multiple numbers at once and saving the data with a start time and end time. Is it possible to capture the time for that specific reference ID?

How to do it or a sample tracker on measuring it.

View 14 Replies View Related

Tables :: Multiple Name IDs Link To Same Names Table

Jul 30, 2013

I have a risk table containing risks and risk owners (many owners for one risk), meaning that I have two risk owners columns. What I would like to do is to connect both risk owner columns (containing the name IDs) to the Names table. But Access does not allow to connect more than 1 column to another.

See the attached file...

View 6 Replies View Related

Import Data From Multiple MDB Tables Into One MDB Table

Feb 20, 2013

How can I import from multiple tables into one table without replacing the destination table.so that the destination table consist of old data and new imported data.

For example: I have table A with date 130205 and I need to import from table B and C which the dates are 130206 and 130207. Those 3 tables already have the same column numbers and data type.

View 9 Replies View Related

One Main Table With Multiple Tables Relationships?

Sep 14, 2014

I'm trying to create a database at work keep track of projects I'm working on and all the different events that happen during the project.

I want the main table to be customers, which will include the job number (as the primary key) name, address etc. Then I need other tables that have information about the building permit that will include dates city names etc and then another table that would include information on our sales people and so on, there would be maybe 5 tables that all connect back to the customer table.

I have tried this several times and keep running into problems, I'm sure it has something to do with the relationships. I'm missing something. I can create 2 tables and it works fine but once I add a 3rd it wont work.

View 4 Replies View Related

Tables :: Import From Multiple Documents To One Record In A Table

Oct 22, 2013

What I wanted to do was try and import from 5 excel sheets into one table and merge them into 1 record.

E.g. I have pupils with test results for maths, English and ICT.

I have 3 spreadsheets called Maths, English and ICT and I wanted to merge them into the test results table all under that specific pupil.

Also I've been searching how to create an import button on my form as I have locked all the ribbons but can't find a simple way, and especially if I want the user to specify the file path.

View 3 Replies View Related

Tables :: Query Off Of A Junction Table - Multiple Categories

Sep 26, 2013

So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:

ProjectID
100
Category
Admin
Finance

But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..

View 14 Replies View Related

Tables :: Make Table Returns Multiple Records

May 11, 2013

I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.

View 1 Replies View Related

Tables :: One Primary Key To Multiple Foreign Keys In The Same Table?

Aug 29, 2013

Right now, I have 4 related tables. There's a table with companies, one with people involved with companies, a table linking the two by having foreign keys of the company number and people names, and a table that indicates directors and their alternates.

Since there's a one-to-many relationship for companies/people to company-peopleID (A person can run multiple companies, a company has multiple directors, it's easier this way), a person's name can appear multiple times, as can a company, within that table.

In a company, a director may or may not have 1 and only 1 alternate director to him/herself. So, I thought the easiest way was to put an autonumber in the Company-personID table and have a table (alternates) that had two fields, "alternate" and "director", both using that autonumber to link them. However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.

[URL]

View 3 Replies View Related

Combine Multiple Tables (all Are With Unique ID Names) Into One Table?

Nov 22, 2013

I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.

View 3 Replies View Related

Multiple Tables On 1 Form

May 20, 2006

I have 6 tables that have 1 to many relationships.
I would like to have all 6 tables on 1 form for data entry.
When I use the “Form wizard” and select the tables it wants to group the last 3 tables in 1 subform and it won’t let me add records to the last 2 tables. Same thing happens when I create a query and run the form wizard selecting the query.

I know I’m doing something wrong but I can’t see it……….

Thanks
Paul

Here's an add on question:

When the form wizard adds a subform to a subform the link child and master are filled out and the form updates. But when I add another subform and set the links it will not update.
Example:
form Book has text fiels and 2 sub forms "Chapter" and "Section" subform section child link is ChapterID, master link is [Chapter Subform].Form![ChapterID] --- This updates as expected.

I create a new subform "para" based on a table and set the Child link as SectID, master link [SectionID].Form![Section Subform].Form![SectionID] --- Will not update the subform "para" when the next record in subform "section" is selected.

Any ideas would be great......

View 2 Replies View Related

Multiple Tables Into One Form

Aug 27, 2012

I have one form (Request Form) which has quite bit of information on it. So I decided that I would need the following tables:

tblRequests
tblCustomer
tblSalesperson
tblSupervisor

I think I have created the relationship fairly well. Most of them one-to-many.

I created the form but now I whenever person is making entry I want the information from one form to go into multiples tables. What's the best to go about this?

I have tried couple different ways, Made a query from the tables and then using that as Record Source for the form as well. It doesn't seem to work properly.

View 3 Replies View Related

Multiple Tables Into One Form?

Apr 6, 2015

I am trying to take two tables (one is customers and the other suppliers) and turn it into a form to pull up the data from the tables. I need to be able to enter new data. This is for a corrective action form so after pulling up the customer and supplier info, I need to input the problems and corrective action. This information will then be turned into a report that I can print out.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved