Replacing one line of data with multiple lines

[btardo01]

Good afternoon:

I have a table, called Data1. Within this table exist raw paroll data but the data is consolidated by certain segments.

I have a 2nd table, called Payroll Detail. Within this table exist the detail payroll data by employee by segment that totals line consolidated line by segment in Data1.

I'm trying to create a query or something that allows me to combine the two sets of information. I need to include some data that is in Data1 and not in Payroll Detail table. However, instead of my results showing the consolidated line by segment, I want it to match up some criteria and give me the detail by employee by segment from Payroll Detail table. So in other words, I want it to replace one line from one table with several lines from the 2nd table.

I'm very new to Access......can someone help me with this, please?

thank you
Brian

 

[Joe4]

For which query?
Are you trying it on the exact same database (and data) you posted up for downloading?

 

[btardo01]

It's on the third query that returns the desired output. It's not the same exact file that I posted. The one I am using has a lot more records and I couldn't post because of very sensitive data. The database that I am using doesn't have records on everyline that are in format SW_PR_xx.xx.xx so when you run the Jan 2011 query, the payroll_date field sometimes returns an error

 

[Joe4]

You just need to exclude all the records that result in that error from the "JAN 2011 Query". If you only want to include all the records that begin with "SW_PR_", you can do so like this:

SELECT DateSerial(Right([Document Header Text],2),Mid([Document Header Text],7,2),Mid([Document Header Text],10,2)) AS Payroll_Date, [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].*
FROM [JAN 2011 SAP KSB1 400000 899999 SW_MGMT]
WHERE Left([Document Header Text],6)="SW_PR_";

I am logging off for the night now.

 

[btardo01]

Much thanks for all your time and patience.

I am logging off as well for the night

In regards to your last comment, In my final output, whenever it finds an error, I just want it to return the record as is. When it doesn't find an error, that's when I want it to give me the output of the 4 lines as opposed to two

 

[btardo01]

I've been working since early this morning and making good progress. In my output query, I need to leave in the records that don't have SW_PR as they are. With the last bit of code you gave me, everything is working except it is eliminating anything without SW_PR.

I understand why it is doing that (because you told it to with this statement):

WHERE Left([Document Header Text],6)="SW_PR_";

but when Left([Document Header Text],6)<>"SW_PR_";

I need it to leave the record as is. I'm not sure what it would then put in the payroll_date field, but I need those other records still.

thanks

 

[Joe4]

First, you must understand that excluding them from your query will not remove them from your table - they will still be there.

It doesn't really make sense leaving them in your final query, because you final query is a join between two other queries based on Element and Payroll Date. If you don't have a Payroll Date to join on for these records, they won't be returned in the query anyway (no matches!).

 

[btardo01]

Right, I understand that excluding from query doesn't delete from my tables.

I have to leave those other records in my final query. Is there no way to do so. The reason being is that my data is a huge dump from our system and I am trying to develope a very detailed income statement for analysis.

The huge dump of data contains all sorts of lines of information. However, there are a few areas where they detail is left out. One being Payroll. I am able to get the payroll detail from another file, hence; our current discussion of trying to "replace" the not so detailed lines in the huge data dump with the detail by employee via a query.

I still need all the other lines to put the rest of my detailed income statement together.

Any ways to accomplish this?

 

[Joe4]

When writing queries, it is important to understand the different JOIN methods you need to use.

The default is an INNER JOIN, in which you only return matched records.
Then there are LEFT and RIGHT OUTER JOINS, in which you return ALL of the records from one of your tables/queries, and then just the data that matches from the other one (see here for more on joins, specifically the three I have mentioned: http://en.wikipedia.org/wiki/Join_(SQL).

So, it sounds to me like maybe you want a LEFT OUTER JOIN here, where you want to return ALL of the records from your Payroll table, and just the matches from this other table. Is that correct?

Now all these records that cause these errors, what do their values look like?
Do they all follow the same standard format?
Can you provide a few examples?

 

[btardo01]

I'll take a look at that article you sent me. But check this out. We are getting closer. I've been working on the query and now have the following code:

SELECT [JAN 2011 Query].*, Payroll!DebitAmount-Payroll!CreditAmount AS Total, [Sag-SAP Mapping Payroll Query].EmployeeName
FROM [JAN 2011 Query] INNER JOIN [Sag-SAP Mapping Payroll Query] ON ([JAN 2011 Query].Payroll_Date = [Sag-SAP Mapping Payroll Query].PayrollDate) AND ([JAN 2011 Query].[Cost Element] = [Sag-SAP Mapping Payroll Query].GL)
WHERE (((Left([Cost Center],3)=Left([Payroll]![GLAccount],3))=True) AND ((Right([Cost Center],3)=Right([Payroll]![GLAccount],3))=True));

The only problem with this code is that it does not return records from my table called "JAN 2011 SAP KSB1 400000 899999 SW_MGMT" that do not start with "SW_PR_" in the "document header text" field.

The records that cause error could be anything that doesn't begin with "SW_PR_" in the field document header text. When it gets to one of these records, I just want it to return the record as it sees it.

 

[Joe4]

The only problem with this code is that it does not return records from my table called "JAN 2011 SAP KSB1 400000 899999 SW_MGMT" that do not start with "SW_PR_" in the "document header text" field.

Of course not! As I said, If you have an INNER JOIN, it will only return records where both sides have a match. Nothing matches to an error.

You need to change your join to a LEFT OUTER from the table that you want to include ALL the records from.

If you read that article I referenced on JOINS, it should make sense.

However, if you have an error in the field you are linking on, you are probably still going to have problems until you address that. I was trying to ascertain from you what this records look like. You may need to convert the format of the Date field to Text in both queries so you can avoid the errors and join them, like this:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
New calculated field in "Jan SAP 2011 Query"
MyPayrollDate: Mid([Document Header Date],7,10)
<o> </o>
New calculated field in "Payroll Query"
MyPayrollDate:Format([PayrollDate],"mm\.dd\.yyyy")
Now join your two queries on these fields instead of the other date fields.