Autonumber using alphanumerics

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,
I received some good advice relating to having an alphanumeric reference
number, which I have been able to create and it works well in a query. Thanks
to those who took the time to give me the advice. However, the data that I am
able to obtain from the query is not displayed in the underlying table or
form.

The Table has 3 fields relative to the query; "ClientID Number", a 3 digit
autonumber, "ClientID Name", a 3 digit manually created alpha figure, and
"ClientRef", which displays a combination of both these data to display the
alphanumeric figure that I need. The query draws on the information from the
"Corporate Client Details" Table, where all related fields are found.

In the underlying table I tried to use the lookup wizard to recall the data
from the query for display in the table/form, but when doing so got the
message "no valid fields can be found in 'Corporate Client Details Query'.
You may have selected a query that uses the table your adding the lookup
column to. Please select a new source".

I guess the error message is self-explanatory, but I'm wondering how I can
get around this problem. Off the top of my head it seemed appropriate to then
create another table which holds the alpha and the numeric fields, along with
the client name, then only keep the ClientRef, which is a combination of the
two fields in the "Corporate Client Details" Table. Upon doing so, I was
still unable to recall the data from the query using the lookup wizard. Is
there a way around this?

The other problem that I have is that I want the alpha figures in the
ClientRef to appear in uppercase. I have enabled this to happen in the table,
but it does not in the query when I combine the two fields together. Does
this matter that it doesn't appear in the query, or is it that once I manage
to combine the "ClientID Number" and "ClientID Name" in a single field I can
then set it to view in uppercase?

Thanks in advance
Katharine
 
I'm not sure I understand, but I suspect you are working directly in the
table, rather than via queries and forms. Access (JET) tables store data,
forms (and reports) display data.

A search through this newsgroup (tablesdbdesign) will reveal a strong
consensus against using lookup data type fields in your table definitions.
The more commonly recommended approach is to create independent tables
holding your lookup values, and to use queries to return those values to
combo boxes in forms.

Good luck

Jeff Boyce
<Access MVP>
 
Hi Jeff,

Thanks for the prompt response. I've tried what you have recommended, and
it's not quite how I want the form to function. What I want the form to do is
to recall the data from the "ClientID Number" and "ClientID Name" fields and
display them as one field on the form relative to the client data being
recalled, as opposed to having a combo box which lists the various options
available to me. So as I scroll though the client listing it will display the
"ClientRef" relative to the client I am looking at at any particular time.

I hope this is a little clearer for you this time. If you have any other
suggestions I would be most grateful.

Thanks in advance
Katharine
 
If the fields are being combined successfully in the query, base the form on
the query and add to the form a text box bound to the query field. If
ClientID Number is unique to the client then it is all you need for a
primary key field; however, you can use a combined-field PK if necessary.
If you scroll through the client records (on the form, not the table) you
will see the combined fields from the query if you followed the suggestion
in the first sentence.
If you are working directly with records in a table, stop doing that. Forms
are how you interact with the data. If you have successfully combined
fields in a query it is possible to view the query in datasheet view, where
you can see the combined number directly next to the Client name, but again,
this is not the best way to interact with data. You may be trying to treat
a table or query as a spreadsheet. They may look similar, but they work
very, very differently.
Combining fields may be thought of as a sort of calculation. Calculations
are not stored except in rare and specific instances (which do not apply in
your case), but are instead figured "on the fly."
 
Hi Bruce

Thank you so much for your response. I have done as suggested and it works
perfectly.

Ever grateful
Katharine
 
I wonder if you can help with another problem thats reared it head. in order
to test how the database is working I only have 2 records entered. The on the
form and query it has created a duplicate of each record with the wrong
ClientRef for each record, so for example 301PET is peterInc, 302JON is
jonInc, which is there, but it shows another record as 301PIN as jonInc, and
302JON as peterInc. Any idea what has happened here? In the intial table that
I based my form on originally this didn't happen.

Thanks in advance
Katharine
 
Are there additional records in your table, or just the original two? I'm
not exactly sure what is going on, but I suspect there is a problem with
your calculation that combines the two fields. Is your query based on the
table? If so, in query design view click in the top of an empty column and
type:
CombinedID: [ClientID Number] & [ClientID Name]

