daily / monthly data join... help


New Member
Sep 11, 2006
Hello forumers

I need yr. help on the following. I will try and be as concise as possible.

I have 2 tables in access, a daily and a monthly table
The daily file has the following structure.

day ---> dd-mmm-yy
qty ---> number

01-Mar-06 456
02-Mar-06 999
03-Mar-06 123

The second table contains aggregated data for the month which is not contained within the daily file

month ---> mmm-yy
exceptions ---> number

Mar-06 123

Questions is how can I format the date/sum/join/group the tables, that the information would display as follows (based on limited data above)

Mar-06 1578 123

Thank you in advance.


Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Query 1 would summarize the "qty" in the first table by month/year. The second query would then join the month/year field from the first query with the month/year field in the second table, include the "qty" total from the first query, and the "exceptions" from the 2nd table.

Upvote 0
Hi Vic

Thnks for the fast reply, would it be possible to run this as 1 query? Do you have any suggestions wrt. the syntax of such a query?

Upvote 0
When you write that the Monthly file contains aggregated data not in the daily file - do you mean that the aggregated data is something else?

ie, the daily file shows you the number of item X that you used and the monthy file gives you the summary of item Y (completely different) that you bought for the entire month?

I think you actually mean something a little more obvious - that the summary data is simply not in the same table.

This doesn't really address your question, however, I think you've missed one of the most basic yet powerful features of a database. SQL

What you could have done is use a single table - the daily table and generated a query that resolves your monthly data.

SELECT sum(qty) FROM tblDaily GROUP BY Month([day])

As an important note, I put the fieldname day into [] brackets because it's also an Access keyword. Brackets lets Access know to try looking for a field.

The reason this is good - is you no longer have to maintain two completely separate although related sets of data. If you change the daily table, the monthly numbers just pop right back up.

With that said, there are some instances when you do want to build aggregate data tables, but it's generally when you're building a data warehouse or datamart and dealing with huge amounts of data and query performance is an issue. As it is, I'm thinking your daily figures are already an aggregate number - you could actualy expand this type of approach out to looking at actual sales data. Ie, pour over thousands of individual sales events and extract a daily OR monthly summary from the same raw data.

Now, back to your actual question. I really can't answer it without a little more explanation as to what you're attempting to show - numbers based on each other or not.

And for anybody else, please feel free to step in if you're reading something I'm not or have a better idea.

Upvote 0
When you run the second query, the first is automatically run to supply the requested records to the second query.

For the syntax, use the QBE grid. This is the grid that appears when you create a NEW query, or open an existing query in design mode. This is not always true, some queries will appear in SQL view in design mode. If this happens, then switch to the "Design View" using the View menu item.
Upvote 0
I think you might have missed that the aggrate data refered to in the initial post carries the field name of "exceptions" From that I implied that it really is separate data. Generally, I would think that an exception is not "sales" data, but maybe something to do with the sales figure, but would be coming from some other area of the business.
I really did like your explaination of how to approach the situation and explaining what brackets do.
Upvote 0
It wouldn't be the first time I looked too deeply into a problem.

I'll have to plead that I have an agenda. I've discovered that the act of explaining a concept in detail helps reinforce the technique in my own mind - and looking at the problems of others has helped me look at those same techniques in a different fashion.

Well, have to wait and see what additional information the original poster supplies.

Well, time to wait for more details from the original poster.

Upvote 0
Mike, that is an outstanding agenda you have. Keep it up!
ps: my agenda? I love helping and solving things. Plus, I have learned a lot from being helped, helping others, and reading answers to others.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back