Merge 2 tables

[Design by Sue]

My Main table has 5 columns, customer. brand, month, forecast and actual. These records extend December 2013 and there are about 8000 of them. At the end of each month I am provided with a table of actual sales for that month. This Monthly table has 4 columns, customer, brand, month and actual. I am trying to create a update query to insert the actual column from the Monthly table to the actual column of the Main table where customer, brand and month are the same. I have created a query that displays the correct records and the actual column from both tables. How do I combine the fields from both tables so that they are added to the actual column of the Main table. I have tried to do this as a simply update query, but when I run it, the actual information already in the Main table is replaced by the actual information from the Monthly table, so it removes actual records from previous months.

How do I merge the actual fields from the Main and Monthly tables so that the information from both are in the Main table (or another way to think of it is how to I insert the actual information into the Main table actual column where brand, customer and month are equal)?

Thanks in advance for any assistance!

Sue

 

[jdraw]

Here is a possible query to do what you want [I have not tested this]

Update tblMain Inner Join tblMonthly ON
tblMain.Customer = tblMonthly.Customer AND
tblMain.Brand = tblMonthly.Brand AND
tblMain.Month = tblMonthly.Month
Set tblMain.Actual = tblMain.Actual + tblMonthly.Actual

You want to Update the value in the Actual field of tblMain. I'm not sure how any particular Month's actual data already exists before you apply the values from the Monthly table.

What exactly is in the Month field? How fo you distinguish 1 year from another?

 

[Design by Sue]

Every month there are updates - my original table was created with October 2012 actuals in it (that is a whole other story - for this just know that there are existing October actuals and obviously in January there will be actuals in the table for October and for November). The month field is actually the first of each month with year, i.e. 10/1/2012, 11/1/2012 etc. It is just displayed as Oct 2012, Nov 2012. The use of this is only for the next 6 months so it's use will end next year.

I have the query that displays the info to be included and looking at your code, I believe it is the last line is what I am missing and that will make this work. I sure hope so! I will try it in the morning.

Thank you for your quick and hopefully successful response. I will be sure to confirm how it goes.

Sue

 

[Design by Sue]

This is so frustrating! I set up the sql as jdrew suggested. When I view the query, only the Main.Actual is in the results and if I run the query, all records in the actual column are set to 0.

I have set up a query to display all results joining the 2 tables with a column for both Main.Actual and Monthly.Actual and another column to add the 2 together - but there are no results in that column. Both Actuals are set as numbers. The expression I am using to add the 2 together is Expr1: [Main]![ACTUAL]+[Monthly]![ACTUAL], which I set up using the build feature. I obviously can't get this query to work properly if I can't even get a column of the totals of these two columns.

What the heck am I doing wrong here? Anyone have any ideas?

Thanks
Sue

 

[jdraw]

What happened when you ran this query --- step by step? Any errors?
Can you set up a sample case using only 1 month, and some mocked up dat to see if the query will work?
Are you sure your data is OK?
Can you provide some sample data in csv format for each table?

Do you have a PK or unique index in your tables?

 

[Design by Sue]

I got a little further - because some fields are blank or 0 I now am using the following to get the totals


CInt(Nz([Master]![ACTUAL],0)) + CInt(Nz([Monthly]![ACTUAL],0))

BUT now in a few of the records I am getting #Num! and I can't figure out what is causing this.

Sue

 

[Design by Sue]

I am making progress (?) or at least I see the pattern. It is giving the #Num! on number larger than 32544. So has to be a formatting error somehow. I have the fields in both tables as long integer. Again, what am I missing here?

Sue

 

[jdraw]

I have mocked up a tblMain and a tblMonthly. I used a slightly modified version of the Update query I posted earlier.

UPDATE tblMain INNER JOIN tblMonthly ON
Month(tblMain.MyMonth) = Month(tblMonthly.MyMonth) AND
 (tblMain.Brand = tblMonthly.Brand) AND
 (tblMain.Customer = tblMonthly.Customer)
 SET tblMain.MyActual = tblMain.MyActual + tblMonthly.MyActual;

NOTE: I only used 2013 data so I have not considered Year in the query

I have attached 3 jpgs showing the modified query using the Month function and the tables with data before running the update; the table designs ( changed the name of the fields to not have reserved words Month and put a leading My on other fields); and the tblMain after running the Update query.
I hope this is helpful.

 

[Design by Sue]

Thank you jdraw, but please see my other posts. I have tried what you posted and the results are only what is in the main actual records, it does not add the Montly actuals to it and when I run it the actual records are blank.

I was able to create a column in the query by using the following that does merge the columns as I need but gives an #Num! error when the numbers are over 32500. AND I am not sure how to update the table using the resulting records.

