Query data from a single table column (1 Viewer)

Stuart H

New member
Local time
Today, 08:50
Joined
Apr 9, 2020
Messages
3
Hi all,

A quick introduction from me. I've been a member for 5 months but not yet posted. The help on the threads has been more than enough to answer everything I have needed up to now and is greatly appreciated.

I am asking for a little help on a query I am running. It uses half a dozen tables from a commercial HR software database. However, there is one table that contains two columns, a Field ID column and a Field Text column. The Field ID is numbered 1 - 100 corresponding to different text in the Field Text column.

I am trying to query two text fields which are employee reference numbers onto a single row in the query results. Each row is employee data and I would like the two text fields next to each other. At the moment I run the query and also a report from the software and V-Look-Up in excel.

I have tried a couple of options which references the Field ID and although this does extract the information, it puts it on two rows. Would anyone have any suggestions as how to manage this? Happy to post screenshots if that helps.

Thank you for any help.
Cheers
Stuart
 

plog

Banishment Pending
Local time
Today, 03:50
Joined
May 11, 2011
Messages
11,611
Demonstrate your issue with data. Provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data your query should produce when you feed it the data from A.

Again 2 sets of data, starting and expected results.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
21,357
Hi Stuart. Welcome to AWF!

Have you tried adding the lookup table twice in your query?
 

Stuart H

New member
Local time
Today, 08:50
Joined
Apr 9, 2020
Messages
3
Thanks for the response, I'll give the lookup table a go. I have added a couple of images from Access as Plug suggested

AE Table shows the FieldID and Text Value columns I'm trying to query​
Results shows the two lines per employee. Ideally I would like one line per employee that has the EMP number followed by the CS​
Design is how it looks just to pull up both for demonstration​

Hopefully this helps. Thanks again for the response.
 

Attachments

  • AE Table.JPG
    AE Table.JPG
    47.5 KB · Views: 173
  • Design.JPG
    Design.JPG
    18.1 KB · Views: 343
  • Results.JPG
    Results.JPG
    58.2 KB · Views: 156

plog

Banishment Pending
Local time
Today, 03:50
Joined
May 11, 2011
Messages
11,611
I'm lost. I asked for 2 sets of data, you gave me 4 and I am pretty sure none of those are the B data I requested. The ones definitely don't tie back to the A data--there's more fields in the expected results than what you are starting with (Surname, STartDate, etc.)--its impossible for results to create data. So, one more time:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data your query should produce when you feed it the data from A.

Again 2 sets of data, starting and expected results.
 

Stuart H

New member
Local time
Today, 08:50
Joined
Apr 9, 2020
Messages
3
Thanks plog, I'm not exactly sure what it is you need, probably down to my inexperience with access / sql. I did accidently upload two sets of screenshots, apologies.

I'll leave it there thanks and see if I can find it somewhere else.

Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
21,357

plog

Banishment Pending
Local time
Today, 03:50
Joined
May 11, 2011
Messages
11,611
My instructions are pretty clear: what data you are starting with and what data you expect to end up with based on that starting data.

I need to know who had the most sales in March:

A
salespersonID, salesDate, salesTotal
1, 3/3/2020, 17
1, 3/3/2020, 18
2, 1/1/2020, 25
2, 3/3/2020, 30
3, 3/3/2020, 16
3, 3/3/2020, 16
4, 5/5/2020, 99

B
salespersonID, Totalsales
1, 35

Starting data, expected results
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:50
Joined
Feb 19, 2002
Messages
42,970
Joining multiple tables can result in a Cartesian Product and that is likely what is happening in this case. At least one of your tables has multiple rows that match on the join and that is producing the additional rows. You can get rid of this if you identify the table with the multiple matches and summarize it in a query and then join to the query.

To identify the table that has the multiple rows, start with a new query. Add only the first two tables. Join them and select the columns you want. Run the query. Does it return duplicates? Yes, figure out which table has the duplicates. No, add the third table. Are there duplicates? Keep doing this until you locate the table.
 

IbrBadri

Member
Local time
Today, 10:50
Joined
May 24, 2020
Messages
35
Hi

Stuart H

I see in the Design pic, you have four columns in your query, go to the column Number 5 which is empty column in the query, then in the Property Sheet, try change Unique Value or Unique Records to Yes, then one of it will solve the duplicate data for you.
 

Users who are viewing this thread

Top Bottom