One query which generate multi report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 12 tables. Each represents a month. Each table contains - Item,
Company A, Company B, Company C, Company D.

I have created a simple query showing - Item, Jan, Feb, Mar.... Dec for
Company A. I would like to have the same for Company B, Company C and Company
D but I do not wish to create a query for each company. Is there a quicker
way, where I can use the same query to shows different companies monthly
figures?

Thanks
 
You really need to look at your table design. It sounds far from normalised,
hence why you are having these problems.

Consider a table called "Companies", a table called "Items" and some sort of
linking table such as CompaniesItems that contains the date . .



"No Professional Training, WilliamH"
 
I have 12 tables. Each represents a month. Each table contains - Item,
Company A, Company B, Company C, Company D.

I have created a simple query showing - Item, Jan, Feb, Mar.... Dec for
Company A. I would like to have the same for Company B, Company C and Company
D but I do not wish to create a query for each company. Is there a quicker
way, where I can use the same query to shows different companies monthly
figures?

Thanks
Click to expand...

As John says, your problem is not so much with the query as with the
totally non-normalized table design. You're "committing spreadsheet" -
a venial sin, punishable by being required to read up on
Normalization. <g>

Storing data in tablenames (months) or fieldnames (companies) is
simply wrong, in the database world. A much better design would be to
have two tables:

Companies
CompanyID
CompanyName
<info about the company as an entity>

Sales <or whatever it is you're storing>
CompanyID <link to Companies
Saledate <e.g. #11/01/2004# for November, you might want multiyears
Value <whatever you now have in your field

A very simple Parameter query will let you pull out the data for any
company for any date:

SELECT Value
FROM Sales INNER JOIN Companies
ON Sales.CompanyID = Companies.CompanyID
WHERE CompanyName LIKE "*" & [Enter part of company name:] & "*"
AND SaleDate BETWEEN [Enter start date:] AND [Enter end date:];

You can construct this in the query grid; just add the two tables,
select the Value, SaleDate and CompanyName fields and put the criteria

LIKE "*" & [Enter part of company name:] & "*"

and

BETWEEN [Enter start date:] AND [Enter end date:]

on the Criteria line under companyname and saledate respectively.

With your current design, this type of query will be difficult or
impossible. At best you would need to write custom VBA code to
construct the SQL of the query dynamically.

John W. Vinson[MVP]

John W. Vinson[MVP]
 
I import those Monthly data (Sales by Company) from Excel worksheet. I
thought that I could use Access to manipulate them to show each company sales
trend.

Anyway, thanks a lot. And Merry Christmas & Happy New Year.


John Vinson said:
I have 12 tables. Each represents a month. Each table contains - Item,
Company A, Company B, Company C, Company D.

I have created a simple query showing - Item, Jan, Feb, Mar.... Dec for
Company A. I would like to have the same for Company B, Company C and Company
D but I do not wish to create a query for each company. Is there a quicker
way, where I can use the same query to shows different companies monthly
figures?

Thanks
Click to expand...

As John says, your problem is not so much with the query as with the
totally non-normalized table design. You're "committing spreadsheet" -
a venial sin, punishable by being required to read up on
Normalization. <g>

Storing data in tablenames (months) or fieldnames (companies) is
simply wrong, in the database world. A much better design would be to
have two tables:

Companies
CompanyID
CompanyName
<info about the company as an entity>

Sales <or whatever it is you're storing>
CompanyID <link to Companies
Saledate <e.g. #11/01/2004# for November, you might want multiyears
Value <whatever you now have in your field

A very simple Parameter query will let you pull out the data for any
company for any date:

SELECT Value
FROM Sales INNER JOIN Companies
ON Sales.CompanyID = Companies.CompanyID
WHERE CompanyName LIKE "*" & [Enter part of company name:] & "*"
AND SaleDate BETWEEN [Enter start date:] AND [Enter end date:];

You can construct this in the query grid; just add the two tables,
select the Value, SaleDate and CompanyName fields and put the criteria

LIKE "*" & [Enter part of company name:] & "*"

and

BETWEEN [Enter start date:] AND [Enter end date:]

on the Criteria line under companyname and saledate respectively.

With your current design, this type of query will be difficult or
impossible. At best you would need to write custom VBA code to
construct the SQL of the query dynamically.

John W. Vinson[MVP]

John W. Vinson[MVP]
Click to expand...
 
William

John, and John, have both described the issue with using a
less-than-well-normalized data design in Access. Your response appears to
be

"but that's how I get it (from Excel)..."

Access has some very strong tools you can use to modify poorly-normalized
data (Monthly Sales by Company, from Excel), and convert it into a
highly-usable database. Not only would you be able to show trends by
company, but you could compare years, or categorize companies and compare
among companies.

To do this, consider the following process:
Import data into a "temporary" import table
Run one/more append queries against the import table data, normalizing
Click to expand...
the data on its way into your "permanent" table
Repeat as needed!
Click to expand...

Your post came across something like "I am doing this, why doesn't it work?"

Another question you could pose is "I'd like to be able to xxxxx, how could
I approach that?"

Best of luck!

Jeff Boyce
<Access MVP>

"No Professional Training, WilliamH"
I import those Monthly data (Sales by Company) from Excel worksheet. I
thought that I could use Access to manipulate them to show each company sales
trend.

Anyway, thanks a lot. And Merry Christmas & Happy New Year.


John Vinson said:
I have 12 tables. Each represents a month. Each table contains - Item,
Company A, Company B, Company C, Company D.

I have created a simple query showing - Item, Jan, Feb, Mar.... Dec for
Company A. I would like to have the same for Company B, Company C and Company
D but I do not wish to create a query for each company. Is there a quicker
way, where I can use the same query to shows different companies monthly
figures?

Thanks
Click to expand...

As John says, your problem is not so much with the query as with the
totally non-normalized table design. You're "committing spreadsheet" -
a venial sin, punishable by being required to read up on
Normalization. <g>

Storing data in tablenames (months) or fieldnames (companies) is
simply wrong, in the database world. A much better design would be to
have two tables:

Companies
CompanyID
CompanyName
<info about the company as an entity>

Sales <or whatever it is you're storing>
CompanyID <link to Companies
Saledate <e.g. #11/01/2004# for November, you might want multiyears
Value <whatever you now have in your field

A very simple Parameter query will let you pull out the data for any
company for any date:

SELECT Value
FROM Sales INNER JOIN Companies
ON Sales.CompanyID = Companies.CompanyID
WHERE CompanyName LIKE "*" & [Enter part of company name:] & "*"
AND SaleDate BETWEEN [Enter start date:] AND [Enter end date:];

You can construct this in the query grid; just add the two tables,
select the Value, SaleDate and CompanyName fields and put the criteria

LIKE "*" & [Enter part of company name:] & "*"

and

BETWEEN [Enter start date:] AND [Enter end date:]

on the Criteria line under companyname and saledate respectively.

With your current design, this type of query will be difficult or
impossible. At best you would need to write custom VBA code to
construct the SQL of the query dynamically.

John W. Vinson[MVP]

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top