Adding Fields

[Devin Spilker]

Can anyone tell me how to build a query that will allow me to add the
currency values listed in different fields together. I have several fields
that have a dollar values in them and I would like to add those together by
month and year. Any thoughts? Thank you so much in advance for your time
and assistance.

Devin

 

[Tom Lake]

Can anyone tell me how to build a query that will allow me to add the
currency values listed in different fields together. I have several
fields that have a dollar values in them and I would like to add those
together by month and year. Any thoughts? Thank you so much in advance
for your time and assistance.

It sounds like you should have more than one table with the dollar amounts
in the second table. Post the table structure you have now and we can help
you normalize it to be more efficient and more than likely save some space
while you're at it.

Tom Lake

 

[Devin Spilker]

The table is for my monthly bills. My table is composed of fields in which
I have debitors. Each field is a seperate bill and each month the amounts
are different. I just want to be able to add the amounts together for each
month. Thanks again

Devin

 

[Tom Lake]

The table is for my monthly bills. My table is composed of fields in
which I have debitors. Each field is a seperate bill and each month the
amounts are different. I just want to be able to add the amounts together
for each month. Thanks again

Each separate bill should have it own record. You can assign an ID
to each debitor and then have a corresponding ID in the Bills table.
Here's an example of the table structures:

Table1: Debitor Table:

DebitorID <- Make this the Primary Key
Name
Address
AccountNo

Table2: Bills Table
DebitorID <- Make this an index but NOT unique.
DateOfBill
Amount

Set up a one-to-many relationship between the tables by linking the
DebitorIDs

For each entry in the Debitor Table, you can have none, one or as many as
you want in the Bills Table.

Now it's an easy matter to sum the Amounts by Date. Create a Query with
both table and the link
between the DebitorIDs in both tables. Add The DebitorID, Name and Address
fields from the Debitor
Table and the amount from the Bills Table to the query. Now make it a
Summary Query,
change the Amount column grouping type to Sum and put the following as its
criterion:

"#" & [Enter Date to be Summed] & "#"

Now when you run the query, it will ask for a date to be summed. put in the
date
and you'll see a sum for each debitor.

The way you have it now, you'd have to add fields to the table once you fill
up every payment slot.
With the two-table method, you can have thousands or even millions of bills
for every debitor
without having to change the table structures.

Tom Lake

 

[Devin Spilker]

Thank you Tom. I will give it a shot. Much appreciated.

Devin

The table is for my monthly bills. My table is composed of fields in
which I have debitors. Each field is a seperate bill and each month the
amounts are different. I just want to be able to add the amounts
together for each month. Thanks again

Each separate bill should have it own record. You can assign an ID
to each debitor and then have a corresponding ID in the Bills table.
Here's an example of the table structures:

Table1: Debitor Table:

DebitorID <- Make this the Primary Key
Name
Address
AccountNo

Table2: Bills Table
DebitorID <- Make this an index but NOT unique.
DateOfBill
Amount

Set up a one-to-many relationship between the tables by linking the
DebitorIDs

For each entry in the Debitor Table, you can have none, one or as many as
you want in the Bills Table.

Now it's an easy matter to sum the Amounts by Date. Create a Query with
both table and the link
between the DebitorIDs in both tables. Add The DebitorID, Name and
Address fields from the Debitor
Table and the amount from the Bills Table to the query. Now make it a
Summary Query,
change the Amount column grouping type to Sum and put the following as its
criterion:

"#" & [Enter Date to be Summed] & "#"

Now when you run the query, it will ask for a date to be summed. put in
the date
and you'll see a sum for each debitor.

The way you have it now, you'd have to add fields to the table once you
fill up every payment slot.
With the two-table method, you can have thousands or even millions of
bills for every debitor
without having to change the table structures.

Tom Lake

 

[Tom Lake]

Oops. The following line should be the criterion for the Date field, NOT
the Amount field. Sorry!

"#" & [Enter Date to be Summed] & "#"


Tom Lake

 

[Devin Spilker]

Tom

I have everything working except the summary query. I do not want to search
by exact date but rather the month and the year. Is there a way to write
the expression to only look for month and year? Thank you aagain you have
bee a big help!

Devin

The table is for my monthly bills. My table is composed of fields in
which I have debitors. Each field is a seperate bill and each month the
amounts are different. I just want to be able to add the amounts
together for each month. Thanks again

Each separate bill should have it own record. You can assign an ID
to each debitor and then have a corresponding ID in the Bills table.
Here's an example of the table structures:

Table1: Debitor Table:

DebitorID <- Make this the Primary Key
Name
Address
AccountNo

Table2: Bills Table
DebitorID <- Make this an index but NOT unique.
DateOfBill
Amount

Set up a one-to-many relationship between the tables by linking the
DebitorIDs

For each entry in the Debitor Table, you can have none, one or as many as
you want in the Bills Table.

Now it's an easy matter to sum the Amounts by Date. Create a Query with
both table and the link
between the DebitorIDs in both tables. Add The DebitorID, Name and
Address fields from the Debitor
Table and the amount from the Bills Table to the query. Now make it a
Summary Query,
change the Amount column grouping type to Sum and put the following as its
criterion:

"#" & [Enter Date to be Summed] & "#"

Now when you run the query, it will ask for a date to be summed. put in
the date
and you'll see a sum for each debitor.

The way you have it now, you'd have to add fields to the table once you
fill up every payment slot.
With the two-table method, you can have thousands or even millions of
bills for every debitor
without having to change the table structures.

Tom Lake

 

[John Vinson]

Tom

I have everything working except the summary query. I do not want to search
by exact date but rather the month and the year. Is there a way to write
the expression to only look for month and year? Thank you aagain you have
bee a big help!
PMFJI...

Try

= DateSerial({Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:] + 1, 1)

as a criterion on the date field.

John W. Vinson[MVP]

 

[Devin Spilker]

Awesome. Thank yuo so much! I do have one more question. The above allows
me to view totals for each debitor per month. What if I wanted to add the
sums of each debitor together. For example:

I paid debitor 1 12,000 in Jan, debitor 2 56,000, debitor 3 135,784.32, and
debitor 4 64,523.66 (all in the month of Jan). Can I develop a query that
will allow me to add all of these summs together? Thank you guys very
much.

Devin

Tom

I have everything working except the summary query. I do not want to search
by exact date but rather the month and the year. Is there a way to write
the expression to only look for month and year? Thank you aagain you have
bee a big help!
PMFJI...

Try

= DateSerial({Enter year:], [Enter month number:], 1) AND <

DateSerial([Enter year:], [Enter month number:] + 1, 1)

 

[John Vinson]

Awesome. Thank yuo so much! I do have one more question. The above allows
me to view totals for each debitor per month. What if I wanted to add the
sums of each debitor together. For example:

I paid debitor 1 12,000 in Jan, debitor 2 56,000, debitor 3 135,784.32, and
debitor 4 64,523.66 (all in the month of Jan). Can I develop a query that
will allow me to add all of these summs together? Thank you guys very
much.

Sure, just don't Group By debitor in the Sum (just remove the debitor
from the query altogether).

If you want to see the individual line items AND the total, use a Form
or Report based on the query, and sum the amounts in a textbox in the
form/report Footer.

John W. Vinson[MVP]