Complex Query...

[Bubbis Thedog]

Hello, all! This is my first post to the Access forum, and I could use some guidance, if possible...

Suppose I have Table1 with 3 records:

Month     Line     Amount
January    CM      10
January    NBR     20
January    FIN     30

... and Table2 with 1 record:

Month     Line     Amount
February  NBR      40

I'd like to write a query that returns a column that adds amounts only for those records with the same "Line." The final table (query) should look like this:

Line     Year to Date
CM       10
NBR      60
FIN      30

I've tried to get several SQL statements to work, but cannot. Any help would be greatly appreciated!!!

Bubbis

 

[Bubbis Thedog]

Hey there, Andrew. Thanks so much for the continued advice, man. I told you errantly in that last post that I started using an 'appended' table; I'm still using separate tables. Here's why:

The reason I was going to use Jan_YTD (a query whose Current Month column values are going to return January's values since it's the first month of the year) with the February table; Feb_YTD (a query that would include January's values plus February's values) with the March table; Mar_YTD with the April table; and so on is because each monthly query reflects year-to-date totals that can simply be added to the next month's totals. If I were to continue with this approach, I'd have two things to consider:

Suppose I'm working on the query for March (Mar_YTD):

Scenario 1: The same records are in, for example, the March table than the Feb_YTD query, with the addition of other records.

Here I would need to include in the query records from March that were not returned by the WHERE condition in my SQL statement above.

Scenario 2: There are fewer records in, for example, March than there are in the Feb_YTD query.

This indicates that at least one field rep record is missing in March that was in the Feb_YTD query, and thus my Mar_YTD query in it's present form will eliminate field rep records that can be found in the Feb_YTD query. So, my SQL statement must also be designed to include records in the Mar_YTD query that are 'left over' from the Feb_YTD query.

Ok, so that's how I'd do it. Of course, Andrew, I highly doubt that my SQL and database design experience is up to par with yours . This really isn't a relational database, though; I didn't intend it to be that way. Access just seems like a much better alternative to Excel formulas in this case. I've tried appending, for example, February records to January's, and working from there; but I couldn't devise a plan to make it work. I also tried working solely with tables as you suggested, rather than tables mixed with queries: no luck there either.

Pardon my lack of experience, friend. I'm trying, daggonit! :wink:

Thanks again for your patience and assistance.

Bubberino

 

[Andrew Fergus]

Hi Bubberino

Approximately how many records are in each of the monthly import files? If the volumes are large enough for Excel to be a hassle (which it probably will be given you attempted to do this in Access) then I will lean towards advising you to use the relational bits of the database and having a 'master' table.

As you have already worked out, where you have records in one table that are not in the other, then you are going to strike problems. Access can't deal with records missing from both tables because it has to use at least one of the tables as the base. The only way (other than using a 'master' query, someone please correct me if I am wrong) is to create a 'master' table.

Using a 'master' table will make your life 100% less stressful and painful when handling the data. Any time spent setting up the database properly will save you ten-fold the time in future. And I can't stress enough, that given Access is a relational database, to actually make use of the available functionailty.

Have a think about what you want in this master table - e.g. all of the imported data plus what other fields would you want? A proper date? (Yes, definitely!) Anything else? Don't even try to use the January table as your 'master' table - create a whole new table and append your monthly records across. You can then "update" the blank fields (such as date etc) using update queries and a bit of logic. You will be surprised how many records Access can handle - I have used databases with millions of records.

Until you do this you are going to fight with your data - if it is not set up correctly then it is going to be like juggling with a cactus.

Andrew

 

[Bubbis Thedog]

Andrew, thanks for having the patience to repeat (beat into my head hahaha) your valuable information. After trying UNIONs and embedded SELECTS and WHERES... I relent! I have now

1) Made an All_Months table that contains monthly data: month appended to month;

2) Changed all of the "January"s to "1/31/2005" and changed the field type from Text to Short Date.

Approximately how many records are in each of the monthly import files?

Oh, anywhere between 180 and 200 per month --not many at all obviously. This is why I simply wrote a formula and did some creative spreadsheet setup to get it to work in Excel. But I don't like the occasional --albeit miniscule-- modifications that I have to do manually. Access is the answer for this one.

As you have already worked out, where you have records in one table that are not in the other, then you are going to strike problems. Access can't deal with records missing from both tables because it has to use at least one of the tables as the base. The only way (other than using a 'master' query, someone please correct me if I am wrong) is to create a 'master' table.

You're right, I exhausted my options doing it the other way.

append your monthly records across. You can then "update" the blank fields (such as date etc) using update queries and a bit of logic.

"Across?" I appended, for example, February, under January. Is this how you mean to do it, or do you actually mean to create identical fields for each new month?

When I get that straight, I would then ask how you would design an UPDATE query. I assume you mean an UPDATE, SET, WHERE EXISTS one.

Thanks, Andrew, for your continued patience and kindness, and your willingness to assist me.

Bubbis

 

[Andrew Fergus]

Hi Bubbis

My apologies for a bad choice of word - 'across' can have two meanings. You are right, I meant add the records to the table, as you say, under the existing data, using the structure that is already in place.

If you have changed your month field to a date format then you don't need to do the update query. I had thought that the 'January' was fixed in the import process and could not be changed. If you changed it manually then you will be ok for another month - let's get the basic functions working properly before we introduce any 'smart' functions to do this automatically.

But now you have the data in a more useable format, you can extract the FEB YTD data using a query with a criteria under the date field of something like <=#02/01/05# (please note that this is very simplistic). There are a number of recent threads that will tell you how to get automatic reporting YTD based on the current date.

HTH, Andrew.

 

[Bubbis Thedog]

Andrew, thanks to your guidance, I got the query the work. After I put all the data into one table, and changed "January" to "01/31/2005," et cetera, my query looks like this:

SELECT DISTINCT [All Months].[Field Rep], [All Months].Section, [All Months].Line, SUM([All Months].[Current Month]) AS [SumOfCurrent Month], SUM([All Months].[Prior Year]) AS [SumOfPrior Year]

FROM [All Months]

WHERE [All Months].Month < #3/1/2005#

GROUP BY [All Months].[Field Rep], [All Months].Section, [All Months].Line;

I'm sort of embarrassed that it was that simple actually!

Thanks again for all the time you spent to help me out, and with such remarkable patience.

Vinny Bubberino

 

[Andrew Fergus]

Hi Vinny
If the data structures are right then everything else is easy, but I wouldn't have said embarrasingly so.
Andrew

 

[Bubbis Thedog]

Dude, I aced an SQL exam for Oracle a couple of years back; it's amazing how that knowledge found its way to the back of my brain.

Thanks for all your help. It has enlightened on how much I need to reacquaint myself with SQL/DDL/DML .

BuhBuhBuh... Bubbis!