Be sure to use the correct field names, including spaces, underscores, etc.
Switch to datasheet view. If it looks OK, use the query as the form's
record source. Click View > Field List, and drag CombinedID onto the form.
This should be all that's needed.

By the way, you referred to ClientID Number as a three-digit autonumber.
Are you sure about that? Is it listed as autonumber in table design view?
If it is a number you have created, is it listed at Indexed (bottom left in
table design view) as Yes (no duplicates)? If not, it needs to be.

Without knowing more about your database it is impossible to say. You will
need to post your table structure and relationships, and the SQL from your
query.

To post the table structure, so something like this:

tblClient
ClientID_Number (primary key)
ClientID_Name
Phone, etc.

Do the same for other tables that are involved in the problem. To get the
SQL for the query (the script that is going on behind the scenes), click
View > SQL View. Copy what you see and paste it into your reply.
 
Hi Bruce

I only have 2 records in my table, no others. My calculation combining the
two fields looks to be correct; it appears like this:

Client Ref: [ClientID Number] & [ClientID Name]

and yes, the query is based on the table, and I clicked on a blank field and
entered the calculation you suggested and the problem remains when I switch
to datasheet view. I still have a duplicate copy of each record with the
wrong Client Ref.

The ClientID Number is an autonumber and listed as such in design view, its
field size is long integer, new values set to increment, and indexed as Yes
(No Duplicates).

The 2 tables that I have used to create the query are Corporate Client
Details and Corporate Reference, the latter holding the ClientID Number and
ClientID Name fields that the query is based on. Presently I have these 2
tables, although the database will draw on information from 2 other tables,
Active Payments and Closed Payments. I have not included these tables as they
are yet to be completed and no relationships have been made to the first 2
tables at this stage.

tblCorporate Reference
ClientID Number (Primary key, and Automunber)
ClientID Name
Corporate Name
Division

tblCorporate Client Details
Client Ref (the combined ClientID Number and ClientID Name does not
appear
here in datasheet view, hence the reason the form is
based on the
query and no relationship established)
Organisation Name
Branch
Title1
Surname1
First Name1
Telephone1
Fax1
Mobile1
Email1
Title2
Surname2
First Name2
Telephone2
Fax2
Mobile2
Email2
Address
Town
County
Post Code
Web Address
Notes

Below is the SQL for the query.

SELECT [ClientID Number] & [ClientID Name] AS [Client Ref], [Corporate
Client Details].[Organisation Name], [Corporate Client Details].Branch,
[Corporate Client Details].Title1, [Corporate Client Details].Surname1,
[Corporate Client Details].[First Name1], [Corporate Client
Details].Telephone1, [Corporate Client Details].Fax1, [Corporate Client
Details].Mobile1, [Corporate Client Details].Email1, [Corporate Client
Details].Title2, [Corporate Client Details].Surname2, [Corporate Client
Details].[First Name2], [Corporate Client Details].Telephone2, [Corporate
Client Details].Fax2, [Corporate Client Details].Mobile2, [Corporate Client
Details].Email2, [Corporate Client Details].Address, [Corporate Client
Details].Town, [Corporate Client Details].County, [Corporate Client
Details].[Post Code], [Corporate Client Details].[Web Address], [Corporate
Client Details].Notes
FROM [Corporate Client Details], [Corporate Reference];

Maybe you can see what I have failed to and advise me as to what the
underlying problem is.

Thanks in advance
Katharine

BruceM said:
Are there additional records in your table, or just the original two? I'm
not exactly sure what is going on, but I suspect there is a problem with
your calculation that combines the two fields. Is your query based on the
table? If so, in query design view click in the top of an empty column and
type:
CombinedID: [ClientID Number] & [ClientID Name]

Be sure to use the correct field names, including spaces, underscores, etc.
Switch to datasheet view. If it looks OK, use the query as the form's
record source. Click View > Field List, and drag CombinedID onto the form.
This should be all that's needed.

By the way, you referred to ClientID Number as a three-digit autonumber.
Are you sure about that? Is it listed as autonumber in table design view?
If it is a number you have created, is it listed at Indexed (bottom left in
table design view) as Yes (no duplicates)? If not, it needs to be.