= Master.ACTUAL + Actual.ACTUAL

I do appreciate your help but the "easy" method that you are giving me is just not working. Any other thoughts using what I have said here?


Sue

 

[jdraw]

Did you see the query and go through it along with the jpgs?
I did the additions and showed you the results.

I think your problem may lie in the fact that you have NOT put a default value of 0 on your numeric fields in your tables. You probably have NULLs and must dela with them explicilty if you do not put a 0 in the "empty field" at the start.

You could update those fields in your table replacing Null with 0.

The query I showed and the examples I have provided, deal with your issue.

As I recall Int is only valid up to 32767. (But it's been a long time so that may be wrong).

I am attaching another jpg where I upped the values in the tblMain and tblMonthly.

 

[Design by Sue]

I found the error in my code for the formatting of the totals, I in error had it set for CInt not CDbl (I am learning as I go!):

CDbl(Nz([Master]![ACTUAL],0))+CInt(Nz([Actual]![ACTUAL],0))

is the correct and now I do not have any #Num! errors.

Thanks again - yes I did look through and I understand the process and know that it should work but isn't at this point. I am sure that you are correct that is has to do with Nulls instead of zeros, and that is why I need the Nz code to make it work. I'll get back to you with the results of your suggestion.

Sue

 

[jdraw]

Yes you use NZ to deal with Nulls. But as a "best practice" you might want to always assign a default of 0 to a numeric field in a table.

Do you have your tables and relationships defined? Can you post a jpg of that?

Glad you have the issue sorted out.

 

[Design by Sue]

Not quite sorted out - but getting closer - is there an easy way to assign 0 to the blank fields in the table - there are 6000 of them and I don't want to sit here and type 0 in each?

OR do you know how to add my code to the update query?

Sue

 

[jdraw]

We're talking tblMain Forecast and Actual, right?

If so, I'd run 2 queries

Update tblMain
Set Forecast = 0
Where Forecast Is Null

Update tblMain
Set Actual = 0
where Actual Is Null

It is often a good practice to make a copy of your table before doing mass updates or mass deletes ---Just in Case.
Is it possible that you have NULL values in the tblMonthly?

 

[Design by Sue]

OK - I updated the table so there were no null entries and tried the query you wrote and it still removed previous actuals and enters only the current ones!

I am able to get the results I need in a column in the query by using the code CDbl(Nz([Master]![ACTUAL],0))+CInt(Nz([Actual]![ACTUAL],0)) so I must be able to accomplish this.

Is there someway to put that coding into an update query? Or put it in the coding of a button. I tried to base another update query on the column in this original (merge) query, but that created almost 2 times the number of records??


I am getting so desperate now to get the done -the deadline for this job has past several days ago! You can't imagine my frustration to be able to get the results I need but not be able to change the table as needed.! I would even be happy to create a new table with the correct information and delete the old one - anything.

Thanks so much for your help.

Sue

 

[jdraw]

??? I'm not sure why things aren't working???

If NZ is giving you a result, that implies a NULL value, so the update where forecast is NULL should work??? There must be something else going on.

Look at your tblMain design how are Forecast and actual defined?
Is there a default value? Changing to default 0 now, will only apply to new records.

Are you fields defined number long integer?

Try doing a select query looking for Len(Forecast) = 0 I don't see why it should be that but there are many things that could exist when a field appears empty

Null, zero length string.

At this point it's best to find out what value is in the "empty" field.

 

[jdraw]

I'm heading out for the rest of the afternoon. Will check back this evening.

 

[Design by Sue]

Thanks jdraw - just to update you I used the query to create a new table - then deleted Master and changed the name of the new file to Master. There is an error message that tells of a type conversion failure - which is probably what is messing up your code from working. I verified to the best of my ability that nothing was missing and for the life of me I can't figure out what is causing this. The piece of this that I am sure is causing the problem is that the file Actual table is being imported from Excel and from all of my research it seems that can cause these problems. At this point I have something that is at least working - can try to fix this later, but now I have to move on to another job. If you have any other ideas, let me know.

Thanks
Sue

 

[jdraw]

If you are importing something directly from Excel, and you are sure that's the source of an error, then import to a temporary table. Create a proper designed table in Access according to the data types you need. Then, via an Append query move the data from the temp table into the properly designed table. You may need some fucntion/formatting to get rid of the conversion failure.

You could post a dumbed down (no confidential info) version of your database and tell us specifically what is WRONG and WHERE we should look.

 

[Design by Sue]

I truly appreciate that and will look at it again tomorrow. Sometimes I need to sleep on these things, as also I had some other stuff I HAD to get done. Will post tomorrow if I find the answer - or a dumbed version.

Sue