Solved Creating a query with 1 table and 4 queries adds a new record

[dullster]

I am building a query to show all payroll taxes for Federal, State, Social Security and Medicare to match the Employee and ClientID. All 4 taxes are in to seperate queries that calculates the tax based on the pay period salary. I'm now combining the Employee table and all 4 queries for taxes. Everytime I add a query with the tax, it adds a new line with the same data as the previous line.

Attached is the Query design with the results. How do i get it to stop listing another line when i add the next tax query?

 

[Pat Hartman]

Your relationships are wrong. Relationships are PK to data field. Yours are data field to data field so who knows what random rows are getting included. The joins should be EmployeeID to EmployeeID in each query.

Another comment - If you are going to hard-code the taxes, you might as well add the four fields to the same table. I don't know what you are using the recordset for. If it is to fill out a form, the taxes should be a subform. That way if a new tax comes along, you don't have to change everything, you just add a new tax type and that creates a fifth row when you produce the report.

If you are using the recordset to export data and you want the whole "record" to be on a single row, use a cross tab to pick up the tax pieces.

 

[dullster]

I have the taxes all in the same table Federal taxes for Single and Married, State taxes for Single and Married, Social Security for Married and Single, Medicaid for Married and Single. So I need to add a TaxID for each Employee for each tax to the Employee Table?

 

[plog]

First is advice you should learn in general, but not apply in this particular case:

Divide, isolate and conquer. You've got 5 datasources in your query any of which could be the offending source with "duplicates". So, narrow it down to find out which one the culprit actually is. Start with just tblEmployees and use criteria to isolate to just 1 client/employee/whatever. If you have duplicates when you run it, then tblEmployees is the problem. If not, add a second datasource and rerun, if duplicates its the second datasource's fault. Then do the third like that, then the fourth and then the last one. Whnever you find duplicates you'll know which datasource is at fault and can dive deeper into it to find out why.

Second, you don't need to do that because your premise is wrong. As Pat pointed out you've structured this thing incorrectly. We are only able to glimpse into 1 table and 4 queries. You can't really assess much by looking at queries because you don't know what data goes into them. But I do know that when you have so many queries with similar structures there's probably a better way to get that data into just one query.

Lastly, the one table we can see, tblEmployees, doesn't look right. If DemoClientID is in tblEmployee there's no need for ClientTypeID and ClientTitleID because those most likely belong in the Client table. It's also usually incorrect to use a text field as an ID field. DemoClientID should be numeric, not someone's name. Lastly, why doesn't tblEmployee hold any real information about the employee? No name, no contact info, nothing but marital status.

 

[dullster]

Thank you. You are right. I'm cleaning it up

 

[dullster]

I have cleaned up the Employee table.

Now I want IIf ([tblEmployees].[MaritalStatus]=[tblpayrolltaxes].[MaritalStatus] AND [tblpayrolltaxes].[MyUCA]=21512, [tblpayrolltaxes].[TaxID])

I can't get it to work

 

[plog]

Define "can't get it to work"?

 

[dullster]

It wouldn't return the ID value. I believe I found another way. As always, I appreciate all your help.