Without knowing more about your database it is impossible to say. You will
need to post your table structure and relationships, and the SQL from your
query.

To post the table structure, so something like this:

tblClient
ClientID_Number (primary key)
ClientID_Name
Phone, etc.

Do the same for other tables that are involved in the problem. To get the
SQL for the query (the script that is going on behind the scenes), click
View > SQL View. Copy what you see and paste it into your reply.
Click to expand...
 
See responses inline.

Katharine Jansen said:
Hi Bruce

I only have 2 records in my table, no others. My calculation combining the
two fields looks to be correct; it appears like this:

Client Ref: [ClientID Number] & [ClientID Name]
Click to expand...

Are you attempting to perform this calculation in tblCorporate Client
Details? If so, delete the field. Combining two fields is a type of
calculation. Calculations are stored in tables only under the rarest
circumstances, but as far as I know it is not even possible to perform a
calculation in a table.
and yes, the query is based on the table, and I clicked on a blank field
and
entered the calculation you suggested and the problem remains when I
switch
to datasheet view. I still have a duplicate copy of each record with the
wrong Client Ref.

The ClientID Number is an autonumber and listed as such in design view,
its
field size is long integer, new values set to increment, and indexed as
Yes
(No Duplicates).

The 2 tables that I have used to create the query are Corporate Client
Details and Corporate Reference, the latter holding the ClientID Number
and
ClientID Name fields that the query is based on. Presently I have these 2
tables, although the database will draw on information from 2 other
tables,
Active Payments and Closed Payments. I have not included these tables as
they
are yet to be completed and no relationships have been made to the first 2
tables at this stage.

tblCorporate Reference
ClientID Number (Primary key, and Automunber)
ClientID Name
Corporate Name
Division
Click to expand...

Can a corporate client have more than one division? If so, Division would
be a table by itself. It would contain its own primary key (PK), a ClientID
Number as a foreign key (FK) field, and any of the details that are now
contained in tblCorporate Client Details. No big changes are needed.
Rename tblCorporate Client Details tblDivision. Delete ClientRef. Add a PK
field (autonumber is fine) and ClientID Number. It table design view, be
sure that the data type for ClientID Number is Number. Click Tools >
Relationships. Add the two tables (tblCorporate Reference and tblDivision).
Drag ClientID Number form one table, and drop it on ClientID Number in the
other table. Click Enforce Referential Integrity. Close the Relationships
window.

Now make a query based on tblCorporate Reference. Add the calculated field
I suggested to combine the two fields. Now build a form based on that
query, and another form based on tblDivision. In form design view, drag the
icon for tblDivision onto tblCorporateReference.

Similarly, if each Division could have several branches, build a Branch
table linked to tblDivision just as tblDivision is linked to tblCorporate
Reference. The forms would work similarly as in the example above.

The approach is the same whether or not the company has multiple Divisions
or Branches. If a company has one Division you can call it Home Office or
something, and allow for the possibility they will add a Division in the
future. Same with Branches.
tblCorporate Client Details
Client Ref (the combined ClientID Number and ClientID Name does not
appear here in datasheet view, hence the reason the form is
based on the query and no relationship established)
Organisation Name
Branch
Title1
Surname1
First Name1
Telephone1
Fax1
Mobile1
Email1
Title2
Surname2
First Name2
Telephone2
Fax2
Mobile2
Email2
Address
Town
County
Post Code
Web Address
Notes

Below is the SQL for the query.

SELECT [ClientID Number] & [ClientID Name] AS [Client Ref], [Corporate
Client Details].[Organisation Name], [Corporate Client Details].Branch,
[Corporate Client Details].Title1, [Corporate Client Details].Surname1,
[Corporate Client Details].[First Name1], [Corporate Client
Details].Telephone1, [Corporate Client Details].Fax1, [Corporate Client
Details].Mobile1, [Corporate Client Details].Email1, [Corporate Client
Details].Title2, [Corporate Client Details].Surname2, [Corporate Client
Details].[First Name2], [Corporate Client Details].Telephone2, [Corporate
Client Details].Fax2, [Corporate Client Details].Mobile2, [Corporate
Client
Details].Email2, [Corporate Client Details].Address, [Corporate Client
Details].Town, [Corporate Client Details].County, [Corporate Client
Details].[Post Code], [Corporate Client Details].[Web Address], [Corporate
Client Details].Notes
FROM [Corporate Client Details], [Corporate Reference];
Click to expand...

