make a query some particular fields

arnelgp said:
it meabs my form variable is incorrect, substitute with correct one. else you may post your db for the last time.
Click to expand...

hi i attached my data base

thanks
 

Attachments

arnelgp said:
it meabs my form variable is incorrect, substitute with correct one. else you may post yo
ur db for the last time.
Click to expand...


i deleted my old customersearchform and my customersearchquerytable . and pasted your codes in new query SQL form. there also an error. pls refer attach JPEG

i think according to my relationship with two tables (refer 11 JPEG) there are two Tour_e_Escursioni
 

Attachments

  • 10.jpg
    10.jpg
    94.4 KB · Views: 80
  • 11.jpg
    11.jpg
    64.5 KB · Views: 79
I haven't looked at your db but the error shown in your first screenshot is easily solved. You just need to specify one of the 2 tables for that field in your query.

Also just to point out for the third time about the )) bracket error which you haven't corrected
 
ridders said:
I haven't looked at your db but the error shown in your first screenshot is easily solved. You just need to specify one of the 2 tables for that field in your query.

Also just to point out for the third time about the )) bracket error which you haven't corrected
Click to expand...

can you pls modify the correct codes for me.
thanks. so i can create a brand new query instead of modifying the old one

and i modified the bracket error also after that i got an error.


SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE (DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
(([Nome] Like "*" & [Forms]![CustomerSearchF]!txtNome & "*") Or
([Cognome] Like "*" & [Forms]![CustomerSearchF]!txtCognome & "*") Or
([Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") Or
([Tour_Operator] Like "*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*") Or
([Cadice_Operatore] Like "*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*")) Or
([Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*");
 
Last edited:
ridders said:
I haven't looked at your db but the error shown in your first screenshot is easily solved. You just need to specify one of the 2 tables for that field in your query.

Also just to point out for the third time about the )) bracket error which you haven't corrected
Click to expand...

main table contains :

ClienteID
Data
Nome
Cognome
Paese
Lingua
Nave
Durata
Pick_Up
Orario
Codice_Operatore
Telefono
Posta_Electtronica
Tour_Operator
Tour_e_Escursioni
Adulti
Ragazzi
Bambini
Adulti_Totale
Ragazzi_Totale
Bambini_Totale
Quota_Totale
Commissioni
Commissione Euro
Netto
Note

secondry table cointains :

PacchettoID
Tour_e_Escursioni
Adulto_€
Ragazzo_€
Bambino_€
Codice

what I wanted is, when user selects Tour_e_Escursioni in data entry form, i want automatically fill Adulto_€,Ragazzo_€,Bambino_€,Codice fields. so i choose this relationship between tables.

thanks
 
Hi dhanu

Sorry I haven't got time to look at your db today & anyway I don't want to 'tread on arnel's toes'

So the following may or may not be correct
Changes made (IN RED):
1. I've added the table name on the last line
2. I've corrected the bracketing on the last 2 lines

If you still get errors, it will need to wait for arnel or someone else to look at your db

Code: 
SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE (DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And 
(([Nome] Like "*" & [Forms]![CustomerSearchF]!txtNome & "*") Or 
([Cognome] Like "*" & [Forms]![CustomerSearchF]!txtCognome & "*") Or 
([Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") Or 
([Tour_Operator] Like "*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*") Or 
([Cadice_Operatore] Like "*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*"[COLOR="red"])[/COLOR] Or 
([COLOR="Red"][PacchettiT.].[/COLOR][Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"[COLOR="red"]))[/COLOR];

Good luck
 
ridders said:
Hi dhanu

Sorry I haven't got time to look at your db today & anyway I don't want to 'tread on arnel's toes'

So the following may or may not be correct
Changes made (IN RED):
1. I've added the table name on the last line
2. I've corrected the bracketing on the last 2 lines

If you still get errors, it will need to wait for arnel or someone else to look at your db

Code: 
SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE (DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And 
(([Nome] Like "*" & [Forms]![CustomerSearchF]!txtNome & "*") Or 
([Cognome] Like "*" & [Forms]![CustomerSearchF]!txtCognome & "*") Or 
([Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") Or 
([Tour_Operator] Like "*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*") Or 
([Cadice_Operatore] Like "*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*"[COLOR="red"])[/COLOR] Or 
([COLOR="Red"][PacchettiT.].[/COLOR][Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"[COLOR="red"]))[/COLOR];

Good luck
Click to expand...


dear sir,

i tried the codes given you. there is an error. pls refer attached JPEG.

and thank you very much for looking on this matter.
 

Attachments

  • 12.jpg
    12.jpg
    93.6 KB · Views: 80
Apologies - extra . crept in by mistake

Try again:

Code: 
SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE (DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And 
(([Nome] Like "*" & [Forms]![CustomerSearchF]!txtNome & "*") Or 
([Cognome] Like "*" & [Forms]![CustomerSearchF]!txtCognome & "*") Or 
([Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") Or 
([Tour_Operator] Like "*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*") Or 
([Cadice_Operatore] Like "*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*") Or 
([COLOR="Red"][PacchettiT][/COLOR].[Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"));

Hopefully its OK now but if not, see previous comments
 
ridders said:
Apologies - extra . crept in by mistake

Try again:

Code: 
SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE (DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And 
(([Nome] Like "*" & [Forms]![CustomerSearchF]!txtNome & "*") Or 
([Cognome] Like "*" & [Forms]![CustomerSearchF]!txtCognome & "*") Or 
([Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") Or 
([Tour_Operator] Like "*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*") Or 
([Cadice_Operatore] Like "*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*") Or 
([COLOR="Red"][PacchettiT][/COLOR].[Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"));

Hopefully its OK now but if not, see previous comments
Click to expand...


yes, it seems ok now. but we have another problem now. what is enter parameter value? pls refer JPEG
thanks.
 

Attachments

  • 13.jpg
    13.jpg
    94.4 KB · Views: 84
arnelgp said:
here try this
Click to expand...


hi,,

thank you very much. i tryied to run query using search by name. it runs but it dosent show me the results.only empty query.

i attached a printscreen too.

thanks.
 

Attachments

  • 14.jpg
    14.jpg
    75.2 KB · Views: 90
arnelgp said:
i hope this is the final
Click to expand...

hi arnegp,

sorry to bother you always.

query form only works with date. other criteria no. it gives me only empty query result.

thanks
 
Dear all, thank you very much for helping me on this matter. I thought to describe you all my needful to understand better.
I’m working for a tourism company. So, we need a database to keep track our customers.
I want a table which includes customer all details. ClienteID , Data (date) , Nome (name) , Cognome (sure name ), Paese (country) , Lingua(language) , Nave , Durata (duration) , Pick_Up, Orario (time) , Codice_Operatore (operator code), Telefono (phone no), Posta_Electtronica (email) , Tour_Operator , Tour_e_Escursioni (Tour and Excursions), Adulti (adults), Ragazzi(teenagers), Bambini (kids) , Adulti_Totale ( adults total ) , Ragazzi_Totale (teenagers total) , Bambini_Totale ( kids total ), Quota_Totale ( total price) , Commissioni (commission ) , Commissione Euro ( commission euro) , Netto (net total ) , Note
I created a form to enter above details to the database. Which includes calculated Fields also. But you people advised me to remove these felids, so I attached them to a query.
What I wanted is, when the user clicks on Tour_e_Escursioni field, auto fill Adulto_€, Ragazzo_€, Bambino_€, Codice (code related to package) fields. so, I created another table and selected looked up wizard on Tour_e_Escursioni field on table one. (Picture 03)
Now I want these things to do from my table,
01. I want to save calculated detail somewhere because we need to run report end of the month. (calculated fields are - Adulti_Totale, Ragazzi_Totale, Bambini_Totale, Quota_Totale, Netto.)
Note: I need to apply a commission also. commission will be 25%, 20%, 0%

E.g.: Tour_e_Escursioni: ALCANTARA_TAORMINA package ,

Adulto euro 20 participants 02 total 40
Ragazzo euro 15 participants 02 total 30
Bambino euro 00 participants 01 total 00
Quota_Totale 70
Comissione 20%
Commissione euro 14 euro
Netto 56 euro
(Pls refer picture 01)



02. I need to make a mask (form) to run a report or query (if report is more better because I need to print) it should be including these criterias to search options - Data di inizio (start date), Data di fine (end date), Nome (name), Cognome (sure name), Paese (country), Tour e Escursioni (Tour and Excursions), Tour Operator, Codice Operatore (operator code)
(picture 02)

I think you all got an idea now. above all steps, I did from watching YouTube tutorials. I don’t have any further knowledge about access.
Thank you.
 

Attachments

  • 1.jpg
    1.jpg
    41.2 KB · Views: 94
  • 2.jpg
    2.jpg
    96.6 KB · Views: 84
  • 3.jpg
    3.jpg
    94.2 KB · Views: 93
arnelgp said:
finally? is over.
Click to expand...

woooooowwwwww grande arnelgp it is working finely. thank you so much. thumbs up for your work. I cant do this without you guys.

thank you very much everyone, who spent their time to work on this.
 
ridders said:
No I don't think so....

Dhanu appears to have 2 new questions as a follow up ... or have you also answered those? :)
Click to expand...

this is more than enough for me :) now I want is, I want to run a report and get printed.
 
You must log in or register to reply here.

Similar threads

C
Crosstab query that uses Form field as criteria
Replies
14
Views
920
Ken Sheridan
Ken Sheridan
B
Help with formatting decimal places in calculated query field
Replies
7
Views
335
Ken Sheridan
Ken Sheridan
M
Solved Selecting data from a drop down box in one field, enters a value in a 2nd field
Replies
12
Views
319
XPS35
XPS35
N
Query to show records EXCEPT those with matching fields
Replies
8
Views
504
nortonm
N
A
Error 3828 when creating a CSV from a query
2 3
Replies
42
Views
795
andymartin3186
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom