Multi-record Query
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 Replies
ADVERTISEMENT
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 5 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 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
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
Aug 5, 2015
I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.
User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.
What I would like to know is:
1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1
2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1
3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code
Code:
Private Sub Edit_A_Click()
'Get Data to text box control
With Me.R_P_Data_P_Subfrm.Form
Me.txtrID = !rID
Me.txtrID.Tag = !rID
Me.txtrefNo = !refNo
Me.cmbrpc = !rPC
[code]....
View 8 Replies
View Related
Dec 8, 2011
I'm new to access, and have discovered its easy to write large queries by breaking them up into smaller queries and having each subsequent query reference the one before it. My problem now is that I want to combine these queries into a single query, so as to make it easier for anyone who comes after me to edit/understand what was done. I can't seem to find a good explanation of how to do this, but it seems like there should be a way.
View 1 Replies
View Related
Aug 23, 2005
Working on a sales guest registration database and need a some assistance with a query for my report. I have the following fields in the table:
Communities
RepName
Date1stVisit
MoveTime
I have created a query which works without a problem. I have also created a dialog form to use with a report that I've created. Everything works ok if I fill in all the boxes on my form with data but in some instances they want reports that are not filtered all the way down. On my form I have an unbound combo box for each of the fields above. I've tried several of the suggestions found here but cannot get it to work completely. The last thing I tried was the following criteria for each field I want to filter on:
like [forms]![repdialog]![communities]& "*" or is null
like [forms]![repdialog]![repname]& "*" or is null
>= [forms]![repdialog]![datebeg] and <= [forms]![repdialog]![dateend]
like [forms]![repdialog]![movetime]& "*" or is null
This sort of works but I must be missing something because it gives me too much data. For instance if a just wanted to fill in the REPNAME box on the form it should give me every record that has that name in it but I get more that that - I think because of the is null comments. Any one help me correct this?
Thanks,
Chester Campbell
View 14 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
May 30, 2006
Hi I Like to know how to aquire the correct results:
Table:
ID - Personalinfo - location - records - date
102 - Dave Blogs - Bristol - Queen - 12.12.03
103 - Brian Smith - Plymouth - SKA - 12.11.03
102 - Dave Blogs - Bristol - Madona - 12.12.03
By selecting the ID I want to show all listings for that ID.
ie:
102
Dave Blogs
Bristol
Queen 12.12.03
Madona 12.12.03
Im going round in circles
View 4 Replies
View Related
Feb 10, 2006
I have a query which calculates Lagtime: which is the difference between the audit date and the current date.
Lagtime: DateDiff("d",[Auditdate],[Date]) and the criteria is >30
this works fine and shows the reports that are over 30 days overdue.
Now I need: There are 2 fields (both are y/no) Minor and Major. Minor need to be answered >30 and Majors with in >7,
how would I write the expression show me Lagtime if over 30 days for a minor and 7 days for a major.
View 1 Replies
View Related
Sep 7, 2006
I have a database... 1 table...40 fields..
I currently have a query set up as a basic a LIKE search for each field and I have to do 40 each time..
Is there a way of being able to do 1 query and have it search all 40 fields?
So my field headings are
Part No
Description
barcode..
Album name
Singer/Group
and so on...
I since some of the albums are compliations I require to search all the fields...
Any ideas?
View 6 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
Apr 25, 2014
i need to know how to get the multi value from list box in my form to be used in a query the list box worked great when it was not multi value
View 3 Replies
View Related
Oct 16, 2014
I am new to access forms. I am trying to use the 2 checkboxs for my make table query.
Future cost checkbox and marginal cost checkbox.
If future cost is checked, then I want 00 as criteria for the field cost type in the query, if marginal is checked then 01. If both checked, then I want both.
Seems like a iif statement is not working in my query. I am trying to create an event now but I do not understand the VB codes.
View 8 Replies
View Related
Aug 4, 2007
Hi guys,
I've seen different log in forms and tips on multi-user use but can't seem to find info on the following...
We have 6 people at work and I'd like to assign which user updated a form or created a new record...this would be particularly useful on the sales part of the database to track who made a call to a particular customer...
Best ideas anyone?
dazza61
View 6 Replies
View Related
Aug 14, 2005
I have a query set up which needs to have different criteria at run time depending on values selected by the user. If no option is specified, I need to pick up all records with 'Nulls' in them else, if date is specified by user, I need to pick up all records with date > than specified date. The query is able to pick up the date value from an unbound text box in the form. I used the same field in the form and populated "Is Null" in the field and it does not work. How do I pass this as a paramater to the query from the form?
Is there a way around without setting up 2 sets of queries and reports?
Thanks,Priya :mad: :confused:
View 5 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
