A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Concatenatd fields in a query for a searching form



 
 
Thread Tools Display Modes
  #1  
Old October 17th, 2004, 10:40 AM
Marc
external usenet poster
 
Posts: n/a
Default Concatenatd fields in a query for a searching form

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




  #2  
Old October 17th, 2004, 12:42 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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

  #3  
Old October 17th, 2004, 03:33 PM
Marc
external usenet poster
 
Posts: n/a
Default

"Jeff Boyce" -DISCARD_HYPHEN_TO_END ha scritto nel
messaggio ...
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






  #4  
Old October 17th, 2004, 06:47 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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.

--
More info, please ...

Jeff Boyce
Access MVP

  #5  
Old October 17th, 2004, 07:59 PM
Marc
external usenet poster
 
Posts: n/a
Default

[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 a

[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







  #6  
Old October 18th, 2004, 01:13 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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

  #7  
Old October 18th, 2004, 07:32 PM
Marc
external usenet poster
 
Posts: n/a
Default

"Jeff Boyce" -DISCARD_HYPHEN_TO_END ha scritto nel
messaggio ...
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


  #8  
Old October 19th, 2004, 04:25 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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?

--
Good luck

Jeff Boyce
Access MVP

  #9  
Old October 19th, 2004, 08:49 PM
Marc
external usenet poster
 
Posts: n/a
Default

"Jeff Boyce" -DISCARD_HYPHEN_TO_END ha scritto nel
messaggio ...
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?

--
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Text form fields Jerry General Discussion 5 August 24th, 2004 10:43 PM
insert query with fields from a form doug General Discussion 6 August 20th, 2004 05:41 PM
Query to join records form 2 databases bdehning General Discussion 5 August 9th, 2004 03:09 PM
surely a form with a ListBox can be used in a query? 1.156 Running & Setting Up Queries 14 June 2nd, 2004 04:54 PM
Filter by Form query will not populate fields Brigitte P Running & Setting Up Queries 2 May 27th, 2004 12:59 PM


All times are GMT +1. The time now is 04:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.