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'll see if I can take a look at this tonight when I am at home, where I can download the files.

 

[btardo01]

Thanks man, I really appreciate it. I'm going to still work on it throughout the day because I truly want to learn it. However, I am trying to get it to a point where I can present to managment tomorrow morning so any help you can offer tonight would be greatly appreciated.

In meantime, can you tell me if you see anything wrong with this:

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]
where iserror(DateSerial(Right([Document Header Text],2),Mid([Document Header Text],7,2),Mid([Document Header Text],10,2))) <> True
 
Union SELECT [GLAccount]
FROM Payroll
where Mid([GLAccount],5,6) > 499999;

 

[btardo01]

In regards to that last post. When I execute, it tells me "criteria type mismatch"

 

[Joe4]

What do the GL_ACCOUNT values you are trying to remove look like?
Can you post a few examples?

 

[btardo01]

Sure, below is a small sample. They are in "text" format. So I assume it has something to do with trying to evaluate a text as value? I tried to modify with the below, but guess thats the Excel intuitive part coming out in me.

Union SELECT [GLAccount]
FROM Payroll
where Val(Mid([GLAccount],5,6)) > 499999;

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Payroll</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>GLAccount</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>310.2180000.000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.5600000.146</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.5400000.146</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.2180000.000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.1000001.000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.5400000.146</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.1000001.000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.1000001.000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.2180000.000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>310.1000001.000</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

 

[Joe4]

One problem I see is that it appears you are trying to do a UNION query. You can only do a UNION query if the structure of each query is similar.

I am not sure why you are trying to do a UNION query. I believe you should be using JOINs. Are you familiar with how to join multiple tables/queries in a new query. If not, please read up on it. It is one of the most basic principles of using Access (it is what makes it a relational database).

If you are not very familiar with Access and the concept of relational databases, you may be putting the cart before the horse and trying to do some things without understanding why or how they work. If that is the case, I would strongly recommend picking up an introductory book to Access and working through that first.

 

[btardo01]

I will defintely take your advice with the introductory book but for now I really need to get my project to the next level to present to mgmt so I might have to try to put the cart before the horse and get by.

 

[btardo01]

Good afternoon:

Did you get a chance to take a look at my sample?

 

[Joe4]

I had just logged in a little while after getting the kids to bed and getting a workout in.

So, looking at your data, the method I originally proposed will create four records.

First, I took your "JAN 2011 SAP..." table a made a new query out of it where I calculate the Payroll Date in date format like I described way back on page 1, and included all the fields. I named that query "JAN 2011 Query", and here is what that SQL looks like (just like you copied and pasted the SQL code from Access to this forum, you can go the other way and copy this code back into your Access in SQL View and change to Design View to see what it looks like:

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];

Then, I took your Payroll table and created a query that calculated the Element number and returns all the fields. I named this "Payroll Query":

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

Now, since both queries have a common Payroll Date and Element number, we can join these two queries in a new query on these two fields.

SELECT [JAN 2011 Query].*
FROM [JAN 2011 Query] 
INNER JOIN [Payroll Query] 
ON ([JAN 2011 Query].Payroll_Date = [Payroll Query].PayrollDate) 
AND ([JAN 2011 Query].[Cost Element] = [Payroll Query].Element);

If you run this query, you will see it returns four records, like you wanted. I returned all of the fields from the "JAN 2011 Query" in it, but you can pick and choose which fields you want to display. You can also link your two lookup tables, like you did in your example. I'll leave those two minor things to you, as it looks like you know how to do that part based on your sample database.

 

[btardo01]

thanks so much for your effots.

I'm having a little issue. I am copying and pasting exaclty as you have it but am getting a "data type mismatch in expression" error.

any ideas?