It seems that Client Ref is a table field, as mentioned earlier. Combine
the fields in the query, and do not store them in a table.

I'm guessing a bit about what you are trying to accomplish, but the general
principles behind table relationships apply no matter the situation. A good
rule for table design is that you should be able to describe a table's
purpose in one sentence without using the word "and". Having said that,
don't get too literal. Personal information can include address and phone
number, but not personal information and employment history, for instance.
In your case, Company and Divisions are related, but do not belong together
(if my assumption is correct about a company having several branches).
Maybe you can see what I have failed to and advise me as to what the
underlying problem is.

Thanks in advance
Katharine

BruceM said:
Are there additional records in your table, or just the original two?
I'm
not exactly sure what is going on, but I suspect there is a problem with
your calculation that combines the two fields. Is your query based on
the
table? If so, in query design view click in the top of an empty column
and
type:
CombinedID: [ClientID Number] & [ClientID Name]

Be sure to use the correct field names, including spaces, underscores,
etc.
Switch to datasheet view. If it looks OK, use the query as the form's
record source. Click View > Field List, and drag CombinedID onto the
form.
This should be all that's needed.

By the way, you referred to ClientID Number as a three-digit autonumber.
Are you sure about that? Is it listed as autonumber in table design
view?
If it is a number you have created, is it listed at Indexed (bottom left
in
table design view) as Yes (no duplicates)? If not, it needs to be.

Without knowing more about your database it is impossible to say. You
will
need to post your table structure and relationships, and the SQL from
your
query.

To post the table structure, so something like this:

tblClient
ClientID_Number (primary key)
ClientID_Name
Phone, etc.

Do the same for other tables that are involved in the problem. To get
the
SQL for the query (the script that is going on behind the scenes), click
View > SQL View. Copy what you see and paste it into your reply.
Click to expand...
Click to expand...
 
Hi Bruce

The calculation that I did was in the query. Sorry I didn't make this clear
to you.

You also asked if the corporate client can have more than one division, the
answer is yes. However, not all corporate clients will have divisions,
therefore I have created a test database with three tables holding the
corporate client information; tblCorporate Client Name, which holds the
ClientID Number and ClientID Name; tblDivision, holding the DivisionRef (if
applicable) relative to the corporate client; and tblCorporate Details,
holding the clients contact details.

I have formed a relationship with the field ClientID Ref in both the
tblCorporate Clent Name and tblDivision in the test database which works well
in datasheet view, however, I have created a query using the three tables
which I want to use to test the functionality of a form that would follow,
and the query will not work. The error message that comes up is "The
specified field '[ClientID Ref]' could refer to more than one table listed in
the FROM clause of your SQL statement". Although I understand why the error
message appears, the 'ClientID Ref' field is what the relationship between
the two tables is based on, therefore I am uncertain as to how to overcome
this problem and pursue further. Is there a way I can reword the calculation
to get the query to run, or is it a little less straightforward than that?

Thanks in advance

Katharine

BruceM said:
See responses inline.

Katharine Jansen said:
Hi Bruce

I only have 2 records in my table, no others. My calculation combining the
two fields looks to be correct; it appears like this:

Client Ref: [ClientID Number] & [ClientID Name]
Click to expand...

Are you attempting to perform this calculation in tblCorporate Client
Details? If so, delete the field. Combining two fields is a type of
calculation. Calculations are stored in tables only under the rarest
circumstances, but as far as I know it is not even possible to perform a
calculation in a table.
and yes, the query is based on the table, and I clicked on a blank field
and
entered the calculation you suggested and the problem remains when I
switch
to datasheet view. I still have a duplicate copy of each record with the
wrong Client Ref.

The ClientID Number is an autonumber and listed as such in design view,
its
field size is long integer, new values set to increment, and indexed as
Yes
(No Duplicates).

