I am creating a database for my small consultancy - around 30 clients.

In addition to the table containing names, addresses, etc. I want a table to
record invoices raised - dates, amounts, date paid, etc.

I'm assuming i make the Client ID field the primary key in the contacts
table. How do I use this to auto-complete the client firstname and surname
into the invoice table? In other words - I want to just be able to enter the
client ID into the invoice table and have it enter the firstname and surname
automatically.

Thanks in advance.


Steve

On Mon, 28 May 2007 11:17:30 GMT, "StevePlym" <(E-Mail Removed)> wrote:

>I am creating a database for my small consultancy - around 30 clients.
>
>In addition to the table containing names, addresses, etc. I want a table to
>record invoices raised - dates, amounts, date paid, etc.
>
>I'm assuming i make the Client ID field the primary key in the contacts
>table. How do I use this to auto-complete the client firstname and surname
>into the invoice table? In other words - I want to just be able to enter the
>client ID into the invoice table and have it enter the firstname and surname
>automatically.

You should ONLY store the ClientID in your Invoice table, and NOT the FirstName, LastName, etc ... otherwise, what
happens when you realize you've spelled the client's name incorrectly? You'd then have to go back through the tables and
correct all misspellings ...

Access/Jet is a relational database system; tables should store information that's relevant to itself; for example, an
Invoice table would perhaps store the InvoiceDate, InvoiceAmount, ShippingMethod, Terms etc etc ... those are
"properties" of an Invoice, and would rightly belong in that table, but the client's FirstName and LastName are NOT
properties of the invoice ... they are relevant to the invoice (otherwise how would you know which client the invoice
"belonged" to), but they don't "describe" the invoice, they instead "describe" the Customer, so they should be stored in
the Customer/Client table.

So you'd maintain your setup as you have it - store the ClientID in the Invoice table when a new invoice in generated.
If you need to see the information as a whole (i.e. both Invoice and Client info) then build a query which returns that
information.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

OK Scott, that's all understood and accepted.

But what if I want the Invoice table to display the client name rather than
looking through the Client table? or to query that table for a surname?

There must be a way of getting the Invoice table to link and display the
name/ I know it can be done in Corel Paradox as a friend of mine has
attempted to convert me away from Access!

I'm guessing Access is equally up to the task?


Steve
"Scott McDaniel" <scott@NoSpam_Infotrakker.com> wrote in message
news:(E-Mail Removed)...
> On Mon, 28 May 2007 11:17:30 GMT, "StevePlym" <(E-Mail Removed)> wrote:
>
>>I am creating a database for my small consultancy - around 30 clients.
>>
>>In addition to the table containing names, addresses, etc. I want a table
>>to
>>record invoices raised - dates, amounts, date paid, etc.
>>
>>I'm assuming i make the Client ID field the primary key in the contacts
>>table. How do I use this to auto-complete the client firstname and surname
>>into the invoice table? In other words - I want to just be able to enter
>>the
>>client ID into the invoice table and have it enter the firstname and
>>surname
>>automatically.
>
> You should ONLY store the ClientID in your Invoice table, and NOT the
> FirstName, LastName, etc ... otherwise, what
> happens when you realize you've spelled the client's name incorrectly?
> You'd then have to go back through the tables and
> correct all misspellings ...
>
> Access/Jet is a relational database system; tables should store
> information that's relevant to itself; for example, an
> Invoice table would perhaps store the InvoiceDate, InvoiceAmount,
> ShippingMethod, Terms etc etc ... those are
> "properties" of an Invoice, and would rightly belong in that table, but
> the client's FirstName and LastName are NOT
> properties of the invoice ... they are relevant to the invoice (otherwise
> how would you know which client the invoice
> "belonged" to), but they don't "describe" the invoice, they instead
> "describe" the Customer, so they should be stored in
> the Customer/Client table.
>
> So you'd maintain your setup as you have it - store the ClientID in the
> Invoice table when a new invoice in generated.
> If you need to see the information as a whole (i.e. both Invoice and
> Client info) then build a query which returns that
> information.
>
> Scott McDaniel
> scott@takemeout_infotrakker.com
> www.infotrakker.com

