linking data from two tables

[Guest]

I have a simple database which consists of a number of tables which in fact
are links to external .DBF files; several queries which I have used bring
together the tables into three logical groups; and three forms (one for each
query).

One of the forms is a "Customer Record" form which includes contact details,
sales total and targets, etc.

One of the imported tables is a list of sales invoices and I would like to
link the sales invoices to the customer record; perhaps as a subform.

The problem I have is that there is no field in the sales invoice table that
exactly matches a field in the customer record table. In the customer record
table the customer number is represented by a 5 digit alphanuemeric code (for
example A0050) whilst the transaction number in the sales invoice table is
made from a combination of customer account number and invoice number (for
example A0050 001234).

Is it possible to link the two tables by comparing the customer account
number to the first part of the transaction number; so if a transaction
number starts A0050 then link that to customer A0050?

Thanks

David

 

[Joseph Meehan]

I have a simple database which consists of a number of tables which in
fact are links to external .DBF files; several queries which I have
used bring together the tables into three logical groups; and three
forms (one for each query).

One of the forms is a "Customer Record" form which includes contact
details, sales total and targets, etc.

One of the imported tables is a list of sales invoices and I would
like to link the sales invoices to the customer record; perhaps as a
subform.

The problem I have is that there is no field in the sales invoice
table that exactly matches a field in the customer record table. In
the customer record table the customer number is represented by a 5
digit alphanuemeric code (for example A0050) whilst the transaction
number in the sales invoice table is made from a combination of
customer account number and invoice number (for example A0050 001234).

Is it possible to link the two tables by comparing the customer
account number to the first part of the transaction number; so if a
transaction number starts A0050 then link that to customer A0050?

Yes. Will that ID always be the first five characters?

 

[Guest]

Hi Joseph

Thanks for your reply.

In answer to your question; yes, it will always be the first five characters.

Thanks

David

 

[Joseph Meehan]

Hi Joseph

Thanks for your reply.

In answer to your question; yes, it will always be the first five
characters.

Thanks

David

Take a look at this (from the Access Help File)

The following table lists examples of expressions that you can use in
calculated controls on forms, reports, and data access pages.
ExpressionDescription
="N/A"Displays N/A.

=[FirstName]&" "&[LastName]Displays the values of the FirstName and LastName
fields separated by a space.

=Left([ProductName], 1)Uses the Left function to display the first character
of the value of the ProductName field.

=Right([AssetCode], 2)Uses the Right function to display the last 2
characters of the value of the AssetCode field.

=Trim([Address])Uses the Trim function to display the value of the Address
field, removing any leading or trailing spaces.

=IIf(IsNull([Region]),[City]&" "& [PostalCode],[City]&" "&[Region]&" "&
[PostalCode])Uses the IIf function to display the values of the City and
PostalCode fields if Region is Null; otherwise, it displays the values of
the City, Region, and PostalCode fields, separated by spaces.

Notes
In the ControlSource property of a calculated control, precede the
expression with the = operator. On a data access page, you can omit the =
operator, and type an alias instead; for example, type FullName:
[FirstName]&" "& [LastName].

When you set the Name property of a calculated control in a form or report,
or set the ID property of a calculated control in a data access page, make
sure to use a unique name. Don't use the name or ID of one of the controls
that you used in the expression.

In an expression on a form or report, you can use the name of a control or
field in the underlying record source. In an expression on a data access
page, you can use only the name of a field that's in the data definition of
that page.

 

[Guest]

Hi Joseph

Thanks for the information. I used the Left expression and that worked a
treat.

Thanks

David

Hi Joseph

Thanks for your reply.

In answer to your question; yes, it will always be the first five
characters.

Thanks

David

Take a look at this (from the Access Help File)

The following table lists examples of expressions that you can use in
calculated controls on forms, reports, and data access pages.
ExpressionDescription
="N/A"Displays N/A.

=[FirstName]&" "&[LastName]Displays the values of the FirstName and LastName
fields separated by a space.

=Left([ProductName], 1)Uses the Left function to display the first character
of the value of the ProductName field.

=Right([AssetCode], 2)Uses the Right function to display the last 2
characters of the value of the AssetCode field.

=Trim([Address])Uses the Trim function to display the value of the Address
field, removing any leading or trailing spaces.

=IIf(IsNull([Region]),[City]&" "& [PostalCode],[City]&" "&[Region]&" "&
[PostalCode])Uses the IIf function to display the values of the City and
PostalCode fields if Region is Null; otherwise, it displays the values of
the City, Region, and PostalCode fields, separated by spaces.

Notes
In the ControlSource property of a calculated control, precede the
expression with the = operator. On a data access page, you can omit the =
operator, and type an alias instead; for example, type FullName:
[FirstName]&" "& [LastName].

When you set the Name property of a calculated control in a form or report,
or set the ID property of a calculated control in a data access page, make
sure to use a unique name. Don't use the name or ID of one of the controls
that you used in the expression.

In an expression on a form or report, you can use the name of a control or
field in the underlying record source. In an expression on a data access
page, you can use only the name of a field that's in the data definition of
that page.