Access Query

[imtmub]

Hi,
I m developing the new database for Monthly Fasion Magazines(3types-To Cover both genders and all the ages) distrubuting freely to the malls, Hotels, Offices etc., The magazines are distributed copies 3 different time with in the month(bcz to circulate whole month and available to people all the time). We have lots of venue(The places for Distribution=Malls, Hotels, Office, etc.,).

Now currently we are maintaining the data by excel sheet for every distribution. Every month has 3 worksheet(bcz 3 times distribution per month) and every month we are creating new file. And i transfered all the sheet to the tables by the times distribution and months.

Now My question is I want to create a query to total how much we distributed to individual venue in one month and also for few months by type of magazines and by the venue.

Imthiyaz

[Killer42]

...Now My question is I want to create a query to total how much we distributed to individual venue in one month and also for few months by type of magazines and by the venue.
Imthiyaz

It sounds as though the actual queries will be relatively simple. But could you post details of how your table(s) is/are laid out?

[imtmub]

It sounds as though the actual queries will be relatively simple. But could you post details of how your table(s) is/are laid out?

I have nine tables in my Database. Everymonth has 3 tables(Bcz 3 time Distribution to the same venue(i.e- Venue/Customer). Now i have data for 3 Months. Bcz every 3 month we need to submit for audit.
The Table as the fields like Venue Id, Venue Name, Address, 1ds,1gw,1ml(1ds,1gw,1ml are 3 types of magazines), Total(all3) and another table contains same fields except 2ds,2gw,2ml(It means 2 nd distribution of the magazine). Like wise for 3 rd distribution.

Now i want to total how much we distributed in the month as well as for 3 month to the single Venue. Bcz we need for auditing as i said before.

I hope u understand and expecting ur answer.

Imthiyaz

[imtmub]

I have nine tables in my Database. Everymonth has 3 tables(Bcz 3 time Distribution to the same venue(i.e- Venue/Customer). Now i have data for 3 Months. Bcz every 3 month we need to submit for audit.
The Table as the fields like Venue Id, Venue Name, Address, 1ds,1gw,1ml(1ds,1gw,1ml are 3 types of magazines), Total(all3) and another table contains same fields except 2ds,2gw,2ml(It means 2 nd distribution of the magazine). Like wise for 3 rd distribution.

Now i want to total how much we distributed in the month as well as for 3 month to the single Venue. Bcz we need for auditing as i said before.

I hope u understand and expecting ur answer.

Imthiyaz

Anybody there to help me. Hello Mr.Killer did u understand my explanation about DB. If u want more info i will provide u.

[MMcCarthy]

I would suggest you start by creating a union query something like this using January Februay and March as the months and call it qryDistribution:

 
SELECT "January" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table1
UNION
SELECT "January" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table2
UNION
SELECT "January" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table3
UNION
SELECT "February" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table4
UNION
SELECT "February" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table5
UNION
SELECT "February" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table6
UNION
SELECT "March" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table7
UNION
SELECT "March" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table8
UNION
SELECT "March" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM Table9;
 
 

Then create two new queries based on this union query:

To get the Monthly distribution figures:

 
SELECT MonthName, [Venue Id], [Venue Name], Sum(AmtDS) As TotalDS, Sum(AmtGW) As TotalGW, Sum(AmtML) As TotalML, Sum(AmtDist) As TotalDist
FROM qryDistribution
GROUP BY MonthName, [Venue Id], [Venue Name];
 
 

To get the Total distribution figures:

 
SELECT [Venue Id], [Venue Name], Sum(AmtDS) As TotalDS, Sum(AmtGW) As TotalGW, Sum(AmtML) As TotalML, Sum(AmtDist) As TotalDist
FROM qryDistribution
GROUP BY [Venue Id], [Venue Name];
 
 

[imtmub]

I created the union query and as well as 2 query for monthly and total distribution and Thanks for ur support to develop the database.

And i have a form to find that contain a combo box. From that i can select Venue Id to find the result related to the Venue Id. I know to link the combo box even procedure to Select query(Normal query). But i don;t know for the union and Sql statements.

Imthiyaz

[imtmub]

I created the union query and as well as 2 query for monthly and total distribution and Thanks for ur support to develop the database.

And i have a form to find that contain a combo box. From that i can select Venue Id to find the result related to the Venue Id. I know to link the combo box even procedure to Select query(Normal query). But i don;t know for the union and Sql statements.

Imthiyaz

And I got a new problem. I want a result For the 3 month for the perticular venue Id Total Distribution all 3 and by the individual total for the each magazine. But the query result is giving the total of all the venue.

Imthiyaz

[imtmub]

And I got a new problem. I want a result For the 3 month for the perticular venue Id Total Distribution all 3 and by the individual total for the each magazine. But the query result is giving the total of all the venue.

Imthiyaz

Hello mmccarthy,
Can u give me some solution to rid out of this problem. I m waiting for ur answer.

Thanks in Advance

Imthiyaz

[imtmub]

Hello mmccarthy,
Can u give me some solution to rid out of this problem. I m waiting for ur answer.

Thanks in Advance

Imthiyaz

Pls somebody help me

[MMcCarthy]

And I got a new problem. I want a result For the 3 month for the perticular venue Id Total Distribution all 3 and by the individual total for the each magazine. But the query result is giving the total of all the venue.

Imthiyaz

Expand this query as follows for Totals by Venue:

 
SELECT [Venue Id], [Venue Name], Sum(AmtDS) As TotalDS, Sum(AmtGW) As TotalGW, Sum(AmtML) As TotalML, Sum(AmtDist) As TotalDist
FROM qryDistribution
WHERE [Venue Id]=[Forms]![FormNameHere]![VenueIdComboboxNameHere]
GROUP BY [Venue Id], [Venue Name];
 
 

Expand this query as follows for Monthly figures by Venue:

 
SELECT MonthName, [Venue Id], [Venue Name], Sum(AmtDS) As TotalDS, Sum(AmtGW) As TotalGW, Sum(AmtML) As TotalML, Sum(AmtDist) As TotalDist
FROM qryDistribution
WHERE [Venue Id]=[Venue Id]=[Forms]![FormNameHere]![VenueIdComboboxNameHere]
GROUP BY MonthName, [Venue Id], [Venue Name];
 
 

[imtmub]

Hi,
But i m not getting the result. But it showing the column heading and no other rows for the results.

[MMcCarthy]

Hi,
But i m not getting the result. But it showing the column heading and no other rows for the results.

I think your problem is the value being returned by the combo box.

[Forms]![FormNameHere]![VenueIdComboboxNameHere]

This should retrieve a value equal to the Venue Id...

1. What is the form name?
2. What is the system name of the combo box (if you're not sure go to properties under other tab look at Name property) ?
3. What is the Row Source of the Combo box (found under data tab) ?
4. What is the Bound Column Number (found under data tab) ?
5. Is Venue Id a number or text field?

[imtmub]

This should retrieve a value equal to the Venue Id...

1. What is the form name?
Form name is Search
2. What is the system name of the combo box (if you're not sure go to properties under other tab look at Name property) ?
Combo5
3. What is the Row Source of the Combo box (found under data tab) ?
SELECT [Venue].[VenueId], [Venue].[name_en] FROM Venue; (Bcz this value i m taking from Venue table and the combo showing two fields bcz its required for me to select some time i remember the Id / name )
4. What is the Bound Column Number (found under data tab) ?
Bound column is 1
5. Is Venue Id a number or text field?[/quote]
Is a number

Here my query:
SELECT [Venue Id], [Venue Name], Sum([AmtDS]) AS TotalDS, Sum([AmtGW]) AS TotalGW, Sum([AmtML]) AS TotalML, Sum([AmtDist]) AS TotalDist
FROM qryDistribution
WHERE [Venue Id]=[Forms]![Search]![Combo5]
GROUP BY [Venue Id], [Venue Name];


I don;t know where i m doing mistakes. Still its giving the result for all the venue. If i select perticular from the combo also its asking the question enter parameter value for Venue Id, Venue Name, Total.
here my code:
Private Sub Combo5_AfterUpdate()
DoCmd.OpenQuery "Total Distribution"
Me.Combo5 = ""
End Sub

[MMcCarthy]

SELECT [Venue].[VenueId], [Venue].[name_en] FROM Venue;

Why has VenueId no space here but in query is [Venue Id]?

 
 
SELECT [Venue Id], [Venue Name], Sum([AmtDS]) AS TotalDS, Sum([AmtGW]) AS TotalGW, Sum([AmtML]) AS TotalML, Sum([AmtDist]) AS TotalDist
FROM qryDistribution
WHERE [Venue Id]=[Forms]![Search]![Combo5]
GROUP BY [Venue Id], [Venue Name];
 
 

This is Correct

 
Private Sub Combo5_AfterUpdate()
 
  DoCmd.OpenQuery "Total Distribution"
 
End Sub
 
 

Remove Me.Combo5="" from code as above...

[imtmub]

Why has VenueId no space here but in query is [Venue Id]?
Bcz all the table contains column name called VenueId, So maybe i need to change this code Venue Id(Remove the sapace) is it correct.

 
 
SELECT [Venue Id], [Venue Name], Sum([AmtDS]) AS TotalDS, Sum([AmtGW]) AS TotalGW, Sum([AmtML]) AS TotalML, Sum([AmtDist]) AS TotalDist
FROM qryDistribution
WHERE [Venue Id]=[Forms]![Search]![Combo5]
GROUP BY [Venue Id], [Venue Name];
 
 

[NeoPa]

Yes, the name there should definitely match the name of the field in the record of the table.

[imtmub]

Yes, the name there should definitely match the name of the field in the record of the table.

But i tried Its showing message like this"You tried to execute a query that does not include the specified expression"VenueId" as a part of an aggregate funstion."
But my table contains the field name called VenueId. I don"t know where is the problem. If i include space its giving the total of all Venue. But not specific. Anyother way is there to get the result for specific venue.

[MMcCarthy]

But i tried Its showing message like this"You tried to execute a query that does not include the specified expression"VenueId" as a part of an aggregate funstion."
But my table contains the field name called VenueId. I don"t know where is the problem. If i include space its giving the total of all Venue. But not specific. Anyother way is there to get the result for specific venue.

You have to go back through all the queries and change it. However, I don't understand how the union query was giving the correct results if the field name was wrong.

[NeoPa]

Post in here the exact (new) SQL that is causing the error message as well as the error message itself.
That way we can compare and, more easily, find the (probably small) error in the SQL.

[MMcCarthy]

But i tried Its showing message like this"You tried to execute a query that does not include the specified expression"VenueId" as a part of an aggregate funstion."
But my table contains the field name called VenueId. I don"t know where is the problem. If i include space its giving the total of all Venue. But not specific. Anyother way is there to get the result for specific venue.

You have to include the VenueId in the Group By statement as well

[imtmub]

You have to include the VenueId in the Group By statement as well

This is Old Code:For qryDistribution:
SELECT "September" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM SepOUTPUT_1
UNION
SELECT "September" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM SepOUTPUT_2
UNION
SELECT "September" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM SepOUTPUT_3
UNION
SELECT "October" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM Octshanghai1
UNION
SELECT "October" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM Octshanghai2
UNION SELECT "October" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM Octshanghai3;
-------------
Monthly Distribution
SELECT [MonthName], [Venue Id], [Venue Name], Sum([AmtDS]) AS TotalDS, Sum([AmtGW]) AS TotalGW, Sum([AmtML]) AS TotalML, Sum([AmtDist]) AS TotalDist
FROM qryDistribution
GROUP BY [MonthName], [Venue Id], [Venue Name];
-------------------
Total Distribution:
SELECT [Venue Id], [Venue Name], Sum([AmtDS]) AS TotalDS, Sum([AmtGW]) AS TotalGW, Sum([AmtML]) AS TotalML, Sum([AmtDist]) AS TotalDist
FROM qryDistribution1
WHERE [Venue Id]=[Forms]![Search]![Combo5]
GROUP BY [Venue Id], [Venue Name];
------------------------------------
This codes are giving only total of all the venues. Not by individual menu. If i remove the space in VenueId according to the table field its not giving the result.

[NeoPa]

You need to post the error number and message along with the actual code being used (preferrably within code tags) as otherwise we could be looking for a problem that doesn't exist in the version posted.
I noticed this SQL doesn't seem to be compatible - one refers to qryDistribution while the other refers to qryDistribution1.
This code doesn't seem to be a consistent set.
I checked it through anyway, but couldn't find the error I was expecting from your comments - hence I'm guessing you've included different (incompatible) versions.

[imtmub]

You need to post the error number and message along with the actual code being used (preferrably within code tags) as otherwise we could be looking for a problem that doesn't exist in the version posted.
I noticed this SQL doesn't seem to be compatible - one refers to qryDistribution while the other refers to qryDistribution1.
This code doesn't seem to be a consistent set.
I checked it through anyway, but couldn't find the error I was expecting from your comments - hence I'm guessing you've included different (incompatible) versions.

Its not showing any error no. But its showing error message as i said before. u said i included didfferent versions ( I don;t understand this) which version and where i can check this and give any other suggestion.

[MMcCarthy]

Its not showing any error no. But its showing error message as i said before. u said i included didfferent versions ( I don;t understand this) which version and where i can check this and give any other suggestion.

In the TotalDistribution query you've put

FROM qryDistribution1

instead of

FROM qryDistribution

Why?

And what exactly is the error message saying?

[imtmub]

that i change already qryDistribution. Eventhough the result is same.

[MMcCarthy]

that i change already qryDistribution. Eventhough the result is same.

Change union query to:

SELECT "September" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM SepOUTPUT_1
UNION
SELECT "September" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM SepOUTPUT_2
UNION
SELECT "September" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM SepOUTPUT_3
UNION
SELECT "October" As MonthName, [Venue Id], [Venue Name], Sum([1ds]) As AmtDS, Sum([1gw]) As AmtGW, Sum([1ml]) As AmtML, Sum([Total]) As AmtDist
FROM Octshanghai1
UNION
SELECT "October" As MonthName, [Venue Id], [Venue Name], Sum([2ds]) As AmtDS, Sum([2gw]) As AmtGW, Sum([2ml]) As AmtML, Sum([Total]) As AmtDist
FROM Octshanghai2
UNION SELECT "October" As MonthName, [Venue Id], [Venue Name], Sum([3ds]) As AmtDS, Sum([3gw]) As AmtGW, Sum([3ml]) As AmtML, Sum([Total]) As AmtDist
FROM Octshanghai3
GROUP BY [Venue Id], [Venue Name];

[Killer42]

In the TotalDistribution query you've put
FROM qryDistribution1
instead of
FROM qryDistribution

That sounds like the sort of thing that the relationships or query editor does when you pull in the same table more than once.

[imtmub]

That sounds like the sort of thing that the relationships or query editor does when you pull in the same table more than once.

Still i have the same problem:
here my code:
for the qryDistribution:
SELECT "September" As MonthName, [VenueId], [VenueName_EN], Sum([1DS]) As AmtDS, Sum([1GW]) As AmtGW, Sum([1ML]) As AmtML, Sum([Total]) As AmtDist
FROM September1
UNION SELECT "October" As MonthName, [VenueId], [VenueName_EN], Sum([1DS]) As AmtDS, Sum([1GW]) As AmtGW, Sum([1ML]) As AmtML, Sum([Total]) As AmtDist
FROM October1
GROUP BY [VenueId], [VenueName_EN];

(I reduced this bcz i already put all the data of one month into one table. previous it was 3 table. now i shifted to one)

Code for Monthly Distribution:
SELECT MonthName, [VenueId], [VenueName_EN], Sum(AmtDS) AS TotalDS, Sum(AmtGW) AS TotalGW, Sum(AmtML) AS TotalML, Sum(AmtDist) AS TotalDist
FROM qryDistribution
WHERE [VenueId]=[VenueId]=[Forms]![Search]![Combo0]
GROUP BY MonthName, [VenueId], [VenueName_EN];

When i run monthly distribution query it show the meesage like"you tried to execute a query that doesnot include the specified expression'VenueId' as part of an aggregate function."

[MMcCarthy]

Still i have the same problem:

Did you make the change to the union query I suggested.

Mary

[imtmub]

Did you make the change to the union query I suggested.

Mary

I change in the qryDistribution(Union query). u can c the codes. Now i reduce 6 tables to 2 tables. I hope i did correct. u can have a look on the code in previous post.

[MMcCarthy]

I change in the qryDistribution(Union query). u can c the codes. Now i reduce 6 tables to 2 tables. I hope i did correct. u can have a look on the code in previous post.

I can't really follow what you're doing any more but you had too many [VenueId] in the WHERE clause.

Code for Monthly Distribution:
SELECT MonthName, [VenueId], [VenueName_EN], Sum(AmtDS) AS TotalDS, Sum(AmtGW) AS TotalGW, Sum(AmtML) AS TotalML, Sum(AmtDist) AS TotalDist
FROM qryDistribution
WHERE [VenueId]=[Forms]![Search]![Combo0]
GROUP BY MonthName, [VenueId], [VenueName_EN];

[imtmub]

I can't really follow what you're doing any more but you had too many [VenueId] in the WHERE clause.

Code for Monthly Distribution:
SELECT MonthName, [VenueId], [VenueName_EN], Sum(AmtDS) AS TotalDS, Sum(AmtGW) AS TotalGW, Sum(AmtML) AS TotalML, Sum(AmtDist) AS TotalDist
FROM qryDistribution
WHERE [VenueId]=[Forms]![Search]![Combo0]
GROUP BY MonthName, [VenueId], [VenueName_EN];

Bcz u had given the suggestion to change the code to get the result by venue.