Query for fetching records similar to 'Concatenated Key' function in Excel (1 Viewer)

harshadborde

Registered User.
Local time
Tomorrow, 01:16
Joined
Apr 10, 2017
Messages
17
I am working on a project of excel transformation. In excel we have columns Emp Id (A), Project Id (B), Project Role (C ), Location (D) and Salary. We have salary almost all a employees. But for those we do not have we calculate salary taking average of B,C,D / C,D / D. We want only one salary per combination of ABCD.
e.g. John working in Project Id X as Manager in London will have salary Y. If his project id is missing then we will calculate his salary based avg of C and D.

I designed 3 separate queries to calculate salary and but facing problem of duplicate records.

Any help in design/ logic and query will be much appreciated.
 

MarkK

bit cruncher
Local time
Today, 12:46
Joined
Mar 17, 2004
Messages
8,178
Hi there and welcome to the forum.

You have posted this in the MS Access Queries section. Is that your intent, or should this be in the Excel section?
 

harshadborde

Registered User.
Local time
Tomorrow, 01:16
Joined
Apr 10, 2017
Messages
17
Hi there and welcome to the forum.

You have posted this in the MS Access Queries section. Is that your intent, or should this be in the Excel section?

we are moving all data from excel to access. SO need help in designing query as mentioned above.
 

MarkK

bit cruncher
Local time
Today, 12:46
Joined
Mar 17, 2004
Messages
8,178
Have you added a primary key to each row in your Access table? Also, with indexes in your Access table you can easily make it impossible for duplicate combinations of ABCD to exist. Then you don't have to worry about that problem going forward, because the table will test for and reject inserts that would create those duplicates.

What I would do is import the raw data from excel into a temp table in Access. Then create your final Access table with indexes, constraints, primary keys, and so on, and then insert the data from the temp table into the final table. In that way you can ensure the integrity of the data and be able to recover from and resolve errors.

But maybe you have already taken some of those steps???

hth
 

harshadborde

Registered User.
Local time
Tomorrow, 01:16
Joined
Apr 10, 2017
Messages
17
I have connected a live excel to the database. We are blending all the data by suing query. Once all fields are ready, we will create table.
 

Minty

AWF VIP
Local time
Today, 19:46
Joined
Jul 26, 2013
Messages
10,355
You can use a IIf statement in your query to either do the sums or use the salary fields as the value to update into your final table. Something like (pseudo code)
Code:
IIF([YourSalayrFIeld] Is Null, do the sums here, [YourSalaryField])
 

MarkK

bit cruncher
Local time
Today, 12:46
Joined
Mar 17, 2004
Messages
8,178
Once all fields are ready, we will create table.
I would move all the data into Access temp table. Then create the destination table with primary key and indexes. Then move the data inside Access-row by row--from temp table to final.
 

Users who are viewing this thread

Top Bottom