Good evening Duane,
Don't think you are going to be happy with my answers but here goes. I have
been using Microsoft Help to look for information on UNION Queries but took
your advise and Googled it. The articles I read made sense, but I do not
think the use of one will help me. I looked up in the Discussion Group, the
use of filters in a form, I am completely confused. I do not know what you
mean when you say "IMHO parameter prompts aren't acceptable user interface."
(I do not write code) Please explain.
You asked me in one of your earlier posts to tell you the tables I have and
the field names. I have an Income Table, tbl_Income. The fieldnames are:
[RefMo] (Number]
[IncDate] (Date/Time)
[Bank] (Number)
[Sales] (Currency)
[ACD] for A Cash Donation (Currency)
[Contributor] (Number)
[IncType] (Number)
[OS] for Other Sales (Currency)
[IKC] for In-Kind Contributor (Number)
[Donation] (Text)
[IKValue] (Currency)
for all of my (Number) fields above, I have tables listing the choices, then
I have each FieldID Joined to the tbl_Income in a query.
Next major thing is Expenses so, tbl_Expense with the following fields:
[Bank] (Number)
[RefMo] (Number)
[CkDate] (Date/Time)
[CkNo] (Text)
[Bsn] (Number)
[Whoes] (Number)
[ExpType] (Number)
[Description] (Number)
Again, for all of my (Number) fields above, I have tables listing the
choices, then I have each FieldID Joined to the tbl_Expense in a query.
I have two forms for entering the information. On the forms, I have used
Combo Boxes, and set the Row Source (using the . . . icon) to reference the
respective tables (John Vinson, MVP) taught me to do that rather than using
the Lookup Wizard. Everything works great. When I need to edit something, I
do so on the form. However, as stated above, I don't write code and am
completely lost when you refer to using a filter on the form rather than a
parameter query for my reports.
From the Income information, I ultimately want a report which shows me, for
a certain time period, (maybe something else later) the day of the month
something was either sold, or donated and by whom and what it was. I also
want a report which shows me, in columns which are side by side on the same
report (and as you said, there are more records in one field than another) I
can build the report using the parameter query but the columns appear off
set. I have been trying to get a subreport to work, but as of yet, have not
had any luck.
From the Expense information, I have successfully made two different
reports. One which shows all information about one particular type of
expense; and one report which shows the total expense amount for each
[description] and each [ExpType]
What I need to do with the information is to combine the tbl_income and
tbl_expense for each bank, into a financial statement.
Bank [A]
[ABegBal] which would come from each banking information table
Income:
[Asales]
[Aos]
[Aoi]
[Acd]
Total Income: =Sum(Nz(Asales],0) etc.
Expenses:
[Whoes]
[Type] TotalType
Total Expenses:
[AEndBal]
I would like to put this information in side by side columns on the same
report page. I think the sub-report would be the thing to use, if I can just
get one to work. I am still doing research and expermentation on that.
I do hope this tells you what my tables are, examples of my field names, and
what I want from my reports. I really hope it does not confuse you more, and,
I hope you can help me. But, Duane, please remember I do not write code. I
can, on some occasions, make sense out of an SQL statement, but sometimes
not. I am afterall, a beginner. k
Duane Hookom said:
Check Help or google on UNION Queries. There is a ton of information.
There is also lots of information on how to use a control (or controls) on a
form to filter a query. IMHO parameter prompts aren't acceptable user
interface.
--
Duane Hookom
Microsoft Access MVP
:
Duane,
Sorry I'm making you confused. Perhaps if I knew exactly what a union query
was, it would help. Also, how am I to generate only a certain date span on a
report if I don't use a parameter query?
Anyway, you are off the hook for awhile, I have a meeting to go to and won't
be home for awhile. When I get back, I'll re-read everything you have said
closely and see what I am supposed to be doing.
Have a good evening and thanks for your help. k
:
Please:
- describe your data with table and field names
- tell us how you want to show this in a report
- stop using parameter prompt queries. Replace all prompts with references
to controls on forms
--
Duane Hookom
Microsoft Access MVP
:
Duane,
I sent the SQL view for the query. Hope it is helpful.
Here is the new problem. I have created a report showing the Cash
Contribution side of the report and a report showing the In-Kind Contribution
side. I found the sub-report icon. As with everything else, I seem to have
nothing in common with each query/report expcept the Month and Year. The
parent/child relationship does not seem to work. As stated earlier, I have
fewer In-Kind Donations than Cash Donations therefore using the other
parent/child relationships don't seem to work. Also, I have the Income date
used to generate the results by using Between [Enter Start Date] And [Enter
End Date] when I try to use "none" as the connecting field, the report just
keeps asking me for the start and end date over and over again.
Any suggestions on what to do for this problem? k
:
If your different groups are "Income" and "Expenses" I expect you could
create one or more subreports to get at your desired results. Do you have an
account number or similar that you want to group by? IOW what ties a group of
Income records with a group of Expense records? Are you using a union query?
If so, can you share the SQL view?
--
Duane Hookom
Microsoft Access MVP
:
Good afternoon Duane,
Yes, they are.
When I tried to combine my SumQry_MonthIncome with my SumQry_MonthExpenses,
up in the right hand corner appears a tag which says "Union Query." But, when
I enter the field names , I receive multiple returns of the entries. Does
that mean that I am ready to develop some sub-reports? Boy, oh boy, do I hope
so; otherwise I'm going to be doing some cutting and pasting at the copy
machine tonight or I'm going to enter my information quickly into Excel.
What do you think I'll be doing? k
:
Aren't your "groups" just different records from the same table/query?
Subreports are much like subforms. They are just reports that are placed on
other reports.
--
Duane Hookom
Microsoft Access MVP
:
Good morning Duane,
Sorry to take so long getting back.
Your first paragraph is right on--correct; the number of records in each
group varies. The good news is, I hope, is I have already put the information
in a query and am using that query as the Control Source for the report. In
the report, I have put the information for each column in the Detail section.
For the left side of the report, I have [CC] [ACD] for the right side of the
report, also in the Detail section, I have [IKC] [Donation] [IKValue]. All
information is there except for the fact it list all the information for the
Cash Contributors before it lists all the information for the In-kind
Contributors. I need the columns to be side by side !! equally regardless if
one column has more data than the other.
And, unfortunately, you have completely lost me on the second paragraph;
except to put them in a query. I have browsed everywhere I can think of to
find out how to create a sub-report, how to create a union query, and how to
create a report that pulls information from more than one query (where
information in those queries is necessary for the report, and everything
needs to be in side by side columns (a monthly financial report for two
banks) without the use of code. I am mentioning that, because I was hoping to
be able to have a sub-report under my nice neat side by side columns which
will show the amount of cash contributions and store sales I have received
for the last 12 months.
I know all this can be done. I know it can be done in Access better than in
Excel or Word, what I've used. I just need help in obtaining the wisdom of
how. I have the determination and patience, and am willing to help others
learn what I know. I hope you have those qualities and can help me out. k
:
Apparently you have groups of records that you want appear together in
columns even though the number of records in each group might vary.
I would change the record source of the main report to a query of just
unique "group" values. Keep the columns. Then create a single column
subreport that can be place in the detail section of the main report. Set the
Link Master/Child properties to the "group" field.
--
Duane Hookom
Microsoft Access MVP
:
I have a report and I would like the information to print side by side on the
report page ! ! (columns which are even at the top) not !
!
(Columns where all the information for one group is printed before the
information from the next group is printed. Is there anyone out there who can
help me out? I really hope so becauxe I need to complete all reports by
tomorrow (8/13) and have used many of the suggestions I've received from you
all, but I am still struggling with some things. the above is one of them.
The next is how to et everything together in an Access report wich looks
like a financioal sheet. I will need to combine several queries and am having
a problem with part of the results showing, then I'll add another field and
I'll receive no returns or i'll receive over 2,000. More spicifics on this
later. Right now, if someone could help me out with the side by side issue,
I'd really be greatful.
k
