daily / monthly data join... help

vo2max_999

New Member
Joined
Sep 11, 2006
Messages
2
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
etc...

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.

vo2max_999
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.

HTH,
 
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?

Thanks
vo2max_999
 
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.

Code:
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.

Mike
 
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
Mike,
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.
Vic
 
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.

Mike
 
Upvote 0
Mike, that is an outstanding agenda you have. Keep it up!
Vic
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

Threads
1,223,304
Messages
6,171,319
Members
452,396
Latest member
ajl_ahmed

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
Back
Top