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]

I am on my way out the door now, so I just very quickly put this together to see if this gets you going in the right direction.

You should be able to create two temporary queries that would allow you to link these two tables together on "payroll date" and element.

For table 1, create a query and add this calculation would get your payroll date:
Payroll_Date: DateSerial(Right([Header],2),Mid([Header],7,2),Mid([Header],10,2))

For table 1, create a query and add this calculation would get your element:
Element: Mid([GL Acct],5,7)

Now, you can join these two queries in a new query on the Payroll_Date and Element fields, and return what you want.

 

[btardo01]

Thanks man for your help, but I'm struggling with your suggestion and moving forward. Would you view files on wikisend? If so, see below, please


Link is below.....here is little better description of what I am trying to do

I have two tables:

1) JAN 2011 KSB1 400000 899999 SW_MGMT
2) Payroll

I have a query called Data, which is what I want my out put to look like. However, when you run the data query right now, it simply returns 2 lines. I want it to return 4. The first table above contains consolidated payroll lines. The second table contains the details of what makes up those consolidated lines by employee. There are two payroll dates in play, 1/7/2011 and 1/21/2011. I want my data query to do the following:

1) I'd like Access to match "payroll date" in table two with the last 7 characters in " Document Header" from table 1 and know they are the same.
2) The 5th character of "GL Account" has to be >= 5 in order for it to be pulled into the data query.
3) Access would also use "cost element" in table 1 and match that field to the 5th - 10th character in table 2's "GL Account" field.
4) Access would also match the first 3 characters of "Cost center" from table 1 with first 3 characters of "GL account" in table 2 AND the last three characters of "cost center" in table 1 with the last 3 characters of "GL acct" in table 2.

So my output would look almost exaclty like the data query now, but instead of two lines that have a document header field equal to SW_PR_01.07.11 and SW_PR_01.21.11, it would have four lines, 2 lines for Mr. Smith for each payroll and 2 lines for Mr Al for each payroll date. But there is going to be more data and it will get more complex so I need it to use the above criteria when making matches.


Here is link

http://wikisend.com/download/516208/sample Detail.mdb

 

[Joe4]

Unfortunately, I do not have the ability to download files here at work (we are not allowed to download files).

But it still seems to me that my suggestion should get you started down the right path. Exactly what kind of problems are you having with it? What did you try?

When you have a big Access problem, it often is best to try to break it apart into multiple smaller tasks instead of trying to one big, convoluted task all at once, especially if you are not an experienced Access programmer.

 

[btardo01]

Well, I've actually created both queries as you suggested.

The payroll date query works except in table 1, not all of my header fields have the format SW_PR_XX.XX.XX. So any field that doesn't have that returns and error.

The cost element query also worked. I actually change the number of characters to pull from the Mid statement from 7 to 6 and am getting the correct output.

I'm not to sure how combining these queries would give help me replace the one line in table 1 with the multiple lines of detail in table 2 though.

 

[Joe4]

The payroll date query works except in table 1, not all of my header fields have the format SW_PR_XX.XX.XX. So any field that doesn't have that returns and error.

You should be able to use Criteria in your query to filter out these records.

I'm not to sure how combining these queries would give help me replace the one line in table 1 with the multiple lines of detail in table 2 though.

You are not going to actually "replace" the records in any table, you are just creating queries to return what you want from those tables. If you link the two queries you created together in a new query, it should create a "one-to-many" or "many-to-many" relationship to return all the records I think you want. Between those two queries, I believe that all the fields you need are there, so after joining the two queries, just select the fields from each that you want to display in your new query.

 

[btardo01]

Ok, I am pretty sure I understand your logic on how to make this work, but I don't know if I am knowledgable enough with Access to make it work.

I literally just started teaching myself Access this week. I've learned some pretty basic functions but thats it.