The 2 tables that I have used to create the query are Corporate Client
Details and Corporate Reference, the latter holding the ClientID Number
and
ClientID Name fields that the query is based on. Presently I have these 2
tables, although the database will draw on information from 2 other
tables,
Active Payments and Closed Payments. I have not included these tables as
they
are yet to be completed and no relationships have been made to the first 2
tables at this stage.

tblCorporate Reference
ClientID Number (Primary key, and Automunber)
ClientID Name
Corporate Name
Division
Click to expand...

Can a corporate client have more than one division? If so, Division would
be a table by itself. It would contain its own primary key (PK), a ClientID
Number as a foreign key (FK) field, and any of the details that are now
contained in tblCorporate Client Details. No big changes are needed.
Rename tblCorporate Client Details tblDivision. Delete ClientRef. Add a PK
field (autonumber is fine) and ClientID Number. It table design view, be
sure that the data type for ClientID Number is Number. Click Tools >
Relationships. Add the two tables (tblCorporate Reference and tblDivision).
Drag ClientID Number form one table, and drop it on ClientID Number in the
other table. Click Enforce Referential Integrity. Close the Relationships
window.

Now make a query based on tblCorporate Reference. Add the calculated field
I suggested to combine the two fields. Now build a form based on that
query, and another form based on tblDivision. In form design view, drag the
icon for tblDivision onto tblCorporateReference.

Similarly, if each Division could have several branches, build a Branch
table linked to tblDivision just as tblDivision is linked to tblCorporate
Reference. The forms would work similarly as in the example above.

The approach is the same whether or not the company has multiple Divisions
or Branches. If a company has one Division you can call it Home Office or
something, and allow for the possibility they will add a Division in the
future. Same with Branches.
tblCorporate Client Details
Client Ref (the combined ClientID Number and ClientID Name does not
appear here in datasheet view, hence the reason the form is
based on the query and no relationship established)
Organisation Name
Branch
Title1
Surname1
First Name1
Telephone1
Fax1
Mobile1
Email1
Title2
Surname2
First Name2
Telephone2
Fax2
Mobile2
Email2
Address
Town
County
Post Code
Web Address
Notes

Below is the SQL for the query.

SELECT [ClientID Number] & [ClientID Name] AS [Client Ref], [Corporate
Client Details].[Organisation Name], [Corporate Client Details].Branch,
[Corporate Client Details].Title1, [Corporate Client Details].Surname1,
[Corporate Client Details].[First Name1], [Corporate Client
Details].Telephone1, [Corporate Client Details].Fax1, [Corporate Client
Details].Mobile1, [Corporate Client Details].Email1, [Corporate Client
Details].Title2, [Corporate Client Details].Surname2, [Corporate Client
Details].[First Name2], [Corporate Client Details].Telephone2, [Corporate
Client Details].Fax2, [Corporate Client Details].Mobile2, [Corporate
Client
Details].Email2, [Corporate Client Details].Address, [Corporate Client
Details].Town, [Corporate Client Details].County, [Corporate Client
Details].[Post Code], [Corporate Client Details].[Web Address], [Corporate
Client Details].Notes
FROM [Corporate Client Details], [Corporate Reference];
Click to expand...

It seems that Client Ref is a table field, as mentioned earlier. Combine
the fields in the query, and do not store them in a table.

I'm guessing a bit about what you are trying to accomplish, but the general
principles behind table relationships apply no matter the situation. A good
rule for table design is that you should be able to describe a table's
purpose in one sentence without using the word "and". Having said that,
don't get too literal. Personal information can include address and phone
number, but not personal information and employment history, for instance.
In your case, Company and Divisions are related, but do not belong together
(if my assumption is correct about a company having several branches).
Maybe you can see what I have failed to and advise me as to what the
underlying problem is.

Thanks in advance
Katharine

BruceM said:
Are there additional records in your table, or just the original two?
I'm
not exactly sure what is going on, but I suspect there is a problem with
your calculation that combines the two fields. Is your query based on
the
table? If so, in query design view click in the top of an empty column
and
type:
CombinedID: [ClientID Number] & [ClientID Name]

Be sure to use the correct field names, including spaces, underscores,
etc.
Switch to datasheet view. If it looks OK, use the query as the form's
record source. Click View > Field List, and drag CombinedID onto the
form.
This should be all that's needed.

