Results 1 to 11 of 11
  1. #1
    excal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    7

    Forms that update multiple tables, including junction tables

    Hi all, access noob checking in again. I solved my last problem through google, but I fear this one will be a bit more complicated, so I'm hoping for any help I can get.



    So I have a database for a project that is simulating a used bookstore's database system.

    My relationship table is attached below. Please forgive the lack of normalization on the 3 type of employees on the right side, it's a specific project requirement. I understand it's not good but I don't foresee an issue for this particular form.

    I'm trying to create a form that will work for a student bringing in their books to sell to us. Each order can contain multiple books. People cannot bring more than 1 of the same book nor more than 5 books.

    I would need to create a record in purchase_inventory (I have been working on this part for a bit, so far I managed to autonumber purchase# through dmax+1 to avoid using the autonumber in the table itself) and also records in the junction table [purchase_detail] depending on how many book is brought in, and also update the inventory table.

    You can assume all student_ID infomation, everything in the inventory table are pre-set up.

    For example:
    Student 1 brings in book A to sell. This is my second purchase.

    I need to create a record in purchase_inventory:
    Purchase#: 2 (Default value, populated through dmax)
    SR_ID: (Manual input based on employee serving customer)
    Student_ID: (Manual input, should validate against SAF_Student_List)
    Purchase_Date: Auto populate through default
    Purchase_Time: Auto populate through default
    Sold_Status: Set to unsold automatically

    [Purchase_Detail]: 1 record per book brought in, joint primary key of purchase# and ISBN
    Purchase_Price: Should be populated through a lookup in the inventory table
    Purchase_Quantity: Ignore, will auto populate as 1
    Book_Condition: Drop-down menu with several options

    [Inventory]: Master file, must update quantity_on_hand based on detail given in junction table.

    I'm not sure where to start with this. I've always done simple forms by creating a bound form and just updating fields. My challenge is that I have to create a single record in 1 table, possibly multiple records in another, and then update a master table, all in 1 form. Is this do-able, or is this too ambitious for a access noob? From my classes I understand I'll need to use some subforms, but we've only learned theory and we were more or less thrown into this access project head first.

    Thanks in advance for the help, please ask if you need clarification regarding my problem.
    Last edited by excal; 07-11-2011 at 09:07 AM. Reason: Updated relationship table

  2. #2
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I do not intend to register at the stated domain so I suggest if you want to publish technical data you use the mechanisms provided by this forum.

    Purchase –> PurchaseDetail is a classic one-to-many pattern that occurs over and over again. The purchase information will be shown and manipulated on the parent/master form. This form is a standard (one record at a time) form. The purchase details will be shown and manipulated on a continuous (multiline – one record per line) form. The continuous form is inserted in the parent form as a subform. The synchronisation between parent form and subform can be done manually or, as probably in this case, automatically by Access. Providing that the binding of both forms is correct then Access will handle any updates and additions to the two tables, Purchase and Purchase_Detail (your names).

    However there will be cases when either the student does not exist on file or the volume offered for sale does not exist on file. In such cases the routine transaction cycle has to be interrupted while the required data is added. This will involve at least two other forms, one for student additions and one for volume additions. After adding new data the purchase forms need to be requeried.

    ‘Sold_Status’ is on the wrong table; move it to Purchase_Detail.

    ‘Quantity_On_Hand’ should not be a field in the database. It is a derived value and should be calculated every time it’s required. Let me explain further. From the fields you mention – particularly Condition and Sold_Status – I conclude that the requirement is to track individual volumes. So the inventory quantity has to be determined by counting the number of unsold volumes. This is unlike say nuts and bolts where one is not interested in individual nuts and can just maintain a number as the quantity on hand.

    OK, enough pontificating, where should you start? I suggest you start by creating a standard form for the purchase and a continuous form for the purchase details. Do not worry too much for now that the continuous form will be embedded in the standard form but concentrate on binding the forms (Record Source) to the correct tables. When you’ve done that, post back and we’ll guide you through the next step.


    BTW, Purchase_Detail is not a junction table. Here’s a fragment of a database design I made from your description.

    Attachment 3792

  3. #3
    excal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    7
    Just a quick clarification before I start, when you say I should start with the purchase and continuous form, do you mean creating 2 forms separately?

    EDIT: I'm working on the subform at the moment, and ISBN is a joint primary key. However, ISBN is pretty arbitrary to the end user and annoying to have to read and recognize even with a drop down. Is it possible for the form to pull the book names and when selected and submitted, the form would update the table with the ISBN?

    Would I use this through an input mask or would it require a query?

    EDIT 2: I have the main form set up and the sub form inserted. However, I can't seem to make updates on the subform and the main form updates before i click the submit button. DataEntry is Yes on the subform, and no on the main form.

    When I try to put something in the subform, it tells me that a record must be in the purchase_Detail table already, which doesn't make sense since I am trying create new records.

    EDIT 3: Talked to my professor today, she wouldn't tell me how to do it (obviously) but did mention that I should start with a query and have the subform pull from the query. I'm not sure what she meant though...
    Last edited by excal; 07-11-2011 at 03:54 PM.

  4. #4
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Yes, two forms: subform are separate forms and can exist on their own without being included in a parent form. If is only when they get embedded in a parent form that forms become subforms. There may be visual design considerations when creating a form intended to be a subform such that it all 'makes sense.'

    I'll spend some time reviewing the database design that I can now see and get back to you on your other points.

  5. #5
    excal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    7
    Thank you, much appreciated.

    I have a subform set up, but I'm not sure how to input things into the junction table. This is what I have so far. I managed to use a SQL query to fix what i wanted in edit 1, and just to confirm, for row source, i now have:

    SELECT Inventory.Book_Title
    FROM Inventory
    ORDER BY Inventory.Book_Title;

    and control source remains unchanged as ISBN. Is that correct? Due to the fact that my forms aren't functional yet, i can't really test it.

    I have attached the form I'm having trouble with below, and what I have so far. I have the purchase number autopopulated using dmax+1, and date and time is also autopopulated. Sales rep and student id are drop downs, and as far as I can see, it's more or less complete (the main form)

    The only issue with the main form is that when i click into purchase detail, it updates immediately, even though i didn't click submit, and that i can't enter things in purchase detail as it says that a record is required, which doesn't make sense to me as i'm attempting to create a record.

    Reiterating, my prof said I should use a query, but being limited in my SQL skills (was only covered in 1 class, the class is primarly for database design in general) I am pretty lost.

    If this is really high level, then I suppose I can abandon it, because my project already fulfilled the criterions I need. I'm just going further because I feel really interested and feels that if i submit it now, it would be some what incomplete.

  6. #6
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Quite frankly, in my opinion, your professor should not have let you proceed without first ‘cleaning’ your database design. I believe you will spend progressively more time and effort supporting a flawed design than achieving the requirements. In other words you will be fighting the alligators rather than draining the swamp.

    I do not have the time or space to complete a full review but offer these overall comments. If it’s not too late to change things, do so now; if you have already much completed work then you will have to ‘go with it.’

    1. The names used, particularly table names do not always reflect the purpose and content of the table. Accurate names are critically important in understanding a design, even for you, the designer, when you return to the project after a period during which you may have forgotten certain aspects of the design.
    2. Names should be as concise as possible. You quickly realise this when lines of code extend off the right-hand side of the screen or page due to verbose names.
    3. Even when the names indicate a specific purpose for the table, the content (fields) does not always reflect that purpose.
    4. Much of the data is not normalised. I know that physical designs do contain denormalised data to be practical but in this case it is excessive and not what I expect for a physical design. (I know you are aware of the three staff tables that should be one.)
    5. There is duplication.
    6. There are fields on the wrong table.
    7. There are incorrect relationships.
    8. The primary keys are inconsistent; some tables use auto incrementing numbers while others use real-life values.
    Here are some examples of what I mean.

    What is the table, Bank. I don’t understand it. Certainly it is not a table of banks since there is no BankName field. Its primary key is Bank_Transaction (is that an autonumber?) so I conclude that this is a table of bank transactions but then I see Bank_Account_Description. Are you going to repeat the account description for every transaction? Date_Last_Updated and Time_Last_Updated are duplicates; the Access Date/Time type contains both date and time; only one field is required. The Bank table has a one-to-many relationship with Deposit_Cash? Surely it’s one-to-one if the situation is a bank transaction to a deposit.

    I have already pointed out that Sold_Status is in the wrong table. You say a student may sell up to five books at each Purchase_Inventory. I am sure these five books are not kept and sold as a ‘set.’

    I could go on. Instead let me try to help you with your immediate concerns.

    Edit 1

    ISBN is a horrible choice as a primary key. Most critical is that it is possible to have a publication without an ISBN – maybe not in your case but there are private publishing and printing organisations that will not bother with ISBNs. I assume this project is concerned with an academic institution and I think it quite likely that a lecturer might ‘publish’ his/her notes. Copies of these notes could quickly become tradable publications. A second reason is that (if I remember) an ISBN does not even have a consistent format. It also has the disadvantages you mention. So my advice is to include a ‘meaningless’ autoincrementing number as the primary key for Inventory. ISBN now becomes just another field on the table; by all means index it.

    (Your design caters for only one author per book unless Author_Name is intended to be a multivalue field. What are Buying_Price and Selling_Price: averages, standards, or perhaps most recent? As you use the term purchase elsewhere in the database, you should really be consistent and say Purchase_Price.)

    Is it possible for the form to pull the book names and when selected and submitted, the form would update the table with the ISBN?
    Yes it is possible to have drop down list of titles and something like this would be the optimal user interface. However bear in mind there may be hundreds of titles; the user does not want to go from the beginning each time so the drop down list should be combined with some kind of search facility. Again, beware of the definite and indefinite articles in titles.

    I assume the table you wish to be updated with the ISBN is Purchase_Detail. No! The foreign key on the detail table is now the ‘meaningless’ primary key of Inventory; ISBN exists in table Inventory and only in table Inventory; it is ‘looked up’ each time you need it.

    Would I use this through an input mask or would it require a query?
    To do what? I don’t think input masks are involved; queries will be involved. I would forget about the drop down list until the basic Form/Subform structure is working. Then you can decide exactly how the user interface should work when entering/selecting a book on a row of the continuous subform.

    Edit 2

    before i click the submit button
    Ahrrrg! Is it a requirement to have a Submit button? This is somewhat contrary to the way Access works. If you don’t want to update the tables until all the Purchase_Inventory data and all the Purchase_Detail data is entered then you cannot use bound forms and Access will not synchronise unbound parent/subforms. Basically you are on your own and have jettisoned a major benefit of using Access.

    You complain about the sequence in which Access updates the tables. Sit back and think about it for a moment. You cannot have a child, Purchase_Detail, without first having a parent, Purchase_Inventory. Consequently the moment you try to do anything with the subform, Access saves the parent form. (Actually it’s more fundamental than that; the moment you leave the parent form Access saves any changes; but the effect is the same.)

    I would abandon the idea of a Submit button and sit back and let Access take care of it all for you. If you still get an error message then something is wrong in your bindings and/or setup.

    Edit 3

    she wouldn't tell me how to do it (obviously)
    Why not? In my opinion instruction is OK while providing the answer/solution is not.

    did mention that I should start with a query
    Yes, your subform will be bound to a query. As you progress you will find that many forms are bound to queries rather than tables.

    What I have done for you is construct a small example Access project based on my design and names. This is attached; use it as an example. It is not complete and I have done some things in an unconventional way just to demonstrate the range of solutions.

    Attachment 3806

    PS Will now read your latest post.

  7. #7
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I have a subform set up, but I'm not sure how to input things into the junction table. This is what I have so far. I managed to use a SQL query to fix what i wanted in edit 1, and just to confirm, for row source, i now have:

    SELECT Inventory.Book_Title
    FROM Inventory
    ORDER BY Inventory.Book_Title;

    and control source remains unchanged as ISBN. Is that correct? Due to the fact that my forms aren't functional yet, i can't really test it.
    What you describe sounds close but not quite right. Let me try an explain using a first-person scenario.

    • When I am entering information in the purchase detail row, I need to identify the row on the inventory table corresponding to the book being purchased.
    • Rows on the inventory table are uniquely identified by the primary key. So I need to find/select the primary key.
    • Once I have the primary key to the inventory table I can look up all the other corresponding inventory values via this key.
    Don’t worry, it’s not nearly as laboured as that in practice. Here’s what you do (as a minimum).

    First design the recordset to which your subform will be bound. The following is my first cut:

    Purchase_Detail!Purchase#
    Purchase_Detail!ISBN
    Purchase_Detail!Purchase_Price
    Purchase_Detail!Book_Condition
    Inventory!Book_Title

    You therefore need a query joining Purchase_Detail with Inventory as the bound source for your subform. This works fine for existing records but what about new records before a value for ISBN has been selected/entered? Without such a value you cannot look up Book_Title. What happens after ISBN is entered or changed? Access won’t update the Book_Title without you telling it to do so. (I would also advise that at all times Book_Title is protected from change by the user.)

    The answer is that you have to trap a change in ISBN and ensure that the correct corresponding title is shown. Thus I would expect to see a macro or code attached to the ISBN After Update event that looks up the corresponding title and inserts it into the row.

    Now you are simply left with deciding upon the best way for the user to enter or select an ISBN. One way to do this is make the control bound to Purchase_Detail!ISBN (note this is the foreign key) a combo box. The Row Source for the combo box is a select query on Inventory extracting ISBN (as a minimum) and probably Book_Title. There are a few properties to set on the combo box for it all to work properly. The critical ones are to make sure the Control Source is Purchase_Detail!ISBN (binding) and that the Bound Column property indicates the column containing ISBN in your query. Column Count should be set to the number of columns in your query.

  8. #8
    excal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    7
    Thanks a lot, I will go through that. The assignment is due tomorrow, and I believe we still have time to fix most of what was mentioned. I will make as many changes as possible before proceeding. I'll come back with what I have and hopefully it'll be well on it's way

    The thing is, this is a very introductory course. I'm an accounting student, not really IT intensive, and this is just 1 course in my program and we're not expected to take any more (there are optional ones in the future) and I think my professor didn't answer because she told me that what i was trying to do was well beyond the scope of the course, and that it would be beyond our level (having nearly no instruction in access). The bare requirement was simply to make the tables (as per our design) and then provide 1 query, 1 report and 1 form, which I've done simple ones long ago.

    I'm just trying to go above and beyond because I'm a bit of a perfectionist, and also that through doing all the simple stuff, access really interested me and it's pretty satisfying when you get a query working or a form/report. With that said, going to get work, thanks again.

  9. #9
    excal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    7
    Still working on that subform, I'm not sure what I should be doing. But I ran into another issue.

    I'm trying to do a count of inventory by using a query on the purchase detail table to pull everything that is unsold. This is easy. Now I am trying to make a count of each occurance of each title, and I feel like I should be using dlookup but I'm not sure how to get it to work.

    I'll upload my database shortly so you could take a look. I have some pretty ambitious ideas but I dont know if I am going to be able to implement it. I'm going to draw up some design and such.

  10. #10
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    You sound really 'hooked.'

    Counting by title sounds straightforward and will use SQL aggregate functions and grouping. I await your attachment.

  11. #11
    excal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    7
    I assume it has to do with GROUP BY and COUNT()

    Working on figuring that out right now...not sure what to put in COUNT to make the query work.

    I have:
    SELECT Purchase_Detail.ISBN, Purchase_Detail.Sold_Status, Inventory.Book_Title
    FROM Inventory INNER JOIN Purchase_Detail ON Inventory.ISBN = Purchase_Detail.ISBN
    WHERE (((Purchase_Detail.Sold_Status)="Unsold"))
    ORDER BY Inventory.Book_Title;

    Originally.

    I imagine I have to make a dummy column that just has 1 in it for every row and then count that? Hmmm...

    EDIT: Got it to work!

    SELECT Inventory.Book_Title, COUNT(*)
    FROM Inventory INNER JOIN Purchase_Detail ON Inventory.ISBN = Purchase_Detail.ISBN
    WHERE (((Purchase_Detail.Sold_Status)="Unsold"))
    GROUP BY Inventory.Book_Title;

    Is that redundant at all? It works, so I'm pretty happy haha

    Just the subform to fix...6 hours before it's due, I don't know if I can get it...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 04-25-2011, 04:11 AM
  2. Importing from Excel and Junction tables
    By fatalmusic in forum Import/Export Data
    Replies: 0
    Last Post: 04-07-2011, 07:11 AM
  3. Tabbed forms and multiple tables
    By jonesy29847 in forum Forms
    Replies: 3
    Last Post: 03-22-2011, 10:03 AM
  4. creating forms using multiple tables
    By mfoster3 in forum Access
    Replies: 0
    Last Post: 03-09-2011, 06:57 AM
  5. update tables via forms
    By jazoo in forum Forms
    Replies: 0
    Last Post: 09-16-2008, 05:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums