Populate one table using a formula that Connects to another Table

[dL1727]

I have two tables: Payments by Month and Payments by Quarter. The 'Payments by Month' Table has fields that indicate how much of a given payment was received in a certain month (i.e. 12 fields). The 'Payments by Quarter' likewise has similar information, however reduced to only quarters (i.e. 4 fields).

Ideally, I would like to enter payments in the columns "Jan, Feb, Mar" of Payments by Month and see that information automatically directed, and cumulated, in the column "Q1" of Payments by Quarter. So if 'Payments by Month' showed Jan = 10, Feb = 5, March = 10 for ROW 1, I would like to see 'Payments by Quarter' show Q1 = 25.

Any suggestions would be greatly appreciated.

 

[SteveSchapel]

DL,

I am sorry to say this, on what is apparently your first visit to this forum, but you have made quite a big mistake here. This is sometimes called the "fields as data trap". To put it bluntly, you simply should not have a Payments by Month table with 12 fields, one for each month, nor should you have a Payments by Quarter table with 4 fields, one for each quarter.

This is not an appropriate database design. One option would be to move to Excel, as a spreadsheet is much more applicable to the kind of structure you are working with.

Otherwise, if you have other reasons for using a database programme, my question is whether your database design is set in concrete here, or whether you could consider a re-vamp?

 

[dL1727]

Hi Steve,

I appreciate your rapid response! I am absolutely open to a revamp of the database format. I am pretty set on using a database for this project (instead of using Excel). What would you suggest instead?

Thanks,

dL

 

[Vassago]

At least something that had one table with a PaymentDate field would work. Then you could use queries to obtain the number of payments by month/quarter/etc...

 

[SteveSchapel]

dL,

Maybe you could give us a bit more information about what this data is all about, and what else is being tracked in the database, and whether these payments have to be related to something else such as orders or invoices or subscriptions. Specific examples can sometimes help.

But on the basis of what you have told us so far, you probably only need a simgle Payments table, with a field for the DateReceived, and a field for the Amount. Deriving monthly or quarterly summary breakdowns would be done in a Totals Query.

 

[dL1727]

I definitely agree that queries are the way to go. Here's a simplified version of what I am doing and then maybe you can direct me how to generate a query that gives cumulative figures:

I am entering Purchase Orders into a database, so PO# is my unique key. For simplicity, the next field is AmountRecd and the following being DateRecd. I would like to generate a query that tells me how much $ was collected in certain months and certain quarters.

Thank you so much for your rapid and considerate responses ,

dl1727

 

[SteveSchapel]

dL,

As a simple example, monthly totals, for the current year, could be obtained from the above described table by a query whose SQL view would look like this:

SELECT Format([DateRecd],"mmmm yyyy") AS MonthRecd, Sum([AmountRecd]) AS Collected
FROM [Purchase Orders]
WHERE [DateRecd]>DateSerial(Year(Date()),1,1)
GROUP BY Format([DateRecd],"mmmm yyyy"), Format([DateRecd],"yyyymm")
ORDER BY Format([DateRecd],"yyyymm")

Hope this gives you an idea of the concept.

 

[dL1727]

Hmmm I keep getting the following error:

"The Microsoft Office Access database engine cannot find the input table or query 'Purchase Orders'. Make sure it exists and that its name is spelled correctly"...

For clarity, my field headings in my "Payments Received" table are:

Purchase Orders
Date Recd
Amount Recd

 

[SteveSchapel]

dL,

Sorry, you didn't tell me the name of your table before, but I assumed from what you said that it would be named Purchase Orders. So it's "Payments Received"? And the names of the fields have a space in them, as in "Date Recd" rather than "DateRecd"? So what you told me before was not correct?

Oh well, I guess we can try it like this:

SELECT Format([Date Recd],"mmmm yyyy") AS [Month Recd], Sum([Amount Recd]) AS Collected
FROM [Payments Received]
WHERE [Date Recd]>DateSerial(Year(Date()),1,1)
GROUP BY Format([Date Recd],"mmmm yyyy"), Format([Date Recd],"yyyymm")
ORDER BY Format([Date Recd],"yyyymm")

 

[dL1727]

Bingo!

I'm going to play around with the query to get quarterly totals. The problem was actually the name of the given table--I mistakenly typed the fields with spaces, but I figured it out. Thanks Steve!

-dl1727