Concatenatd fields in a query for a searching form

[Marc]

Dear All,

I have a Data Entry Form which have several fields, five of them are
Sample1, Sample2, Sample3, Sample4, Sample5. They are separeted because each
sample have different Custom Tariff Number.

I built also a second form, used for tracking (findind) the samples sent, in
this form I have a field for [Internal Code], [Customer] and [Country].

I would like to improve this form inserting a sample field, as well.

My question is:
in a query can I concatenate the five samples fields in one column only,
such as:
Sample: [Sample1] & " " & [Sample2] & " " & [Sample3] & " " & [Sample4] & "
" & [Sample5]

having the criteria row the following statement:
Like ("*" & [Forms]![frmFind]![Sample] & "*")

I made some trials, but had negative results.

Thank you very much for your help,

Marc

 

[Jeff Boyce]

Marc

I have no idea what "negative results" you got ... it would be helpful in
diagnosing what didn't work.

If you have multiple fields (by the way, tables have fields, forms have
controls, possibly bound to table fields) in your underlying table, with
names like "Sample1", "Sample2", ..., there's a good chance your data design
would benefit from further normalization. The use of repeating field names
like that is fairly common ... in spreadsheets!

Access is a relational database, and you don't want to store data the same
way you would in a spreadsheet. "Why not?", you ask? For one thing, it
makes doing the kind of query you've posed much more difficult!

Even if each sample has an associated CustomTariffNumber, you can create a
table something like:

tblWhateverYouAreWorkingWith
WhateverID
Sample (whatever about the sample you are measuring)
CustomTariffNumber

With a design similar to this, you only look one place for a sample.

 

[Marc]

Marc

I have no idea what "negative results" you got ... it would be helpful in
diagnosing what didn't work.

If you have multiple fields (by the way, tables have fields, forms have
controls, possibly bound to table fields) in your underlying table, with
names like "Sample1", "Sample2", ..., there's a good chance your data design
would benefit from further normalization. The use of repeating field names
like that is fairly common ... in spreadsheets!

Access is a relational database, and you don't want to store data the same
way you would in a spreadsheet. "Why not?", you ask? For one thing, it
makes doing the kind of query you've posed much more difficult!

Even if each sample has an associated CustomTariffNumber, you can create a
table something like:

tblWhateverYouAreWorkingWith
WhateverID
Sample (whatever about the sample you are measuring)
CustomTariffNumber

With a design similar to this, you only look one place for a sample.

--
Good luck

Jeff Boyce
<Access MVP>

Thank you for you prompt answer Mr. Boyce,
I built the database day after day starting from a draft, trying to improve
it in every point. You are right, when you say it needs a further
normalization, because the samples is the last section to adjust.

The data entry table is the main table, but there are also the Continent,
Country, Customer, Packing and Courrier tables. In the Data Entry Form, with
a combobox I select the continent, then in a second one I can choose only
the countries present in that continet, afterwards in the third combobox I
can see only the customers present in that country. Selecting the customer
the address autofills in dedicated controlls and so on.

We have more than 400 products and building a right database, I should store
them in a table named Samples, like you also suggested me.

Sometimes customers requires more than one product, so in the data entry
form (also table) I need about 5 sample fields with its custom tariff number
field and I do not know how to solve this point, taking also into
consideration that the final user unfortunately is not disposed for the
computer, least of all access.

However, solving this problem, how can I concatenate more fields in only a
column in a query in order to use it in a form for tracking the samples
sent?

Any advices are welcomed.

Kind regards,

Marc

 

[Jeff Boyce]

Marc

Please re-read my response. I should have been more explicit in
recommending that you modify your data structure BEFORE trying to find a way
to display it in a form, or to query it.

From your description, I still am having trouble visualizing your table
structure. In a relational database, everything starts with the data (i.e.,
table structure). Everything else (forms, queries, reports, ...) are based
on that structure.

If you provide more information about the structure, the 'group readers may
be able to offer additional ideas/suggestions.

 

[Marc]

[Cut]

Jeff, thank you in ansering me and for you frankness. The database is
composed with 6 tables. In the satellite tables
(continent, country, customer, packing, courrier) I insert *only one time*
all the data available and then in the DataEntry Form these data are
filtered
and recalled by comboboxes.

The tables are:

[tblContinent]
idContinent - text (primary key)
Continent - text

[tblCountry]
idcountry - text (primary key)
country -text
eu (europeaunion) - boolean field

[tblCustomer]
idcustomer - counter (primary key)
customer - text
address1 - text
address2 - text
address3 - text
address4 - text
fao - text

[tblPacking]
IdPacking - counter (primary key)
Packing - text

[tblCourrier]
IdCourrier - Counter (primary key)
Courrier - - text


Main Table

[tblDataEntry]
ProformaInvoiceN° - Text (Counter and Primary Key)
ShippingDate - Date/hour
OurCode - Text
Reference - Text
Courrier - Text
AirWayBill - Text
N°Packing - Text
Packing - Text
GrossWeight - Text
NetWeight - Text
Dimension - Text
Cost - Text
Sample1 - Text
CustomTariffN°1 - Text
Sample2 - Text
CustomTariffN°2 - Text
Sample3 - Text
CustomTariffN°3 - Text
Sample4 - Text
CustomTariffN°4 - Text
Sample5 - Text
CustomTariffN°5 - Text
Continent - Text
Country - Text
EU (EuropeanUnion) - Text
Customer - Text
Address1 - Text
Address2 - Text
Address3 - Text
Address4 - Text
Fao - Text


