Queries and Reports

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

I have 2 seperate queries created one for deposits by
register number and one for total store deposit. I would
like to show each field from the first query and the
totals for each column from the second. However each time
I attempt to make the report I get a message stating I
can not use both queries. Is there an easy way around
this. Thank You for your help.
 
Nathan

We can't see your data, nor your queries, so it could be tough coming up
with sensible suggestions. However, that has rarely stopped me before...
<g>

Something you said, "... totals for each column..." makes me wonder if you
have designed your table with one column per store. If so, read up on
normalization, as your design is more like a spreadsheet than a relational
database.

Please provide more information -- what is the exact error message you
receive?

Jeff Boyce
<Access MVP>
 
Sorry if I was to vague. Here is what I have.
I work in a store woith threre cash registers. I built a
table for the following information.

Date-Register #-Total Cash-Total Checks-Total Deposit

I built a query that collects the 3 records from each
register based on the date.
I built a second query that totals each currancy column
of those records for that date.
I would like to build a report that I can print each
night that shows me each register entry and the combined
currancy totals for the date.
I.E.
reg. 1 - cash - checks - total
reg. 2 - cash - checks - total
reg. 3 - cash - checks - total

total cash
total checks
total deposit
Thanks Again Nathan
 
Nathan

I'll suggest that you check into the topic of normalization. You have three
"repeating" fields describing types/categories of amounts.

By normalizing your data structure, you could easily total, by category. It
will be more work to do it with your current design. And with your current
design, if your operation ever adds credit and/or debit cards, or direct
EFT, you'll have to: rebuild the table, rebuild any/all queries against the
table, rebuild any reports, rebuild any code involving the table, ...

You could conceptualize the data as:

trelRegisterReceipt
ReceiptsID
ReceiptsDate (don't use "Date" as a field name -- it's a reserved
word in Access)
ReceiptAmount
ReceiptType (cash, check, credit, ....)
RegisterID

With this design, you could write a simple query, group by Register and
Type, to get amounts. And in your report, you could add a control that sums
those types, for your Total

Note that you don't need and probably shouldn't be storing the TOTAL TOTAL.
It is calculable from your type totals.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top