Multiple, Non-Related, Sources in a Report. 1

[CptCrusty1]

I am trying to create a report to give count of a list of different Fields. My "Master Table" I'll just call tblMaster, which contains data about cars that have been sold,i.e. New_Used, VIN, StockNum, Customer, etc. The table also has purchasable options like: LoJack, Etching, Tinting, etc. I need to create a report which is going to count the total for a given time. I already have a query to select a date range. (I know I'm long-winded). My problem is thus: I don't think I can get that kind of an answer in 1 query, but I can't put multiple sources in a report unless they are related. Can I dump a text box in the report and use a query as the control source to populate the box? Without telling the Form what my source is? What kind of special formatting do I need?
HELP?

 

[dhookom]

You can't use a SQL statement as the control source of a text box. You can use DLookup() or other domain function as a control source.

However, you haven't really told us what you want except to say "count the total for a given time". Count what? We don't even know your table structure(s). Are your options in a related table like a normalized application or do you have fields for options?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CptCrusty1]

Well... you've answered the basic question of the Text Box. BUT, can you use multiple data sources in a report without having to create numerous Sub-Reports?

Table structure? OK. Here's a snapshot.

NEW/USED- value is New or used only.
Entered_Date
Customer_Name
Stock_Num
GAP - a vehicle option for purchase
ETCH - ditto
ROA - ditto
Salesman1
Salesman2

The client wants a report showing a breakdown of the number of options purchased during a given period of time, i.e., one accouting cycle, or one month. Ergo:

New Used
GAP 3 4
ROA 5 0
ETCH 1 5

My current method is to create a query for each count, i.e. 1 for GAP(New) and one for GAP(USED). I have a total of TEN columns which need to be counted, so I'm looking at 20 queries to return 1 number as its data-set. I then create a sub-report to pull that data set. This means LOTS of formatting, LOTS of manuevering the stuff on the report. There has GOT to be a better way.

I hope that clarifies things a little better. Thanks for the response. I appriate it greatly.

Crusty

 

[dhookom]

Your table is not normalized. Either normalize or create a union query that does so.

SELECT Stock_Num, "GAP" as Option
FROM tblAuto
WHERE GAP =-1
UNION ALL
SELECT Stock_Num, "ETCH"
FROM tblAuto
WHERE ETCH =-1
UNION ALL
--etc--

You can then create a totals query based on the union query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CptCrusty1]

Cool.. your Union query gives me an idea. The problem with all of the GAP, ETCH, etc, is that the value of the field is a dollar amount that is not a constant. It can be different every time. The same with customers, etc. But you gave me a great idea. I'll let you know.

 

[CptCrusty1]

It worked! I created individual queries for the necessary items (9 not 10) like this:

SELECT tbl_FinanceSourceByDateRange.New_Used, "GAP" AS GAP_Gross, Count(tbl_FinanceSourceByDateRange.GAP_Gross) AS CountOfGAP_Gross
FROM tbl_FinanceSourceByDateRange
WHERE (((tbl_FinanceSourceByDateRange.GAP_Gross)>0))
GROUP BY tbl_FinanceSourceByDateRange.New_Used, "GAP"
ORDER BY tbl_FinanceSourceByDateRange.New_Used;

Creating an Alias column with the name of each type to be counted. Then a Union query which also renamed the Alias to yet another alias, which brought the whole thing together.

Thanks for your help dhookom, it gave me the idea and the solution.

CptCrusty1