Hope this can be usefull for you.
Thank in advance and kind regards,
Marc

 

[Jeff Boyce]

Marc

I'll reiterate my earlier suggestion. Your design with repeating fields
(field groups - SampleN, CustomTariffN) is not well normalized. Removing
this information to a "child" table would allow you to better use Access'
functions and features.

One of the clues to the "one-to-many" nature of this relationship is in your
post wherein you stated that a customer (one) could have more than one
sample, with its related CustomTariff (many).

I also noted that you have a table with Customer information (apparently
name, address and fao), but that you also repeat those fields in your
tblDataEntry. Again, considering relational database design, this redundant
data storage is both unnecessary and it creates a situation in which you
must ensure the synchronization of the data between the two tables. If you
keep this design, you will need to handle the situation in which a
name/address/fao of a customer is changed in one table, but not the other --
now, which one is correct?! (the better design has this information
recorded only once, making that one instance the correct version)

I also noted that your tblDataEntry held NONE of the primary keys from the
related tables, only their text values. This presents the same issue
discussed above for the customer.

If you wish to further normalize your data structure, consider reading more
on the topic. The reason for having the related tables is so that you can
store the ID of the related table's value in your "one" table, not the text
of that related table.

 

[Marc]

Marc

I'll reiterate my earlier suggestion. Your design with repeating fields
(field groups - SampleN, CustomTariffN) is not well normalized. Removing
this information to a "child" table would allow you to better use Access'
functions and features.

One of the clues to the "one-to-many" nature of this relationship is in your
post wherein you stated that a customer (one) could have more than one
sample, with its related CustomTariff (many).

I also noted that you have a table with Customer information (apparently
name, address and fao), but that you also repeat those fields in your
tblDataEntry. Again, considering relational database design, this redundant
data storage is both unnecessary and it creates a situation in which you
must ensure the synchronization of the data between the two tables. If you
keep this design, you will need to handle the situation in which a
name/address/fao of a customer is changed in one table, but not the other --
now, which one is correct?! (the better design has this information
recorded only once, making that one instance the correct version)

I also noted that your tblDataEntry held NONE of the primary keys from the
related tables, only their text values. This presents the same issue
discussed above for the customer.

If you wish to further normalize your data structure, consider reading more
on the topic. The reason for having the related tables is so that you can
store the ID of the related table's value in your "one" table, not the text
of that related table.

--
Good luck

Jeff Boyce
<Access MVP>

Thank you for your advices Jeff, I will try to focus how better normalize
the tables, since I like the perfect things. Nevertheless, there is still
something that I don't understand.
If I make a related Customers table with a Samples one, how can I manage all
this in a form? Using a subform? And all the other fields, such Shipping
Date, Continent, Country, Courrier, AWB, Reference, GrossWeight, and so on
.... using an other subform?

I am focusing a form with 2 subforms, but I am not yet sure how to design
all this. Moreover, I would like to have a whole simple form, since the
final user has no aptitude for the computer and I am afraid that all this
would be too complicated for she.

Anyway, thank you again for your interest.
Kind regards
Marc

 

[Jeff Boyce]

Marc

(see in-line comments)

Thank you for your advices Jeff, I will try to focus how better normalize
the tables, since I like the perfect things. Nevertheless, there is still
something that I don't understand.
If I make a related Customers table with a Samples one, how can I manage all
this in a form? Using a subform?

If you have a customer with multiple samples, a common approach is to put
the customer information in a main form, and the related samples information
in a subform.

And all the other fields, such Shipping
Date, Continent, Country, Courrier, AWB, Reference, GrossWeight, and so on
... using an other subform?

If I understand your data structure, there are only ONE of each of these per
customer, so you wouldn't need to use a subform. These data elements, based
on your customer table design, only happen once.

There is one thing that I'm still puzzled about, however. Based on your
design, it appears a given customer has one and only one ... ?Order? I
can't tell how the samples relate, but I'm wondering if you are dealing with
a situation analogous to one with multiple customers, and each customer can
have multiple orders, and each customer's order can have multiple ...
?samples?

 

[Marc]

Marc

(see in-line comments)

If you have a customer with multiple samples, a common approach is to put
the customer information in a main form, and the related samples information
in a subform.


If I understand your data structure, there are only ONE of each of these per
customer, so you wouldn't need to use a subform. These data elements, based
on your customer table design, only happen once.

There is one thing that I'm still puzzled about, however. Based on your
design, it appears a given customer has one and only one ... ?Order? I
can't tell how the samples relate, but I'm wondering if you are dealing with
a situation analogous to one with multiple customers, and each customer can
have multiple orders, and each customer's order can have multiple ...
?samples?

--
Good luck

Jeff Boyce
<Access MVP>

Thank you Jeff for your answer, I explored the Northwind database and I saw
something of similar to my realty, even if it doesn't completely fit it. Do
you mind if I send you my masterpiece, for having a good knowledge of my
mistake?
I should like to solve this problem, because it could be a good experience,
since I can learn from my mistakes, but you should only explore my database
not adjust it.

As to your questions, for instance, this week, a customer can ask some
samples and next month he can require further different ones, so I should
have a relation one (customer) to many (samples) ....

Let me know something if you are interested in looking at my database.
Kind regards,
Marc