Multi Record/Query/Multi Table/Going Crazy Issues
Sep 7, 2007
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
View Replies
ADVERTISEMENT
Apr 10, 2008
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
View 7 Replies
View Related
Oct 1, 2007
I have a nine-table database about the people in a certain no-longer-existing East European village. So it revolves around a main table, called PersonLOG, of people who used to live there, each with a unique ID#.
There is another table that list facts about the people: PersonFACTS. This table has, among other fields, three fields for three types of sources. One is called SubmitterID; this is the code for a person who submitted a testimony page attesting to that persons' death in the Holocaust. There is also a text field that a particular "factoid" can be typed into. There may be many fact records about any individual. So there is a one-to-many rel. between the PersonLOG and PersonFACTS.
There is a 3rd table called MAIN, that lists the "vital" information about most of the individuals. It includes one yes/no field indicating whether they died in the Holocaust.
I am trying to create a query that would list only those Persons for whom there 1)is no Submitter and 2)Died in the Holocaust. This requires that all of the records in PersonFACTS pertaining to a given individual be compared, looking for the ABSENCE of a FACT record for that person that has a submitter attribution, and then comparing to "and"-ing that with the yes/no field described. I can't see a way to do that.
My apologies if this question is too complicated.
Thanks!:confused:
View 8 Replies
View Related
Sep 28, 2007
I have a query that includes 4 different tables. It only shows info for the Sales Orders that have information in all four tables.
Is there a way to get it to show any information available? For instance, if it only has info in two of those tables, it would show that info and the fields for the other two tables would be blank?
View 5 Replies
View Related
Apr 9, 2008
I'm not quite sure on how to go about this query.:confused: What I need to know is if a certain patient has had an immunization or not. I need one query to show the patients who have had an immunization and another to show the ones who have not had the immunization.
I have two tables.
Appointments
Date_of_Service
Patient_Name
Chart_ID
Immunizations
Patient_Name
Immunization_Date
Chart_ID
They are joined on Chart_ID.
Any help would be greatly appreciated! :)
View 7 Replies
View Related
Dec 9, 2005
Hi, I new to access and I just started using it for a group project in one of my classes. We are supposed to make a database for our "business" so we decided to pick a small independent movie theater.
After some trouble we finally figured out that we had to link the tables with some relation ships. Our basic tables are:
"Distributors" -relationship-distribributorID-"Ticket Sales"
"Employee Information" -relationship-TitleID- "Position Information"
"Inventory"
So I made a query that would get the revenue for each product sold in the inventory by multiplying the price and the amount sold.
I was wondering if it would be possible to create a query that would total all the revenue and all the ticket sales and divide them to create a per-person spending ratio.
View 2 Replies
View Related
Dec 7, 2011
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
View 3 Replies
View Related
Apr 13, 2006
I keep thinking this is so obvious I shouldn't have to ask, but....
I have a database, the back end sits on the network. Forms in the front end are bound to the data in the back end. This is certainly not optimal, but it has worked.
I'm guessing other uses of the network have increased, though, cause now, Access occasionally chokes when trying to open the forms.
So I need to unbind them.
Okay, I know how to do that. Trouble is, how do I deal with multiple users accessing the same record at the same time. With bound forms, Access may not handle it as elegantly as possible, but at least it does try to handle it.
Every example of unbound forms I have ever seen have grabbed data, used it to populate the form. Then if the record is edited, throw the changed data back to the back end. But what if someone jumped in between when the data was grabbed and when the changes were written back? How do I handle that?
To make matters worse, my users absolutely want to be able to continue to search and filter the forms. Plus, the form has detail data in continuous sub-froms.
I am thinking of accomodating that by up front having them select a subset of records, either all for a given customer, or hopefully, just a given invoice. I plan to copy that to a table on the front end, and bind the form to that copy.
When they change the beginning filter value, I would write the data back. But that could be hours later. Lots of time for the data to become stale.
Any suggestions on how to deal with these issues? Pointers to where is has been discussed before? (Hey, I did look, but all I found were vague, "you have to watch out for...." but never a way to handle it when it does happen.)
Thanks,
David
View 4 Replies
View Related
Aug 5, 2005
I have a multi-select list box for selecting which faculty members apply to a project. The faculty table and project table are linked in a many-to-many relationship. I have the following code which should create entries in the link table:
Private Sub Command5_Click()
Dim varItm As Variant
rs.Open "tblFacultyLink", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each varItm In lstFaculty.ItemsSelected
rs.AddNew
rs!FacultyID = Me.lstFaculty
rs!EntryID = Me.EntryID
rs.Update
Next varItm
rs.Close
Set rs = Nothing
End Sub
It successfully creates new records and enters the EntryID and LinkID (autonumber). However, FacultyID is always left blank. lstFaculty is the unbound list box which has three columns from the faculty table and is bound to FacultyID. Any ideas on why FacultyID isn't created in the link table (I get no error messages)?
Also, any ideas on how to prevent duplicate links being created every time the button is pushed? I was planning on having it first run a delete query for that EntryID in the link table so that it replaces the old links and any that have now been unselected are no longer linked. Comments on that idea?
Thanks again to everyone on the forums for your help.
View 4 Replies
View Related
Jul 31, 2014
I have a form that has a FIND button. I want the user to enter the 3 fields that make up the primary key, then find the associated record. How can I do this in Access - not VBA?
View 1 Replies
View Related
Mar 30, 2015
I have a Form named Clients with a multi-page tab named TabCtl0.
The record source tables - primary key is [ID] , TabCtl0 has 7 tabs, the 1st named General.
The 1st 6 tabs show information from the same record.
The 7th tab contains a subform named Focus displaying continuous forms with 3 fields in each record including [ID].
What I am trying (for hours and hours without success) to do is to create a Macro that fires when I double click on a control within subform Focus that takes me to the page named General and selects the record where ID = the ID within the selected record of subform focus.
View 1 Replies
View Related
Feb 1, 2013
So I have a multiselect listbox. The list that it shows depends on what is selected in a separate combo box.I want to be able to select multiple items from the listbox, and then be able to leave the record, and when I come back to it, to still have those items highlighted. Right now in my form, as I go through records, if I highlighted items 1,2 and 3 on a record, those first three lines will stay highlighted as I move through different records, rather than changing to what had been selected for each record.
I've already stored the actual selected values in a subtable. I just want to have it highlighted again so that you can see what was previously selected.
View 2 Replies
View Related
Mar 9, 2006
All,
Bottom line I'm in bit of a pickle, work has moved forward the migration of Office97/NT4 to Office2003/XP by 2 months (clever lot) and was hoping not have to cram for this question until at least 3-5 weeks.
I'm looking for pointers/suggestions because I now have to test in Access2003 Runtime on MONDAY!!!!
What it is, is:
I have a query which is a list of items that need to be worked out of 65,000.
Of that 65,000, 20,000 end up in the query (Actually do need looking at after an Automated process)
(it's adapting the query/process I think I need)
Query:
EntID - UniqueID for a household
Applicant1 - Number lookup value for Applicant 1
Applicant2 - Number lookup value for Applicant 2
Qualifies - 1 = Yes, 2 = No, Null = Not worked.
That's basic building block of the Query which is Drives the main Form.
What I would like is a scenario similar to this:
Person A opens record 1
Person B opens record 2
Person C opens record 3
Person C finishes record 3
Person B finishes record 2
Person C opens record 4
Person B opens record 5 (he/she took a little break)
Now that's the way I would like it to work, but there will be other factors and this is where I'm all ears for anyone who is used to this type of system.
The problem I can't figure out is that the Main Form has several subforms, but none of the data is to be edited, it is there for visual purposes only.
The users will be creating records via the Main Form through code, but not directly into any tables/queries with which I can use conventional record locking, that I can see.
My thoughts are that Person A calls up a record based on Min EntID and somehow locks it so the Person B looks for Min EntID Where not locked.
--------------------------------------------------
Question Time:
Please, please can someone point me in the direction of how to do it and more importantly the correct order of events?
I have tried doing the Min EntID and locking the record, but while Person A is running the Min Query, Person B is running it also so they end up with the same record as B has the record on screen while A is locking it.
Also,
In the real world Person B might open record 2 and think, nah I can't be bothered.
I would like a proffessional opnion on whether C opens record 2 or should B be made to deal with it.
I think not given the huge time constrants landed square on my lap, I would get there with smaller questions, but I've spent the last 10hrs writing a Function, to get it ready for the testing lab, where the owner has changed the requirements 7 times and my head isn't working.
All or some help given will be GREATLY received.
Any further information needed, just ask.
Hope you can help.
Cheers,
Ian Mac
View 4 Replies
View Related
Jul 7, 2014
This is what the search box must do:
- Searches 3 fields (StudentID, FirstName, LastName) and jumps to the relevant record.
The closer to the search box in the navigation bar below, the better, so this is what I would really like too.
- A repeat click would take me to the next result/record (if any) with the same criteria
- Instant search as soon as I start typing
View 11 Replies
View Related
Jan 12, 2013
I am the administrator for a 2010 Access Client Database that consists of many clients with their information and we have three users whom go in and edit-add information to this Database and it hasn't been a problem until two users have tried to edit the same client record at the same time and then we have had some issues...
So, not sure this is even possible, but can one prevent more than one user being on the same client record? Is there a way to have a message come and say this record is in use?
View 1 Replies
View Related
May 24, 2006
Hi, I'm currently creating a performance review database for my team and I'm having a few issues.
I was wondering if there is any way other than making a multi table query to have multiple tables linked to a form. The reason for this is because the performance review has 5 sections and I made a table for each section and would like data entered into the form to be entered into those tables.
What I first tried was making a multi table query with all the fields from all 5 tables. Then when I am done updating the values in the form to that 1 table with all the fields I run an append query to populate the data into all the other tables. The issue with this way is it's a manual task and with autonumber on my tables I have to delete all the values from the other tables and then run the append else I have duplicates from when the data was entered last time. Any tips or help would be greatly appreciated.
View 5 Replies
View Related
Jun 2, 2005
Hi there.
I am in the process of creating a database without using the wizard and am confused regarding multi table queries. Can someone explain in simple terms why when you create a form from a query, some forms wont let you add data. I think it is to do with the join type but I just dont understand it at all. Say you are creating the order details query, you have to select the product ID from the order details table but the product name from the products table. Why is this? (lol).
Please help a confused newbie!!
Thanks
View 6 Replies
View Related
May 16, 2015
I created a database to record lesson information (see attached sample). When there was only one student per record, everything worked fantastic.
However, management would like to have one record per lesson, which means that up to eight students would need to be accounted for.
Adding additional Student_IDs to the lesson record table and the associated links to the student table was simple. Inputting lesson information was pretty straightforward.
The problem arises with output. There is one report to review an individual student's past lessons. Also, there is a form in which to export the lesson's public comment.
Both use different queries, but with the same name as the form and report.
Both the report and exported Excel file are blank, no data.
I'm building this in A2010, but saved as A2003 (working environment).
View 5 Replies
View Related
Apr 7, 2014
I am creating a small database to record payments.
There are three tables :
Supplier - tblSupplier
Payments - tblPayments
Invoice - tblInvoice
There are two Forms :
frmPayments (Bound to tblPayments) - Main Form for payment entry.
frmInvoice Sub (Bound to tblInvoice) - Sub form to display not paid invoices.
What i need is a Command button , lets named it "Commit", to perform a few tasks :
1. When click, prompt message asking whether to Save current payment record when all relevant input is completed during data entry.
2. If Answer is "Yes", then it will compare the "Invoice No" on the Main Form with the "Invoice No" of the Sub
Form , and if found to match, then put a tick in the "Yes/No" field of Invoice Table (tblInvoice) against the matching "Invoice No" of Main Form. This is to record payments made to this particular Invoice in Invoice Table.
3. Proceed to save current record, Refresh Main Form to be ready for a new data entry.
4.If Answer is "No", discard all current entries in the Main Form, Refresh to be ready for a new data entry.
Sample DB is attached...
View 1 Replies
View Related
Mar 6, 2006
I have a master table with several pick list columns. One field in particuliar has 3 options(fed from a separate table):
a
b
c
But, the user will have a need to select more than just 'a' for example. they will need to select 'a' and 'b', or 'b' and 'c'....etc
Can this be done in a table within Access?
Thank You,
RRA23
View 1 Replies
View Related
Jun 16, 2005
I'm creating a database that contains some information spread out over multiple tables. In order to enter information into the database there are a couple forms that the user can access to enter all the information, and these data will be placed in the appropriate tables. I am also creating a form that will be used for data-viewing and simple editting purposes. This form will contain data from many different tables and viewed all together in the same form.
I created this second, more complicated form (more complicated because it contains more tables all viewed on the same form) and editting and adding new data doesn't work. It won't let me type in the fields. I've narrowed down the problem to the combination of three tables and I will explain the nature of the relationships of these tables.
First of all, I have a table the contains general person information. These needed to be broken down in to sub-groups, employee and contract. Each of these two sub-groups of people has their own specific fields that need to be entered, so I created three tables. The first table, person, has general person fields. The other two tables, employee and contract, contain fields that are specific to their person type and a foriegn key referencing the associated person information.
My problem is that, on the more complicated form, I wanted to put all the fields related to that entry. So a contract person would have all the fields filled in for the general person, the fields for contract person would be filled in, and then the fields for employee would be blank. Now, I'm sure there is a better way to do this (for example gray out the employee fields when a contract person is viewed, or hide fields for employee etc.) but I'm new at VB and that seemed more complicated.
If anyone has any ideas on what the problem might, or how I could resolve this, that would be amazing. Thanks in advance
View 3 Replies
View Related
Mar 11, 2014
I'm having a big struggle with adding a multi-value lookup field to a table. I need to provide a long pick list of items (from a source table) but these items must not be output (in forms or reports) in alphabetical order, they need to remain in the order that they are in the source table.
Access wants to alphabetize the output even if a I add a number field to the source table and sort by that.
Is there any way I can persuade access to just give the selected items back in the same order as in the source table ?
View 14 Replies
View Related
Jun 23, 2014
i have a form with a control for "repair type" and a subform that has a query that searches a table holding vehicle repair records. on the form the user can enter up to 5 repair types, [repair1], [repair2], etc.. Then the control on the form holds each type of repair to search by. Since the user could use one or all five repair fields and put repair types likes brakes in field one on one record and field 5 on another record, how do i search to just pull out those records that have the specified search criteria?
If i put in [form]![control] in each of the 5 criteria in the query it comes back with no records, because it it looking for that type in every repair field rather than once between the five fields?
View 2 Replies
View Related
Oct 20, 2013
So I have an event table with a muti value field with all the people on that event. This people field is look up column from my people table (so shows the name but stores the key).Anyhow what I now need to to do is record logs from the event for each person (from that muti value people field). I want ideally be prompted to enter the log data for each person, and show it related to the event it is for.
View 1 Replies
View Related
Oct 28, 2013
problem using MS Access 2010. One of my database field's is using multiply values (you can add one than more values in that field by checkbox). So, for example, one record in that ONE field looks like this: "Gastropoda; Mermithidae; Nematoda; Oligochaeta; Scorpiones". When I'm going to "PrivotTable view" I have only "Ga" for instance instead of full names...
View 13 Replies
View Related
Mar 26, 2012
I am trying to make a simple database where the data entered in a form will update to a table. My issue is, one of the fields is manufacturing location where I would like the user to be able to enter multiple locations and then have those locations update the table where the record is stored. I've been able to set up a list box with multi-select but am stuck at getting the table to update with the choices made from the list box selection.
View 2 Replies
View Related
