Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 67
  1. #31
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46

    Quote Originally Posted by orange View Post
    DBID10T,

    Yes, I was aware that the RogersAccesLibrary tutorials would take you from a business description to a set of normalized tables and relationships-- a blueprint for your database. And further, that working with this model (paper and pencil) you could determine if stored data was sufficient for the reports/invoices/forms etc that you required. The underlying structure is key to a successful database. I hope the design experience from the RogersAccessLibrary tutorials was useful for you.

    You can find sample reports, invoices, bill of lading...... using google. Very helpful in design and for mock-up/testing.

    It appears that your basic concern now is access queries and other Access objects. There are several articles and youtube videos
    mentioned in the Database Planning and Design link. Richard Rost and Steve Bishop have tutorials at youtube, and there are others.

    Continued good luck with your project.
    Thanks Mate all of your advice has been spot on

  2. #32
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    LineItemAgreedPrice on my tblProjectLineItem

  3. #33
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I did comment a while ago that saving price would be advisable if possible price could change in future.

    Alternative is to create a new price record and 'deactivate' old one so it is not available for selection in combobox but still available for reporting.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #34
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by June7 View Post
    I did comment a while ago that saving price would be advisable if possible price could change in future.

    Alternative is to create a new price record and 'deactivate' old one so it is not available for selection in combobox but still available for reporting.
    Indeed you Did June7, I was just slow on the uptake learning curve is pretty steep here

    Progress is being made however slowly the reward will be sweet

  5. #35
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    June already stated you shouldn't use Look-uo FIELDS (in tables) - here is why: The Evils of Lookup Fields in Tables
    Also see The Ten Commandments of Access


    Why do you have these two tables in a 1-to-1 relationship?
    Click image for larger version. 

Name:	onetwoone1.png 
Views:	15 
Size:	52.9 KB 
ID:	40014
    It is extremely rare that two tables would be in a 1-to-2 relationship!


    --------------------------------
    BTW, I saw where you have a suffix of PF - what does PF mean??

  6. #36
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by ssanfu View Post
    June already stated you shouldn't use Look-uo FIELDS (in tables) - here is why: The Evils of Lookup Fields in Tables
    Also see The Ten Commandments of Access


    Why do you have these two tables in a 1-to-1 relationship?
    Click image for larger version. 

Name:	onetwoone1.png 
Views:	15 
Size:	52.9 KB 
ID:	40014
    It is extremely rare that two tables would be in a 1-to-2 relationship!


    --------------------------------
    BTW, I saw where you have a suffix of PF - what does PF mean??
    The Tables Being into a 1-1 relationship and the Primary Foreign Key = PF are from the Tutorials and Following the data models over at Databaseanswers.org as suggested by orange. As I understand all this the 1 - 1 is connected to the PF key being that the Primary Key in tblProjectLineItem is the PrimaryKey of tblInvoiceItems so that all line items of a project can be invoiced?

    Also yes June7 said stop using lookup fields, which I think I did I am only passing the numerical key value from table to table. And plan to lookup when generating reports or queries.....

    If I am super far off in left field please correct me

  7. #37
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by DBID10T View Post
    .... and the Primary Foreign Key = PF.....
    I have never seen anything referred to as a "Primary Foreign Key". Did some searching and cannot find anything resembling a "Primary Foreign Key".


    If two tables are linked in a 1-1 relationship, why not just merge the two tables together?


    I just spent more time looking at the dB "DBDesign4,accdb" - specifically the table "tblInvoiceLineItems".
    Turns out that "tblInvoiceLineItems" is just a junction table. IMHO, a better design would be

    tblInvoiceLineItems ( or jnctInvoiceLineItems)
    ----------------------
    InvoiceLineItem_PK (Autonumber) <<-- add this field
    ProjectLineItemID_FK (Long)
    InvoiceID_FK (Long)



    I also remove the default value of zero from the FK fields.



    -----------------------------------------------------------------------------------------------------------
    I meant to post a link in an earlier post - Microsoft Access Tables: Primary Key Tips and Techniques

  8. #38
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by ssanfu View Post
    I have never seen anything referred to as a "Primary Foreign Key". Did some searching and cannot find anything resembling a "Primary Foreign Key".


    If two tables are linked in a 1-1 relationship, why not just merge the two tables together?



    -----------------------------------------------------------------------------------------------------------
    I meant to post a link in an earlier post - Microsoft Access Tables: Primary Key Tips and Techniques

    Was using this template as a guide. http://www.databaseanswers.org/data_...ices/index.htm


    I think I need to go back and redesign again from the ground up, it's getting to complex if im using highly unusual relationships.

  9. #39
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    http://www.databaseanswers.org/data_...jobs/index.htm

    The PF key One-to-one arrangement is used here also, or am i interpreting these data models incorrectly?

  10. #40
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    1-to-1 relationships are not very common but I do have 1 db where I found this necessary.

    I am not seeing 1-to-1 in that model.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #41
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @DBID10T, that was my bad. I hadn't downloaded the latest dB.
    See post #37 - as June says, it is not a 1-to-1 relationship - it is a junction table.


    PF seems to be something that Barry Williams came up with - I've never seen a FK being set as a PK.
    It is a strange way to create a table, but it seems to work. Again, this is the first time I have seen the nomenclature of PF.

  12. #42
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by ssanfu View Post
    @DBID10T, that was my bad. I hadn't downloaded the latest dB.
    See post #37 - as June says, it is not a 1-to-1 relationship - it is a junction table.


    PF seems to be something that Barry Williams came up with - I've never seen a FK being set as a PK.
    It is a strange way to create a table, but it seems to work. Again, this is the first time I have seen the nomenclature of PF.
    Ok good to know thanks very much.

  13. #43
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by June7 View Post
    Advise not to build lookups in table. Build comboboxes and listboxes on forms.

    HEy June7 or anyone can you clarify this, I think this is what I am struggling with now, I have been using PK_FK to transfer just the IDNumber from the first coloum which results in records that are a series of keys, can I instead pass on the associated word to the other table and store that?

    Am I understanding this right?

  14. #44
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Advise not to build lookups in tables. Still store ID but do it through combobox or listbox on form.

    Yes, if you really want to save other value that can be done. But query links should be done with designated primary key field and related foreign key. If you want to save 'associated word' then this should be designated as primary key in table it comes from.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #45
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Got it thanks June7

Page 3 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help please, total newbie in the deep dish
    By Peanutdust in forum Reports
    Replies: 2
    Last Post: 09-14-2017, 05:35 AM
  2. Newbie getting stuck with summing queries
    By snorky in forum Access
    Replies: 2
    Last Post: 01-19-2012, 03:42 PM
  3. stuck on expression for total query records
    By pacer31 in forum Queries
    Replies: 3
    Last Post: 07-04-2011, 11:30 AM
  4. Newbie Here & Stuck
    By FOZILD in forum Access
    Replies: 5
    Last Post: 09-24-2009, 08:26 AM
  5. :?: newbie...grand total?
    By PnerraD in forum Access
    Replies: 1
    Last Post: 08-24-2006, 06:28 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