On Mon, 28 May 2007 17:09:58 GMT, "StevePlym" <(E-Mail Removed)> wrote:

>OK Scott, that's all understood and accepted.
>
>But what if I want the Invoice table to display the client name rather than
>looking through the Client table? or to query that table for a surname?
>
>There must be a way of getting the Invoice table to link and display the
>name/ I know it can be done in Corel Paradox as a friend of mine has
>attempted to convert me away from Access!

Ideally, you should never use the actual Table (i.e. the Datasheet view of the TAble) to enter or update data. Instead,
you'd use a Form and set the form to show a Datasheet view or a Continuous view, both of which can mimic the Table view.

If you do this, then your form can be based on a query that pulls together your Invoice table and your Client/Customer
table; from there, you can display whatever values you wish (including the Customer/Client name, etc). The first step
would be to build a query that returns the information you want, then build a form based on that query.

You'd also use this query to search for Invoices related to a particular Client name ...


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

OK, things are becoming a little clearer.

In Paradox I seem to recall my friend linking the two tables, thus showing
the client table details in the Invoice table.

As I'm very new to Access I've a feeling it's going to take me a while to
get my head around all this. Any further guidance will be appreciated and
I'll be rreading the huge book I've bought to help me find my way around the
whole of Office 2007.


Steve
"Scott McDaniel" <scott@NoSpam_Infotrakker.com> wrote in message
news:(E-Mail Removed)...
> On Mon, 28 May 2007 17:09:58 GMT, "StevePlym" <(E-Mail Removed)> wrote:
>
>>OK Scott, that's all understood and accepted.
>>
>>But what if I want the Invoice table to display the client name rather
>>than
>>looking through the Client table? or to query that table for a surname?
>>
>>There must be a way of getting the Invoice table to link and display the
>>name/ I know it can be done in Corel Paradox as a friend of mine has
>>attempted to convert me away from Access!
>
> Ideally, you should never use the actual Table (i.e. the Datasheet view of
> the TAble) to enter or update data. Instead,
> you'd use a Form and set the form to show a Datasheet view or a Continuous
> view, both of which can mimic the Table view.
>
> If you do this, then your form can be based on a query that pulls together
> your Invoice table and your Client/Customer
> table; from there, you can display whatever values you wish (including the
> Customer/Client name, etc). The first step
> would be to build a query that returns the information you want, then
> build a form based on that query.
>
> You'd also use this query to search for Invoices related to a particular
> Client name ...
>
>
> Scott McDaniel
> scott@takemeout_infotrakker.com
> www.infotrakker.com

On Mon, 28 May 2007 21:29:20 GMT, "StevePlym" <(E-Mail Removed)> wrote:

>In Paradox I seem to recall my friend linking the two tables, thus showing
>the client table details in the Invoice table.

Well, Paradox and Access are both capable database environments... but they
are DIFFERENT database environments. Treating Access as if it were a flawed
implementation of Paradox will be frustrating!

In Access, you *can* use table datasheets for data editing; with Lookup
Fields, you can even put "lookups" (e.g. to the Clients table) into the table
datasheet.

But in practice, this buys you very little benefit, and has many costs. See
http://www.mvps.org/access/lookupfields.htm for a critique. Table datasheets
have *very* limited functionality, and you're much better off just using
Tables as data repositories, and creating Forms to do all interaction with the
data in the tables. on the form, you can very easily put controls such as
Combo Boxes or Listboxes to display (say) client data on the same form with
invoice data.

John W. Vinson [MVP]

Thank you John.

I've looked at the Listbox idea for getting data from the Client table into
the Invoice table but I was really looking for something which would fill in
the data without further intervention from me.

I take what you say about paradox - that's why I'm trying to build this from
scratch using Access. It's getting very frustrating though!


