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

 

[btardo01]

I don't think that will work. Here is a random sample of some other values in the document header text field:

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>JAN 2011 SAP KSB1 400000 899999 SW_MGMT</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>Document Header Text</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>SW_PR_01.21.11</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_Accrued PR 1.2011</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_PR_01.07.11</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_PR_01.21.11</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_Accrued PR 1.2011</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>MPS Allcoation</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>MPS Allcoation</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_PR_01.07.11</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_PR_01.21.11</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>I/C K. Price to NW</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>SW_Accrued PR 1.2011</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>MPS Allcoation</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>MPS Allcoation</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Wouls it be a better idea to now create a query from table, JAN 2011 SAP KSB1 400000 899999 SW_MGMT, to produce all results where it doesn't find "SW_PR_".

Then join that query with my current query that is producing only the SW_PR_ results?

what do you think?

 

[Joe4]

I don't think that will work.

Why not?
Did you try it?

If you create the new JOIN fields like I suggested, and change your join in the final query to a LEFT OUTER JOIN from the "JAN SAP QUERY" to the "Payroll Query", it should work.

 

[btardo01]

Sorry didn't mean to say, It won't work. I think I had another thought in my head and typed it without realizing it....(I am no where near the point where I can tell you something won't work).

Anywhere, here is where I am now with my queriries:

SELECT Mid([Document Header Text],7,10) AS MyPayrollDate, 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];

SELECT Format([PayrollDate],"mm\.dd\.yyyy") AS MyPayrollDate, Mid([GLAccount],5,6) AS Element, Payroll.*
FROM Payroll;

SELECT Mid([GL#],5,6) AS SagElement, [SAP Map].GL
FROM [SAP Map]
GROUP BY Mid([GL#],5,6), [SAP Map].GL;

SELECT [Sagitta to SAP Map query].GL, [Payroll Query].MyPayrollDate, [Payroll Query].Company, [Payroll Query].Division, [Payroll Query].Department, [Payroll Query].EmployeeName, [Payroll Query].EmployeeId, [Payroll Query].EmployeeType, [Payroll Query].GLMajor, [Payroll Query].GLSub, [Payroll Query].PayTypeCode, [Payroll Query].PayTypeDescription, [Payroll Query].DebitAmount, [Payroll Query].CreditAmount, [Payroll Query].GLAccount
FROM [Sagitta to SAP Map query] INNER JOIN [Payroll Query] ON [Sagitta to SAP Map query].SagElement=[Payroll Query].Element;

The records that I want to report in my final output that don't have the SW_PR_ should come from the Jan 2011 query or the JAN 2011 SAP KSB1 400000 899999 SW_MGMT table. Final query is below and is returning no records...????

SELECT [JAN 2011 Query].*, Payroll!DebitAmount-Payroll!CreditAmount AS Total, [Sag-SAP Mapping Payroll Query].EmployeeName
FROM [JAN 2011 Query] Left Outer JOIN [Sag-SAP Mapping Payroll Query] ON ([JAN 2011 Query].MyPayrollDate = [Sag-SAP Mapping Payroll Query].MyPayrollDate) 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));

 

[boblarson]

Just a quick chime in here - the syntax is LEFT JOIN (not Left Outer Join).

 

[btardo01]

Thanks for that. As I'm sure you can tell and Joe4 will attest to the fact that I am pretty hopeless right now. (but I gurantee once this project is over and I have time to go back and follow his advice, I'll pick it up).

I made the correction and still get no output.

 

[Joe4]

I have tried very hard to help you out, but it is extremely difficult to try to teach someone how to do something in a language (program) that they are not familiar with. And these forums are really designed to help people with specific problems, but not really teach someone how to use the program (some sort of "basic" knowledge level is assumed), or to do projects for them.

As such, I think you may be better served at this point by referring to a consultant. MrExcel does offer consulting services (see the bottom of the page), as do other individuals (some members of this site have links in their signatures).

 

[btardo01]

Although you pretty much were guiding me through my project step by step, I assure you my intention was not to free-load off you. thats not who I am. I was just in a bind and need a lot more assitance that I thought.

Anyway, thanks for the help you did provide............

 

[Joe4]

I assure you my intention was not to free-load off you. thats not who I am.

Don't worry. I never thought that. I think that it is just something that ended up being a lot more involved and difficult than you first thought. Computer programming is often like that.​

Access is one of those programs that can really cause inexperienced users a lot of grief. When I first starting using it years ago, I dove head-first into a big project. I knew how to use Access for some basic things (comparing data lists), but didn't have a full understanding of Access, relational databases, or the Rules of Normalization. After months of banging my head on the wall and programming myself into corners, I took some classes and picked up some books on the subject. Needless to say, I had to scrap months of work and start over from scratch. The final database turned out well in the end, but I wasted months by trying to do too much with too little knowledge.

Hope it all turns out well for you.​