Data From Multiple Tables In One Subform?
Oct 5, 2006
Hi!
First of all, I'm kind of a newbie to all of this, but not entirely, bare this in mind please ;-) Also I'm from the Netherlands and my english might be a bit rusty, so bear with me if you please.... :-)
I'm having a problem for which I cannot seem to find a solution. Below I've outlined the situation:
I have a couple of tables up to now:
CustomerTable
CaseDateTimeTable
CaseTechInfoTable
CustomerTable has got a primary key: Clientnumber
CaseDateTimeTable has primary key: Casenumber
CaseTechInfoTable has no primary keys.
CustomerTable only includes (apart from all adress info etc) the clientnumber field which is of importance in this case i think.
CaseDateTimeTable includes both Casenumber and clientnumber.
CaseTechInfoTable also includes both Casenumber and clientnumber. Only both other tables have Primary keys, and this one doesn't.
I've created a relation between CustomerTable - Clientnumber and CaseDateTimeTable - Clientnumber.
Another relation I've made is between CaseDateTimeTable - casenumber and CaseTechInfo - casenumber.
Now, I have made a form in which the name and adress data is fed into the database, in normal form view. Below these textboxes etc. I've created a SubForm in datasheet view in which the existing cases from the at that time selected/displayed customer should be displayed. At the moment only fields from the CaseDateTimeTable can be displayed, but I would also like to display some fields from, for example, CaseTechInfoTable in that same subform.
But how?
When I create a (sub)form via the wizard and for example select:
CaseDateTimeTable - clientnumber
CaseDateTimeTable - Casenumber
CaseTechInfoTable - Problem description
and display these fields in the subform as a datasheet, then the separate cases from each customer are not displayed. I have 5 clients in my test dbase, just about all of them have 2 or 3 cases associated with them. In the example above only the data from two clients is displayed and then only from 1 case each, not the actual amount of cases. So for example:
clientnumber 4 has casenumber 5 and casenumber 6. But only casenumber 5 is displayed. The same happens for clientnumber 2, he has casenumbers 9 and 3 but only casenumber 9 is displayed.
The strange thing is though, that when I create the same (sub)form in datasheet view, but only use fields from the CaseDateTimeTable all cases per client are nicely displayed in the subform.
Why? And how do I solve this problem? I need to solve it so that I can add data from other tables into the subform later on so a nice overview of certain important data can be given per customer per case, even before the case form(s) is/are opened.
I've tried making a query and base a form on that, but for some reason I can't get the query to display all my customers, let alone all cases associated per customer. In the past I have made another (smaller and simpler) database for which I also made a lot of queries. But now? Pff... I'm lost at the moment.
Anyone?
View Replies
ADVERTISEMENT
Aug 25, 2012
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
Any other ways to import the data?
View 1 Replies
View Related
Sep 6, 2005
Hi guys,
I've run into a little problem while working on a electronics component database:
My plan was to use one specific table for each kind of components, i.e. resistors, capacitors, transistors etc as the specific data of each type is quite different from the other types (resistance/capacitance etc). I also thought I'd use a main form with a drop box allowing the user to choose which component to look for and then a component type specific subform to let the user make a more detailed search.
The thing is that I'm not quite sure how to call different subforms based on the drop box selection (I guess I need a subform for each type of component)? Or can I call a table with each component's specific data types and generate a subform based on this table? (I'd prefer the latter...)
To find a specific resistor you might want to specify the resistance, the type of resistive material, size and tolerance whereas an integrated circuit requires information on type of circuit, package, size etc.
All similar questions I've found relates to the use of a single, main, table only, I can't really see how to do that here...
View 1 Replies
View Related
Mar 9, 2008
Hi there (New to access design)
I have a subform (studentmodules) which should pull data from two tables (hea) and (hea courses).
Hea table contains the course code, title, level of study and award, Hea Courses contains the start dates, location, cost and tutor etc for each intake of the course.
When I enter data into the Student modules form I can type in the HEA number, and then have to choose the course title from a very long drop down list (which doesn't seem to work for some reason as values do not stick) and then enter the award and the level of study. I am able to enter the information relating to the course intake, and this automatically updates the information in fields relating to start date, coordinator etc.
How do I get Access to do the same for the HEA information.
Answers would be appreciated.
View 6 Replies
View Related
Jul 22, 2005
Hi All,
I have designed a form that holds data from a table called Purchase, it also has got 3 more fields that are coming from another table called Master. These 3 fields, Code No, PartNo and Description are same in both tables. All i need is a way to bring the data of the stock table on purchase form. As soon as i enter code number in code field, it should check in stock table if the same code exist, if yes then it should bring the all 3 fields in purchase form and display.
Hope someone out there will return my call.
Warm Regards,
Darno
View 1 Replies
View Related
Apr 26, 2006
I have a form, created from a query. THe query contains an links about six tables using an AutoNumber ID.
OK, so when users enter their data into this form, I would like the autoincremented number to increase in each of these tables. The catch is that when the user enters data he/she does not have to enter data into every field.
So, some of my tables do not auto-increment and some do. How should I go about resolving this so that when someone enters new data, each table's ID is autoincremented no matter if that table contains information immediately or not? Please Help.:confused:
View 1 Replies
View Related
Nov 8, 2004
I haven't used access for a long time so I am very rusty.
I have a few tables but I'll just use two for an example:
Table A
Ticket No
Part No
Defect Code
WorkCenter No
Clock No
(Ticket No is Primary Key)
Table B
Clock No
Supervisor
Employee
(Clock No is Primary Key)
Is there a way for me to create a form, that when Clock No is entered it will be placed in both tables?
Thanks in advance.
View 2 Replies
View Related
Oct 24, 2005
hi, i am new to access and struggling so any help would be great...
i have a db with 3 tables. students contains a Primary Key ('studentid') and then 'firstname' and 'lastname' fields. The two other tables (contactdetails, coursedetails) have 'studentid' as a Primary Key and then they each have other fields.
I set up relationships between the tables (1 to 1) but i would now like to create a form that will allow me to enter data to all three tables at the same time. (this would be ideal as it keeps the data normalised) if this isn't possible i would like to create a form that adds the 'studentid' record to each of the tables.
let me know if you need anymore details but if someone can point me in the right direction it would be cool.
cheers
View 11 Replies
View Related
Mar 17, 2007
I am a new user to access and are therefore seeking the assistance of a more experienced user (I'm sure this will be an easy one for all out there).
I want to be able to get records from different tables after the table name has been chosen.
In other words and to be a bit more specific, at the moment in one table I am selecting the tables I want to get information from. Once I select the tables I need to then be able to extract the required data from the selected tables and then put it into a new table with summary totals.
View 8 Replies
View Related
Aug 15, 2006
K,
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,
View 6 Replies
View Related
Jan 1, 2013
i have a table that holds "course codes" with the corresponding Module title and module codes, and assessment titles and codes that are used in each of the courses, so I have a few records that will go against one course code. I have forms set up with subforms, with the same general details on the top of each as the main details, and each of the subforms giving different information. But, for four of my subforms, I need to pull information from this table with the module title, module codes, assessment title and codes. possible to have these certain columns appear across four subforms, and what is the best way to go about it, I was thinking of using lookups?
View 1 Replies
View Related
Aug 25, 2005
Been trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
View 2 Replies
View Related
Aug 18, 2006
I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.
I have an "Activities" table that contains fields:
ActivityID
ActivityName
I have a table of 7 Risk areas which contains fields:
RiskID
RiskName
What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.
This involves the creation of a 3rd table to collect the user input which would contain fields:
MeasurementID
RiskMeasurement
ActivityID
RiskID
I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.
I hope this was clear. Any help is appreciated.
View 14 Replies
View Related
Oct 14, 2004
I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)
Paint (txtHrs), Action (cboPaintAction), Condition (cboPaintCondition)
Carpet (txtCarpetHrs), Action (cboCarpetAction), Condition (cboCarpetCondition)
Counter (txtCounterHrs), Action (cboCounterAction), Condition (cboCounterCondition)
etc.
Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!
View 2 Replies
View Related
Nov 2, 2004
Hi all, I'm not an Access programmer by trade, but unfortunately got handed a project that nobody else wanted.
I have a small database taking shape which will track the auditing of paperwork/files. Each file has a unique ID associated with it, in this case, an account number. Audits will take place by selecting the job area (department), then the type of file (Line, Loan, Increase). This will be used to sort which type of audit questions are asked.
I have the following tables created:
tblQuestions: ID, Question, Product question applies to, Function question applies to, Comment field, and result field.
tblTeamMember: MemberID, TeamMember, Manager, PrimaryFunction
tblAuditResult: AuditID, MemberID, AccountNumber, Question, Result, Comment, Date of Audit
tblProduct: List of product types (line, loan, increase)
tblFunction: list of job functions
Currently a form is displayed so the manager can select the job function, then the file type. This will display:
frmAuditCreate - this form contains the fields: txtAccount, cmbManager, cmbTeamMember, txtAuditDate
Sub form on frmAuditCreate - frmQuestionSub - this form contains the fields: Question, Comment, Result
The frmQuestionSub is built off a query which uses the function and product values to determine which questions to filter and display.
Now, once the user is done flipping through each question in the sub form and input the data into the comment and result field associated with that question, I need that information along with the information on the frmAuditCreate to be stored in the table tblAuditResult. This is the problem I'm having difficulty with. I can't find a way to copy the question, comment, and result and insert it into the same record as the rest of the data.
Unfortunatlly I can't strip the database down enough to meet the 100k or less attachment limit.
View 8 Replies
View Related
Nov 4, 2004
Hi, I have a big problem. Here is the business rule, I am trying to track meeging request(MR) made. A meeting request is made by a manager,MR can include many analysts, and MR involves 1 project. I need to generate several reports from the MR information so I am trying to make sure the info is inputed correctly. I am trying to creat an input form for the MR table (tblMR) The input form is going to contain all the fields from the tblMR except the manager_ID and and the Project_ID, I am also going to have it track which analysts attended the meeting(1 or more). The problem I am having is in creating the form but having the analyst, manager, project values come from their own tables but after selecting them through combo box or list box, for values of Manager_ID and Project_ID to be entered in the tblMR. And for the analyst involved to be stored in a a composit table called tblMR-Analyst. this table will help me track how many meeting each analyst has been to. Any suggestions will help.
View 2 Replies
View Related
Jun 24, 2013
I have a database with employees. The tables are as follows:
Deptdatatble
Depttble
Emptble
HRtble
Servicetble
Servicedatatble
Archivetble
Classestble
Classdatatble
At certain times, I want to archive employees out (lets say they are terminated). When I do this, something strange happens. If an employee has 4 records in the servicedata table and 4 records in the Classdata table, then it exports out 16 records (4 x 4). I would expect it to export out 8 records.
View 6 Replies
View Related
Aug 29, 2014
I am trying to merge data from multiple rows into one row for each unique ID. I can replicate on how to do this on a table, maybe a query or something like that. Attached img shows
View 3 Replies
View Related
Nov 5, 2013
We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.
Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.
View 9 Replies
View Related
Mar 26, 2014
I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.
In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.
Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?
View 3 Replies
View Related
Aug 5, 2013
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
Process1_triggers
Process1_targets
Process2_triggers
...
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
View 3 Replies
View Related
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
Mar 2, 2015
I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.
I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?
View 3 Replies
View Related
Jun 20, 2015
I have these tables set up.
1) tblTeams.
2) tblDefensiveStats.
3) tblOffensiveStats.
Tables 2&3 hold data for the past 3 seasons for the 32 teams in the league.
I don't know how to get access to perform these functions.
View 3 Replies
View Related
May 30, 2006
First off, its been a long time since I have been here (since sometime in 2003 LOL), partly because I didn't have to create a new db in a while. Now, I have been handed a project and come across something new.
OK, here is what I want to do. I would like to be able to dump data from an Excel file into existing Access tables (two to be more specific). Now, I also have relating tables that add data into certain fields. I tried using a query to try to group data ad hopeful able to dump data through the query. I noticed that I couldn't import data through a query. Just for kicks, I tried to pull data through table and noticed that I can only create a new table, not able to update and existing table.
How would I go about performing this task? I have never attempted anything in the past.
Thank you in advance for your considered help,
Steve
View 3 Replies
View Related
Dec 26, 2012
I need to pull data from multiple tables in order to show a "financial summary"..Currently I have: Company; BalanceSheet; Debt; Liabilities; Income..All tables have a lot of information (which is why I built them in multiple tables).
I need to build a form where I can use a combo box to select a company from a list.Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math functions).I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what the wizards will accomplish.
I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)
This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save information for future use.I believe all of my fields are constructed correctly. how to compile the data from multiple tables into one form/report.
View 5 Replies
View Related