By the way, you referred to ClientID Number as a three-digit autonumber.
Are you sure about that? Is it listed as autonumber in table design
view?
If it is a number you have created, is it listed at Indexed (bottom left
in
table design view) as Yes (no duplicates)? If not, it needs to be.

Without knowing more about your database it is impossible to say. You
will
need to post your table structure and relationships, and the SQL from
your
query.

To post the table structure, so something like this:

tblClient
ClientID_Number (primary key)
ClientID_Name
Phone, etc.

Do the same for other tables that are involved in the problem. To get
the
SQL for the query (the script that is going on behind the scenes), click
View > SQL View. Copy what you see and paste it into your reply.
Click to expand...
Click to expand...
Click to expand...
 
Where did DivisionRef come from? In what relationships does it participate?
Is it a primary key?
From what I can understand, ClientID Ref is a field in two tables. That is
probably why the query does not work.
What is the purpose of tblDivision? I understand that a company may or may
not have divisions, but what is stored in that table? I assume that each
division would have its own address and contact information. How do you
relate an address to a division?
Just guessing a bit, but try this: Keep your tblCorporate Client Name and
your tblCorporate Details, except add ClientID Number as a foreign key field
in tblCorporate Details, and an optional field for describing if the details
are for a divsion of the company. Establish a relationship between ClientID
Number in the two tables containing the field. Click Enforce Referential
Ingegrity. Build a form and subform as previously described.

Katharine Jansen said:
Hi Bruce

The calculation that I did was in the query. Sorry I didn't make this
clear
to you.

You also asked if the corporate client can have more than one division,
the
answer is yes. However, not all corporate clients will have divisions,
therefore I have created a test database with three tables holding the
corporate client information; tblCorporate Client Name, which holds the
ClientID Number and ClientID Name; tblDivision, holding the DivisionRef
(if
applicable) relative to the corporate client; and tblCorporate Details,
holding the clients contact details.

I have formed a relationship with the field ClientID Ref in both the
tblCorporate Clent Name and tblDivision in the test database which works
well
in datasheet view, however, I have created a query using the three tables
which I want to use to test the functionality of a form that would follow,
and the query will not work. The error message that comes up is "The
specified field '[ClientID Ref]' could refer to more than one table listed
in
the FROM clause of your SQL statement". Although I understand why the
error
message appears, the 'ClientID Ref' field is what the relationship between
the two tables is based on, therefore I am uncertain as to how to overcome
this problem and pursue further. Is there a way I can reword the
calculation
to get the query to run, or is it a little less straightforward than that?

Thanks in advance

Katharine

BruceM said:
See responses inline.

Katharine Jansen said:
Hi Bruce

I only have 2 records in my table, no others. My calculation combining
the
two fields looks to be correct; it appears like this:

Client Ref: [ClientID Number] & [ClientID Name]
Click to expand...

Are you attempting to perform this calculation in tblCorporate Client
Details? If so, delete the field. Combining two fields is a type of
calculation. Calculations are stored in tables only under the rarest
circumstances, but as far as I know it is not even possible to perform a
calculation in a table.
and yes, the query is based on the table, and I clicked on a blank
field
and
entered the calculation you suggested and the problem remains when I
switch
to datasheet view. I still have a duplicate copy of each record with
the
wrong Client Ref.

The ClientID Number is an autonumber and listed as such in design view,
its
field size is long integer, new values set to increment, and indexed as
Yes
(No Duplicates).

The 2 tables that I have used to create the query are Corporate Client
Details and Corporate Reference, the latter holding the ClientID Number
and
ClientID Name fields that the query is based on. Presently I have these
2
tables, although the database will draw on information from 2 other
tables,
Active Payments and Closed Payments. I have not included these tables
as
they
are yet to be completed and no relationships have been made to the
first 2
tables at this stage.

tblCorporate Reference
ClientID Number (Primary key, and Automunber)
ClientID Name
Corporate Name
Division
Click to expand...