I understand that I am going to create a new query and return the output I want by joing tables. But I can't grasp how to tell Access that whenever it finds a record in my Table one that contains a header field in format, SW_PR_XX.XX.XX, to go into the payroll table and replace it with all the different records that fit the different criteria.

 

[Joe4]

That's one of the biggest things many Access newcomers have trouble understanding, is that most of the time you are NOT replacing or updating data in your tables, you are just using queries to return what you want/need. There is rarely a need to go back in and physically update the data in the underlying tables. Queries can be used just like tables for Reports, Forms, and Exports.

I should also caution you that Access is a bit different than Excel. It is a relational database program. It is very powerful, but not as intuitive as Excel, and therefore a has a much steeper learning curve. If you are looking to create involved databases, it is important to understand important concepts of Relational Databases and Data Normalization. Otherwise, you could end up programming yourself into a corner and experiencing lots of frustration (if you are just trying to do a simple query or two and not design a whole database, then those concepts aren't as critical).

Back to your problem at hand. You said that you were able to create the two queries, right? Can you change your Query from Design View to SQL View and cut and paste the code here? If so, I may be able to help you join them and return what you need.

 

[btardo01]

I agree 100% with your second paragraph and am quite certain that is my problem. I have no background with relational databases and your comment on Access not being as as intuitive as Excel is dead on. I usually pick on software very quickly but have had trouble getting past the very basics.

If I can get help building this project, I'll be able to study the steps and try to grasp the process better.

In regards to your request for the SQL:

Cost Element code:

SELECT Mid([GLAccount],5,6) AS [Cost Element]
FROM Payroll;

Payroll Date code:

SELECT DateSerial(Right([Document Header Text],2),Mid([Document Header Text],7,2),Mid([Document Header Text],10,2)) AS Payroll_Date
FROM [JAN 2011 SAP KSB1 400000 899999 SW_MGMT];

 

[Joe4]

I can see one thing that would be causing you difficulties. You need to add all the other necessary fields to your queries! Specifically, the ones you need to join the two queries, and the fields you want to return on your final output. Once you do that, then you should be able to join them and return what you need.

 

[btardo01]

I might have misunderstood your request. I started my output query and this is what I have:

SELECT [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Cost Center], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Cost Element], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Cost element name], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Valin repcur], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Offsetting acct no], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Name of offsetting account], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Document Header Text], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[User Name], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Posting Date], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Document Number], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Business Area], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Company Code], [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].Segment, [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Document type], [OpCo Lookup].OpCd, [LOB Lookup].LOB, IIf(Left([Cost Element],1)=4 Or Left([Cost Element],1)=9,"Revenue","Expense") AS Type
FROM [LOB Lookup] INNER JOIN ([OpCo Lookup] INNER JOIN [JAN 2011 SAP KSB1 400000 899999 SW_MGMT] ON [OpCo Lookup].Division = [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].[Business Area]) ON ([LOB Lookup].Segment = [JAN 2011 SAP KSB1 400000 899999 SW_MGMT].Segment) AND ([LOB Lookup].Division = [OpCo Lookup].Division);

But now I am at the point where I need to add the different criteria:

1) I'd like Access to match "payroll date" in table two with the last 7 characters in " Document Header" from table 1 and know they are the same.
2) The 5th character of "GL Account" has to be >= 5 in order for it to be pulled into the data query.
3) Access would also use "cost element" in table 1 and match that field to the 5th - 10th character in table 2's "GL Account" field.
4) Access would also match the first 3 characters of "Cost center" from table 1 with first 3 characters of "GL account" in table 2 AND the last three characters of "cost center" in table 1 with the last 3 characters of "GL acct" in table 2.

Just to reiterate, table 1 is the table that contains the records that some times have a document header in SW_PR_xx.xx.xx format. When it sees this format, I want it to go to payroll table and return the detail (using the criteria above) to match up. However, some of the records, don't have document header in SW_PR_xx.xx.xx format. When it doesn't see that format, I just want it to return that record as is.