Steve
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Mon, 28 May 2007 21:29:20 GMT, "StevePlym" <(E-Mail Removed)> wrote:
>
>>In Paradox I seem to recall my friend linking the two tables, thus showing
>>the client table details in the Invoice table.
>
> Well, Paradox and Access are both capable database environments... but
> they
> are DIFFERENT database environments. Treating Access as if it were a
> flawed
> implementation of Paradox will be frustrating!
>
> In Access, you *can* use table datasheets for data editing; with Lookup
> Fields, you can even put "lookups" (e.g. to the Clients table) into the
> table
> datasheet.
>
> But in practice, this buys you very little benefit, and has many costs.
> See
> http://www.mvps.org/access/lookupfields.htm for a critique. Table
> datasheets
> have *very* limited functionality, and you're much better off just using
> Tables as data repositories, and creating Forms to do all interaction with
> the
> data in the tables. on the form, you can very easily put controls such as
> Combo Boxes or Listboxes to display (say) client data on the same form
> with
> invoice data.
>
> John W. Vinson [MVP]

Have you written a query which joins client table to the invoice
table?

Have you tried creating a form based on the client table and another
form based on the invoice table and then making the invoice form a
subform in the client form?

The when you search a client, you see all their invoices!

Regards,
Tom Bizannes
Microsoft Access Development
Sydney,Australia

On Tue, 29 May 2007 06:33:16 GMT, "StevePlym" <(E-Mail Removed)> wrote:

>Thank you John.
>
>I've looked at the Listbox idea for getting data from the Client table into
>the Invoice table but I was really looking for something which would fill in
>the data without further intervention from me.

Well... that's where you're making the mistake. The Invoice table should
contain the ClientID, *AND NOTHING ELSE*.

Relational databases use the "Grandmother's Pantry Principle" - "a place - ONE
place! - for everything, everything in its place". Information about clients
should be stored in the Clients table; the Invoices table should have the
ClientID. You can *display* other data from the Client table on the form. One
way is to have a Combo Box on the form bound to the ClientID field, but
displaying the client name. The Combo's Row Source query can include other
fields from the client table (address, phone, ...); you can put textboxes on
the form with control sources like

=comboboxname.Column(n)

where n is the zero based index of the desired field.

Again... don't expect the Invoice table to display *anything* from the client
table. It won't, and it shouldn't.

John W. Vinson [MVP]

This sounds like it's getting way too complicated for someone like me!

I'm not a programmer and just want to be able to design a form which can be
seen on screen showing a list of invoices. Each form should display invoice
number, client ID - along with their name, which I guess will come from the
Cleint table - invoice amount, date issued, date paid and the number of days
between issue and payment.

I'm slowly getting to grips with designing the form - although even that
brings some challenges to my brain.

I shall persevere
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news(E-Mail Removed)...
> On Tue, 29 May 2007 06:33:16 GMT, "StevePlym" <(E-Mail Removed)> wrote:
>
>>Thank you John.
>>
>>I've looked at the Listbox idea for getting data from the Client table
>>into
>>the Invoice table but I was really looking for something which would fill
>>in
>>the data without further intervention from me.
>
> Well... that's where you're making the mistake. The Invoice table should
> contain the ClientID, *AND NOTHING ELSE*.
>
> Relational databases use the "Grandmother's Pantry Principle" - "a place -
> ONE
> place! - for everything, everything in its place". Information about
> clients
> should be stored in the Clients table; the Invoices table should have the
> ClientID. You can *display* other data from the Client table on the form.
> One
> way is to have a Combo Box on the form bound to the ClientID field, but
> displaying the client name. The Combo's Row Source query can include other
> fields from the client table (address, phone, ...); you can put textboxes
> on
> the form with control sources like
>
> =comboboxname.Column(n)
>
> where n is the zero based index of the desired field.
>
> Again... don't expect the Invoice table to display *anything* from the
> client
> table. It won't, and it shouldn't.
>
> John W. Vinson [MVP]