Ask Experts Questions for FREE Help !
Ask
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #1

    Nov 17, 2015, 06:15 PM
    Help with multi-table query results to populate a form
    I have three tables each with primary keys with relationships to another table in which each of these fields is a foreign key. I have attempted to prepare a query using these tables (requesting parameter input of values for these primary keys) to populate fields on my form. I would intend to use this form to create a transaction entry into a "transaction" record table. So far I am able to enter only two parameters in this sequence and then my form is only opened as a blank. I would appreciate some guidance in what I need to do to clean up this confusion. I would provide my database, if needed. Thanks.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Nov 19, 2015, 06:02 AM
    First, what is the SQL of the query. Second why are you using parameters? If you want to add records to a table, I would not use a multi-table query, but rather use subforms.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #3

    Nov 19, 2015, 10:15 AM
    Hello Scottgem. Thanks so much for your response. The SQL for this form record source is SELECT tblAssets.AssetID, tblAssets.PerformanceType, tblAssets.Title, tblAssets.PrimaryLoc, tblAssets.SecondaryLoc, tblAssets.TertiaryLoc, tblBorrower.BorrowerID, tblBorrower.LName & ", " & tblBorrower.FName AS Fullname, tblBorrower.H_Phone, tblBorrower.Street, tblBorrower.City, tblBorrower.State, tblBorrower.ZIP, tblUser.UserID FROM TertiaryLoc INNER JOIN (SecondaryLoc INNER JOIN (PrimaryLoc INNER JOIN ((tblUser INNER JOIN (tblBorrower . It appears that the SQL is not complete, but I don't know why. I have attached a screen shot of the query showing the table relationships, etc.

    The summary scenario for my using this form/query is this. A "user", who knows his/her ID, receives a request to borrow an asset. Identifying the borrower's ID, the user searches the Asset DB to ascertain the O/H status of the requested asset and when found identifies the Asset ID, status, location, etc. Thus, my assumption was that knowing these three IDs, the User could use parameters to input them to create a "loan" form through which the borrower's signature would acknowledge asset identify, date loaned and return due date. It would be intended that the form on update or close, etc. would create a transaction added to a "Loaned Asset Table". I have attached a screen shot of the design view of this form. As explained earlier, I obviously have not yet learned enough to make this form work and I would have to concede I don't know how to accomplish the objective using sub-forms either. I would appreciate your further comments.
    Attached Images
      
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Nov 19, 2015, 02:12 PM
    You are making this more complex then it needs to be. You need three tables:

    TblAssets: information about the assets.
    TblBorrowers: information about the people who will borrow an asset
    TblLoans: information about the lending of an asset. Something like this: LoanID (PK), AssetID (FK), BorrowerID (FK), CheckoutDate, CheckInDate

    You have a main form bound to the Asset table. You have an unbound search box where you can enter/select an asset and bring up that asset. You then have a subform bound to tblLoans. This form shows all loans of that asset. If the asset is out, then there will be one record with a blank checkInDate. If its in all CheckInDates will be filled.

    If it s a return then you fill in the CheckInDate. If this is a loan, then a new record is created with a CheckOutdate (if available). Use a combobox to select the Borrower. The AssetID is then entered automatically.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #5

    Nov 19, 2015, 05:56 PM
    Thank you for your very helpful guidance. Your conclusion that I'm making this more complex than is needed is certainly true. I'm going to have to spend some time digesting your suggested plan. It seems to me I already have the necessary tables: tblAssets, tblBorrower, tblUser, & tblAssetInOut. Now I just need to figure out how to coordinate all that as you suggest. Incidentally, how do I differentiate a Foreign Key. For example, in my tblAssetInOut, I include a field for AssetID, BorrowerID & UserID. Since these are Primary keys in their own table, does this inclusion of them in the tblAssetInOut make them Foreign keys? Or is there a setting that more clearly defines them as such in that table?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Nov 19, 2015, 07:22 PM
    A foreign key is not designated except by usage. It is a field that is used to link to a record in a related table. Usually a primary key.

    What is the difference between tblBorrower and tblUser?
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #7

    Nov 23, 2015, 05:55 PM
    Thanks for the clarification of the definition of a foreign key. I've been thinking of them in that way, but thought there might be a setting I've been missing.

    The User is my acronym for management, staff and volunteers (i.e. per access level for control of assets). Then, Borrower is just that, someone who temporarily takes possession of assets. It might be that a User would become a Borrower, but, if so, they would be ID'd separately as a Borrower and would be handled in that role by a separate User.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #8

    Nov 24, 2015, 06:08 AM
    Could a borrower not be a user? The only reason to keep them separate is if the borrowers would not be users.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #9

    Nov 24, 2015, 09:35 AM
    Of course, I see your point. We do have some limitations/requirements on User qualification; but, we do have volunteers so we're pretty flexible. However, as you point out a Borrower might well become a volunteer or staff member (paid) at some point. But, I would see their "identity" completely changing at that point (a new User ID). Having everyone in a single table might save space, but it seems there would have to be some identifier for the different roles and everyone would not meet that need. Anyway, until I become more experienced in Access, I think I better keep them separate. I do appreciate your guidance.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #10

    Nov 24, 2015, 12:39 PM
    Scott, I'm struggling trying to set up my forms to follow your earlier suggestions. I have set up my Main form bound to Assets and using a combo box am able to select an asset on the Main form. Then, I have my tblAssetsLoaned with relationships linking AssetID, BorrowerID, UserID, etc. But, when I try to prepare a subform using tblAssetsLoaned, under More Forms, I do not see the Forms with subforms option. I would appreciate your help in what I'm missing. I am able to create frmAssetsLoaned based on tblAssetsLoaned, but this form and Main don't seem to be linked.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #11

    Nov 24, 2015, 01:58 PM
    I generally create my forms (using the wizard) and then embed them as subforms. So if you have frmAssetsLoaned, then open the main form in Design mode. On the Design ribbon find the subform control (looks like a little form) its near the bottom of the list. Select it and go to the form and draw a rectangle to the size you want. The Subform wizard will then open asking you whether you want to use an Existing form (Yes), then giving you a list of forms to choose from. Choose your form and it should prompt you to link on AssetID. Give the subform control a name and you should be done.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #12

    Nov 24, 2015, 04:46 PM
    Thanks, Scott. That worked just as you described. The selected Asset on the Main form (by AssetID) is linked to the subform and the AssetID field there shows the same number. However, the other fields are not populated, i.e. asset locations from the tblAssets do not show up on the subform fields even though the relationships are there (at least I believe they are properly set up). How do I assure that this is, in fact, done? Also, the LoanID shows as "New", since this transaction record has not yet been completed. I have not tried to add the tblBorrower or tblUser, which will be needed until I have a better understanding of the situation so far. I will surely appreciate your further guidance.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #13

    Nov 25, 2015, 06:30 AM
    What is the Recordsource of the subform?
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #14

    Nov 25, 2015, 06:11 PM
    I don't see Recordsource, as such. The Source Object is frmAssetsLoaned with Link Main Fields as AssetID and Link Child Fields as AssetID. Further, neither of these is more uniquely identified, e.g. Main as frmAssetsMainForm.AssetID and Child as frmAssetsLoaned.AssetID. Is this satisfied by the form/subform relationship?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #15

    Nov 28, 2015, 02:22 PM
    You have to make sure the subform is selected before you can check its Recordsource. Or you can open the form outside the subform. If you are only seeing the SourceObject then you are not inside the subform.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #16

    Nov 28, 2015, 07:44 PM
    Thanks for the clarification. When I open the subform separately, I find the subform, frmAssetsLoaned, the record source is tblAssetsLoaned. Now when I open the main form, frmAssetsMainForm, it opens together with the subform. The main form is populated with the first record information and an unbound box in which I can enter the AssetID which is to be loaned. When this AssetID is entered, the subform LoanID modifies from "New" to "1". The Main Form is populated with the entered AssetID information and the subform is partially updated, but, all fields are not updated. Further, the tblAssetsLoaned is not updated.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #17

    Jan 5, 2016, 11:35 AM
    Unfortunately, I have had to leave this project since my last post, but am now back looking for help again. So let me review. I have created a form, frmAssetsMainForm, bound to tblAssets. This form has a combo box by which an asset to be loaned can be selected by AssetID from its pull-down list. Then, the form populates the fields AssetID, Title, PrimaryLoc, SecondaryLoc, TertiaryLoc and OnHand status. Then, there is a subform, frmAssetsLoaned, which I have captioned, sfmAssetsLoaned. The record source for this form is tblAssetsLoaned and the form includes all the fields from that table (LoanID, AssetID, BorrowerID, LoanDate, UserIDOut, ReturnDate, UserIDIn, PrimaryLoc, SecondaryLoc and TertiaryLoc); however, it only shows LoanID as "New" and AssetID as the selected value. Any input to the subform, such as BorrowerID, causes the LoanID to be updated to the next available value. So, my question(s). How do I set the subform to then update to today's date and reflect the currently logged-in UserID as the UserIDOut? Further, I want the ReturnDate to be updated with a pre-set increment, e.g. 15-days, 30-days, etc. Finally, the locations for this asset should be updated on the subform. Thus, the tblAssetsLoaned will be updated accordingly and a receipt/checkout form printed for the borrower/library record. Ultimately, I would want this update to be reflected in the tblAssets to show that the asset is no longer OnHand. I would appreciate help in achieving this objective. Help with one step at a time would certainly be welcomed. Thanks.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #18

    Jan 7, 2016, 06:29 AM
    How do you capture current UserID? Use the DateAdd function to add a specified increment for the Return Date. To tell if an asset is not OnHand, you check tot see if it has no Returned date.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #19

    Jan 8, 2016, 10:19 AM
    Hello ScottGem, thanks for your response. I should have been clearer in my review. The User is the volunteer/staff handling the transaction of issuing the asset. He/she (likely knowing their own ID) has logged in previously with ID from tblUser. He then queries tblBorrower for BorrowerID. He then queries tblAssets to insure that Borrower's requested asset is, in fact, OnHand (indicated by Yes/No status). Now, I am ready to create the transaction, frmAssetsLoaned, in which I select the AssetID in the combo box. This leads to my question as to how I link the subform info so that if, for example, I enter the BorrowerID that the subform updates with the info from frmAssetsMainForm. I was not aware of the DateAdd function, so thanks for the pointer. If I add it as an expression in the Property Sheet, which entry should I choose? (at this point, I have selected OnChange). Thanks again for your help.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member
     
    #20

    Jan 12, 2016, 09:56 AM
    Hello ScottGem, This procedure is still not working. What needed information am I not providing that will lead to the solution. I'm trying as best I know how. I will appreciate your further help. Thanks.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Problem using a Pivot Table Form as a Sub-Form -- Access 2003 [ 3 Answers ]

I made a Pivot Table Form using the Pivot Table Wizard in Access 2003. It works great when opened.as a form. But when used as a sub-form, only the data columns appear. The Row Heading column is missing. The sub-form is not linked to a child field on the main form. Has anyone seen this...

Efficient ways to get new results of a particular search query automatically? [ 2 Answers ]

I'm trying to know if there are any efficient or powerful tools or websites which provide me search results automatically. I'm looking for a feature which is more efficient that Google Alerts. Google alerts at times deliver me search results for that search criteria however, I believe its not that...

I need to interpret the results of the two way anova table in apa format [ 3 Answers ]

interpret the results from the sex X treatment condition table you are given. Report results in apa format and write a one or two sentence interpreting the results(main effect and interaction anova table factors are sex with df of 1, ss of 8.00, ms of 8.00, f of 0.96 and p 0.399 treatment are...

If there are more fields in a table will it take more time to get search results [ 4 Answers ]

Hi. I want to make a database which'll have many tables. One of the table is USER. So I want to search username in User table. My question is if there are 3 fields in this table will it take less time to get search result of username as compared to if I've 30 fields in this table. Or you can say...


View more questions Search