Thanks Mate all of your advice has been spot on
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.
LineItemAgreedPrice on my tblProjectLineItem
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.
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
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?
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?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?
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??
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
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
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.
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?
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.
@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.@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.
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?
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.
Got it thanks June7