Page 4 of 4 FirstFirst 1234
Results 46 to 59 of 59
  1. #46
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    James,


    I worked in database/data management for years aka -I have some thick skin, so I'm sticking with your efforts to help you get this conversion under control.
    In the last post I sent (in response to Mike) I put in a link. There is excellent info there on Normalization and Codd's rules. Take some time and review them -- Normalization is your issue at the moment (in my view). Also, in my signature the Database Planning and Design link has several pertinent articles. If you think you just need a quick refresher --watch some of the 5 videos by Dr. Jennifer Widom. I highly recommend you watch them in sequence.
    Feedback is encouraged.
    Good luck.

  2. #47
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi James

    I have attached an amended database which covers the process as follows:-

    On the Form that opens at Startup you can select a Specific Customer using the Search Combobox in the Form Header.

    The Subform allows you to enter specific information about the Sale.

    If it is a New Sale for this Customer then you use the Add New Sale button. (The other buttons allow you to navigate through all of the Sales for the Customer Selected.

    Once you have entered Sale Details the next Subform allows you to add Invoice Information for the Specific Sale Record.

    This is just one method but there are numerous other ways of Viewing/Adding Data.

    Study the relationship diagram to see how data is related.

    In the Form Example I have Colour Coded the Matching Primary/Foreign Keys.

    Comments?

    You can PM me if you want to discuss.
    Attached Files Attached Files

  3. #48
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Quote Originally Posted by mike60smart View Post
    Hi James

    I have attached an amended database which covers the process as follows:-

    On the Form that opens at Startup you can select a Specific Customer using the Search Combobox in the Form Header.

    The Subform allows you to enter specific information about the Sale.

    If it is a New Sale for this Customer then you use the Add New Sale button. (The other buttons allow you to navigate through all of the Sales for the Customer Selected.

    Once you have entered Sale Details the next Subform allows you to add Invoice Information for the Specific Sale Record.

    This is just one method but there are numerous other ways of Viewing/Adding Data.

    Study the relationship diagram to see how data is related.

    In the Form Example I have Colour Coded the Matching Primary/Foreign Keys.

    Comments?

    You can PM me if you want to discuss.
    Hi Mike,

    Thanks for you database. I needed to stick with the current format as I am trying to replicate the existing Lotus Approach database as much as possible. The users are not particularly computer literate and they are used to the system.

    That said, your relationship graph helped me a lot and I managed to resolve my problem using your example so thank you very much! Very grateful for your help.
    My database now pulls the make and appliance from the stock system and populates it in the sales field as it does in the original Lotus version. It also stores that data in the invoice database.
    I will use this information later when I need it to print an invoice.
    I've attached the latest version so you can see where im going with it.

    What i am a touch stumped with is how to sum the values in 'S Total' field of the sub table and calculate them as indicated below.

    Click image for larger version. 

Name:	picofcost.jpg 
Views:	24 
Size:	108.4 KB 
ID:	41132

    I cant seem to work out how to sum all of the values together. Any ideas?

    Thanks in advance
    Attached Files Attached Files

  4. #49
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    James

    The attached db is a copy of the one you posted with some amendments, to show how you might get the effect you wanted on your form.
    I have added the PK field [Customer Number] to the Record Source of the form.
    I have made the form open at a New Record.
    I have put code in the After Update events of the "Postcode" and the "DoorNo" controls. This code looks for the existence of a record with the same PC And DoorNo. If found, it filters the form to only show that record and removes the entered details from the new record.

    I don't understand your table structure or the actual purpose of the db but I hope this helps in some way
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #50
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi James

    I'm afraid I am not willing to give further advice seeing as you are going ahead with your original design.

    We are trying our hardest to give you advice on how to structure your tables which you have decided to ignore.

    Luck with your project.

  6. #51
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Quote Originally Posted by Bob Fitz View Post
    James

    The attached db is a copy of the one you posted with some amendments, to show how you might get the effect you wanted on your form.
    I have added the PK field [Customer Number] to the Record Source of the form.
    I have made the form open at a New Record.
    I have put code in the After Update events of the "Postcode" and the "DoorNo" controls. This code looks for the existence of a record with the same PC And DoorNo. If found, it filters the form to only show that record and removes the entered details from the new record.

    I don't understand your table structure or the actual purpose of the db but I hope this helps in some way
    Hi Bob,

    Thank you so much. That....kind of works.

    If you enter M19 1LL and 64 as the door number then it correctly populates the address and works well however, any other address I try to recall doesnt work and im not sure why.
    Sometimes it also seems to remove the address after entering a matching postcode and door number.

    Try yourself to enter a new record with a new postcode and door number. Then start a new record and enter those same details and it removes the postcode.
    I think its somehow tied to the customer number not populating correctly but im not sure.

    Regards

  7. #52
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    James

    If you change the join in the Record Source to include all records from the customer table, then the top halve of your form that handles customer data works.

    Your problems here, as others have already stated are as a result of having an incorrect table structure and relationships. For example, the two tables used in the Record Source for the form are joined by their PK fields which are both AutoNumbers. At the moment you have no valid way to join the tables.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #53
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Quote Originally Posted by mike60smart View Post
    Hi James

    I'm afraid I am not willing to give further advice seeing as you are going ahead with your original design.

    We are trying our hardest to give you advice on how to structure your tables which you have decided to ignore.

    Luck with your project.
    Hi Mike,

    As I said, I have to replicate the design from the previous database.

    As for structuring the tables differently, I haven't ignored it, I don't understand which parts are wrong.

    But thank you for your help, it was appreciated and I will hopefully update when I'm further down the line for those interested.

    Regards

  9. #54
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi James

    Can you explain what you mean by "Replicate the Design"?

    Are you talking about how the Form Looks?

  10. #55
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think most would say - " I have to ensure there is the same functionality when converting a database from one RDBMS to another."

  11. #56
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Quote Originally Posted by mike60smart View Post
    Hi James

    Can you explain what you mean by "Replicate the Design"?

    Are you talking about how the Form Looks?
    The form looks and actual use.
    I designed the original database through trial and error - and help of support forums.

    I'm not by any stretch a DB guru. That said, I'm around a third of the way through this and bar the odd struggle it's almost identical so far.

    Regards

  12. #57
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi James

    I must stress that when you say you are a Third of the way through this, most would say "Bite the Bullet" and start again with the correct Table Design.

    Your current table relationships are wrong.

  13. #58
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi James

    See the attached ER Diagrams with an explanation as to why your table structure is wrong.
    Attached Files Attached Files

  14. #59
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    James,

    What is latest status? Did you look at Database Planning and Design materials mentioned in post #46 ? If you work through one of the tutorials from RogersAccessLibrary in the link, you will learn more about structure.
    Good luck with your project.

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple Sub-Form Lookup Table Updating Issue
    By tmptplayer in forum Forms
    Replies: 8
    Last Post: 08-09-2017, 07:45 PM
  2. simple snytax (lookup most recent record)
    By markjkubicki in forum Programming
    Replies: 9
    Last Post: 06-20-2017, 02:50 PM
  3. Newbie, seems simple to lookup value?!
    By ayce123 in forum Queries
    Replies: 3
    Last Post: 03-13-2011, 12:22 PM
  4. Lookup Form (should be simple)
    By joshlee in forum Forms
    Replies: 3
    Last Post: 05-06-2009, 12:04 PM
  5. Simple record lookup?
    By Transeau in forum Access
    Replies: 0
    Last Post: 01-18-2006, 10:27 PM

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