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

 

[Andrew Fergus]

Hi Bubbis

Have you considered putting all of the data into one table? Where you have separate tables for each month it is difficult (not impossible) to recombine the data in queries, as you have found it.

If this is not an option, then the SQL for the query (Click View -> SQL when in query design) to add the two fields together is this :

SELECT T_Jan_Data.Line, [T_Jan_Data].[Amount]+IIf(IsNull([T_Feb_Data].[Amount]),0,[T_Feb_Data].[Amount]) AS TotAmount
FROM T_Feb_Data RIGHT JOIN T_Jan_Data ON T_Feb_Data.Line = T_Jan_Data.Line
ORDER BY T_Jan_Data.Line;

Please note I used the table names T_Jan_Data and T_Feb_Data - be sure to use your actual table names. Also be aware that I have assumed the Jan table contains a complete list of the "Lines", a bad assumption I know but this is a work-around. It would be better if all of the data is in one table.

If you try to extend this to other months of the year then it is going to get pretty big pretty fast. It would be better to combine all of the data into one table - even if your data is stuck in discreet tables via an import process, you could create a new 'master' table and append the monthly data into that. This 'master' table would then be the basis for the queries.

HTH, Andrew.

 

[Bubbis Thedog]

Thanks so much for the excellent advice, Andrew!

I actually do have a table that will contain the data for all of the months of the year, but figured the query would work better using separate tables. You're correct that I import the data as a text file at the end of each month. I'll just append, let's say, February, to the end of January. What would the query look like then? (I understand SQL to moderate degree, so I'm not confused by the jargon.)

You're very kind to respond.

Thanks again,

Bubbis

 

[Andrew Fergus]

Hi Bubbis
I'm pleased you have 'seen the light', so to speak. To append the February data into the January table, the SQL (using my previous table names and field names) would look like this :

INSERT INTO T_Jan_Data ( [Month], Line, Amount )
SELECT T_Feb_Data.Month, T_Feb_Data.Line, T_Feb_Data.Amount
FROM T_Feb_Data;

If you have a look at some of the recent posts there are links to some good web pages on database normalisation that might help with your database design.
HTH, Andrew.

 

[Bubbis Thedog]

Thanks again for the excellent advice, Andrew! I actually use INSERT clauses while constructing MySQL tables, so I understand that. I don't mean to harp on the issue, but I have an 'extension' of my earlier inquiry [I thought that I could get a kick-start using a simple example, but I can't; sorry.]:

So let's say that I append February's data to January's in a table called "All Months." In QueryJan, I need to be able to see something like this (referring to the data in Table1):

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

In QueryFeb, I need to see (referring to the data in Table1 AND Table2):

Month     Line     YTD
February     CM     10
February    FIN     30
February    NBR    60

Remember that February NBR is 40 in "All Months--" as it was in Table2. And as you can see, since the CM and FIN lines did no business in February, I'd like them in the query to remain the same as January, but the month changes.

This is how I get my data unfortunately. I need to find a way to 'carry over' lines if they have existed before. Is it even possible for me to do this? I have a way of doing it in Excel right now, but it involves a little bit of 'filling-in-the-blanks,' which I'm not fond of because I'm afraid that I'll screw something up by doing some things manually.

I'm sorry if I'm confusing you, Andrew. I really appreciate your assistance thus far.

Thanks,

Bubbis

 

[Bubbis Thedog]

Ok, I know I've made a mess of things now; but it's the way that I get my stinking data. I think that I'll leave the data in separate tables, and then apply your first SQL statement in multiple queries. I'll try this and post my results.

Thanks, Andrew, for taking the time to help me!

Bubbis

 

[Andrew Fergus]

What is the 'mess' you referred to? It may have been better to create a new table and transfer all of the data into that table.
My 2c
Andrew

 

[Bubbis Thedog]

Hello, Andrew. "Mess" = Trying to explain things in simplistic terms, which was ultimately inadequate, it turns out, to give me the kick-start that I needed; now I think I should re-explain from the top, and hope that you are patient enough to still assist me ...

My actual table structure is this:

Month  Field_Rep  Section  Line  Current_Month  Prior_Year

Now, January may contain field reps that February does not; and, conversely, February may contain field reps that January does not. What I need my monthly query to include is a list of all field reps that have worked here, along with their year year-to-date total for that particular month. Here is a common situation:

Jan_Table:

Month  Field_Rep  Section  Line  Current_Month  Prior_Year
January  Bob      AF        12         100          125
January  Anne     NOC        6          5            7

Feb_Table:

Month  Field_Rep  Section  Line  Current_Month  Prior_Year
February  Bob      AF       12         128         123
February  Charlie  NOC      12         10           11  
February  Tom      CO       CM         50           35

Mar_Table:

Month  Field_Rep  Section  Line  Current_Month  Prior_Year
March  Anne        NOC       6        200        234
March  Charlie     NOC       12        12        14

So then, a February query for YTD should look like:

Month  Field_Rep  Section  Line  Current_Month_YTD  Prior_Year_YTD
February  Bob      AF       12       228              248
February  Anne     NOC       6        5                7  
February  Charlie  NOC      12       10               11
February  Tom      CO       CM       50               35

...and a March query for YTD should look like:

Month  Field_Rep  Section  Line  Current_Month_YTD  Prior_Year_YTD
March  Bob        AF       12       228              248
March  Anne       NOC       6       205              241
March  Charlie    NOC      12       22               25
March  Tom        CO       CM       50               35

I hope this makes clearer what I am trying to accomplish. Thanks for your time thus far, Andrew.

Bubbis

 

[Bubbis Thedog]

Well, I've found a way to do most of it:

1) Set up a table that includes Jan, Feb, Mar, etc. (Feb, Mar, etc. appended to Jan), as you (Andrew) recommended.

2) For query Feb, I used

SELECT Jan_YTD.Section, Jan_YTD.Line, Jan_YTD.[Field Rep],
Jan_YTD.Cur_Mth_YTD+February.[Current Month]
AS Cur_Mth_YTD

FROM Jan_YTD, February

WHERE Jan_YTD.[Field Rep]=February.[Field Rep]
AND Jan_YTD.Section=February.Section
AND Jan_YTD!Line=February.Line;

...where Jan_YTD is a 'query.' Therefore, for March, Jan_YTD would be replaced by Feb_YTD, and February would be replaced by March.

That's actually pretty elementary SQL. Now I'm trying to figure out how also include the remainder of the records from Feb. I'll keep you posted --if you give a @!?# .

Bubbis

 

[Andrew Fergus]

Hi Bubbis
From your last paragraph it sounds like some of the records that should be in the query are not there. If this is the case, then I suspect it is because you have based the Feb YTD query on the JAN YTD query and the table. If there is a rep in the Feb table that is not in the Jan YTD query then it may be omitted from the Feb YTD query results. Try creating your Feb YTD query solely on the table instead, much like you would have for the Jan YTD query. The criteria will be something like ="January" or "February".

BTW, working with month names is much harder than working with month numbers or dates. Is there any chance of converting the months to dates? You couild do this by adding a new field into the master table which is populated by an update query.
HTH, Andrew.