Can a corporate client have more than one division? If so, Division
would
be a table by itself. It would contain its own primary key (PK), a
ClientID
Number as a foreign key (FK) field, and any of the details that are now
contained in tblCorporate Client Details. No big changes are needed.
Rename tblCorporate Client Details tblDivision. Delete ClientRef. Add a
PK
field (autonumber is fine) and ClientID Number. It table design view, be
sure that the data type for ClientID Number is Number. Click Tools >
Relationships. Add the two tables (tblCorporate Reference and
tblDivision).
Drag ClientID Number form one table, and drop it on ClientID Number in
the
other table. Click Enforce Referential Integrity. Close the
Relationships
window.

Now make a query based on tblCorporate Reference. Add the calculated
field
I suggested to combine the two fields. Now build a form based on that
query, and another form based on tblDivision. In form design view, drag
the
icon for tblDivision onto tblCorporateReference.

Similarly, if each Division could have several branches, build a Branch
table linked to tblDivision just as tblDivision is linked to tblCorporate
Reference. The forms would work similarly as in the example above.

The approach is the same whether or not the company has multiple
Divisions
or Branches. If a company has one Division you can call it Home Office
or
something, and allow for the possibility they will add a Division in the
future. Same with Branches.
tblCorporate Client Details
Client Ref (the combined ClientID Number and ClientID Name does not
appear here in datasheet view, hence the reason the form is
based on the query and no relationship established)
Organisation Name
Branch
Title1
Surname1
First Name1
Telephone1
Fax1
Mobile1
Email1
Title2
Surname2
First Name2
Telephone2
Fax2
Mobile2
Email2
Address
Town
County
Post Code
Web Address
Notes

Below is the SQL for the query.

SELECT [ClientID Number] & [ClientID Name] AS [Client Ref], [Corporate
Client Details].[Organisation Name], [Corporate Client Details].Branch,
[Corporate Client Details].Title1, [Corporate Client Details].Surname1,
[Corporate Client Details].[First Name1], [Corporate Client
Details].Telephone1, [Corporate Client Details].Fax1, [Corporate Client
Details].Mobile1, [Corporate Client Details].Email1, [Corporate Client
Details].Title2, [Corporate Client Details].Surname2, [Corporate Client
Details].[First Name2], [Corporate Client Details].Telephone2,
[Corporate
Client Details].Fax2, [Corporate Client Details].Mobile2, [Corporate
Client
Details].Email2, [Corporate Client Details].Address, [Corporate Client
Details].Town, [Corporate Client Details].County, [Corporate Client
Details].[Post Code], [Corporate Client Details].[Web Address],
[Corporate
Client Details].Notes
FROM [Corporate Client Details], [Corporate Reference];
Click to expand...

It seems that Client Ref is a table field, as mentioned earlier. Combine
the fields in the query, and do not store them in a table.

I'm guessing a bit about what you are trying to accomplish, but the
general
principles behind table relationships apply no matter the situation. A
good
rule for table design is that you should be able to describe a table's
purpose in one sentence without using the word "and". Having said that,
don't get too literal. Personal information can include address and
phone
number, but not personal information and employment history, for
instance.
In your case, Company and Divisions are related, but do not belong
together
(if my assumption is correct about a company having several branches).
Maybe you can see what I have failed to and advise me as to what the
underlying problem is.

Thanks in advance
Katharine

:



Are there additional records in your table, or just the original two?
I'm
not exactly sure what is going on, but I suspect there is a problem
with
your calculation that combines the two fields. Is your query based on
the
table? If so, in query design view click in the top of an empty
column
and
type:
CombinedID: [ClientID Number] & [ClientID Name]

Be sure to use the correct field names, including spaces, underscores,
etc.
Switch to datasheet view. If it looks OK, use the query as the form's
record source. Click View > Field List, and drag CombinedID onto the
form.
This should be all that's needed.

By the way, you referred to ClientID Number as a three-digit
autonumber.
Are you sure about that? Is it listed as autonumber in table design
view?
If it is a number you have created, is it listed at Indexed (bottom
left
in
table design view) as Yes (no duplicates)? If not, it needs to be.

Without knowing more about your database it is impossible to say. You
will
need to post your table structure and relationships, and the SQL from
your
query.

To post the table structure, so something like this:

tblClient
ClientID_Number (primary key)
ClientID_Name
Phone, etc.

Do the same for other tables that are involved in the problem. To get
the
SQL for the query (the script that is going on behind the scenes),
click
View > SQL View. Copy what you see and paste it into your reply.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top