Access Query

[imtmub]

Hi,
I m developing the new database for Monthly Fasion Magazines(3type s-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=Ma lls, 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]![VenueIdCombobox NameHere]

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_AfterUpd ate()
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];