table structure

[Baybars]

Hi,
I have a project which will control my shop's income and expenses
daily basis (not each purchase).
I have 3 different type of income. First one is the credit cards. So I
have to specify WHICH BANK (name of the bank), TYPE OF WITHDRAWAL (3
different categories, full amount, bonus usage or payment in 2 to 4
months). Last field is the AMOUNT.
Second table is CASH purchase. This could be local currency as well as
foreign currencies. I structured the table as CURRENCY, AMOUNT,
EXCHANGE RATE and LOCAL CURRENCY VALUE (of foreign currencies).
Third table is OTHER INCOME ITEMS. Such as payment for services (not
purchases, so have to specify what service it is).

I tried to figure out to keep them in one table, but due to variation
of each table subject, I found it easier to make 3 different tables.
However, it is the trouble to see the results of each day (eg, I want
to see how much from X bank came in, or total of daily transactions).
I also want to use the data from expenses and see the daily summary or
"between dates" summary).
I am not asking for someone to make it for me, but if it is clear
enough what I wrote above, I want to know whether I am going into an
endless well or right direction.

Regards,

Baybars

 

[Pavel Romashkin]

It looks like you set up your data structure reasonably well. In order
to see the data you want you should use queries. Make a new query in
design view and select the tables you want, then use Criteria to limit
the number of dates you see.
Use the Totals row (right-click the design grid to see it) to summarize
the transactions.
Pavel

 

[Larry Daugherty]

--
-Larry-

 

[Larry Daugherty]

I'm not sure you should trust an answer from someone at 04:00.

What springs to mind is a simple unbound Report that will show just the sum
fields for each element you described. It will have to ask you for the
date to display/. You can put a message box or an Input Prompt to get the
date to a text box on the report so you always show the date in your report.
For the individual sums I'd use DSum() as the data sources for the
individual sums and refer each one to the date you just put into the textbox
ReportDate. For the grand total sums, simply refer back to the values in
the textboxes above and add and subtract them appropriately.

The hole you're digging shouldn't get much deeper than that. If the above
misses the mark please post back with questions and clarifications.

HTH

 

[Baybars]

Dear Larry and Pavel,
Both answers gave me a relief so I can continue my tables as they are.
Thanks a lot for your time spent in sleepless hours.
The challenge for me is to build up everything on these tables. I will
need to see the results instantly when I enter the values. For
example, data entry for Bank transactions, I want to see the total of
that specific day (so I can see the what I am doing at that moment). I
will need the totals of a specific day later (after completion of
daily entries). Also I will create some other reports like monthly
bank transactions, cash transactions etc etc.
I used Dsum in a textbox control source to see the Sum of specific
date. But I failed. I used similiar to this but doesn't work. What I
understand is Dsum will sum the field accoding to date which is shown
in form during entry.

=DSum("[FieldToSum]","NameOfTable","[DateFromTable] =
Me![DateTextBox]")
It gives syntax error, when I tried variations, then it returns
errors. I found this formula from other posts, but I must be missing
something somewhere.

I appreciate your comments.

Regards,
Baybars

 

[Pavel Romashkin]

Try

=DSum("[FieldToSum]","NameOfTable","[DateFromTable] = #" &
CStr(Me![DateTextBox]) & "#")

I think also that you may want to use >, < or BETWEEN instead of = when
filtering data by the date.
Pavel