Complex Query?

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I'm looking for an easier way of setting up a query in Access. I know how to
do the following in Excel, but I don't know how to do it in Access and I am
trying to imrpove my Access skills. Basically, if I have a list of data as
such:
Job001 C034
Job002 D010
Job002 D011
Job002 D010
Job003 C039
Job004 B009
Job005 P045
Job006 C047
Job006 C046
Job007 P033
Job008 C034
Job008 C008
Job009 C015
Job009 D016
Job009 C071
Job010 JW0027
Job011 BP1320
Job011 BP1320
Job011 BP1320
Job012 CB2260
Job013 EH0720
Job014 BP1350
Job014 DI0470
Job015 CF1430
Job016 BF1220
Job017 EI0110
Job018 PZ0810
Job019 PM1140
Job020 CD0450

How do you make it appear as such:
Job001 C034
Job002 D010 D011 D010
Job003 C039
Job004 B009
Job005 P045
Job006 C047 C046
Job007 P033
Job008 C034 C008
Job009 C015 D016 C071
Job009
Job010 JW0027
Job011 BP1320 BP1320 BP1320
Job012 CB2260
Job013 EH0720
Job014 BP1350 DI0470
Job015 CF1430
Job016 BF1220
Job017 EI0110
Job018 PZ0810
Job019 PM1140
Job020 CD0450

Basically, all the data is 'consolidated'. In Excel is is somewhat
difficult to do, but I suspect it is quite easy with an Access query, right.

Regards,
Ryan---
 
You are asking how to make data in a relational database look like a
spreadsheet.
Can you explain what it is you are trying to do?
Where do you intend to present this data (form, report, etc)?
 
It's just for my own learning experience. I bought an Access book last week
and learned how to make combinations of data using an Access query. It is
extremely easy in Access is extremely complex in Excel. I thought there
would be an easy way to combine the data in the list using a query, but I
have not been able to figure it out yet..... Right now, the data in the
first column (Job001) is in one table and the data in the second column
(C034) is in another table. I tried various combinations of queries but
could not get the desired results...

Any suggestions would be appreciated.

Regards,
Ryan--
 
Right now, there is no relationship. I think that's the key to this thing...
I tried a few things like inner join, left join, right join, but nothing has
worked so far. I suspect Access can do it...I just don't know how to do it...
 
There needs to be a field common to both tables that you can use to determine
which records in table 2 belong to which record in table 1.
 
My query is set up with the following Join Property:
Include records form Table1 and only those records from Table2 where the
join fields are equal.

I get the results below:
Table1.Field1 Table2.Field1
Job001 C034
Job002 D010
Job002 D011
Job002 D010
Job003 C039
Job004 B009
Job005 P045
Job006 C047
Job006 C046
Job007 P033
Job008 C034
Job008 C008
Job009 C015
Job009 D016
Job009 C071
Job010 JW0027
Job011 BP1320
Job011 BP1320
Job011 BP1320
Job012 CB2260
Job013 EH0720
Job014 DI0470
Job014 BP1350
Job015 CF1430
Job016 BF1220
Job017 EI0110
Job018 PZ0810
Job019 PM1140
Job020 CD0450

I can tell the the relationships are correct, but I thought the data may be
displayed differently, like this:
Job001 C034
Job002 D010 D011 D010
Job003 C039
Job004 B009
Job005 P045
Job006 C047 C046
Job007 P033
Job008 C034 C008
Job009 C015 D016 C071
Job009
Job010 JW0027
Job011 BP1320 BP1320 BP1320
Job012 CB2260
Job013 EH0720
Job014 BP1350 DI0470
Job015 CF1430
Job016 BF1220
Job017 EI0110
Job018 PZ0810
Job019 PM1140
Job020 CD0450

Maybe what I am proposing can't be done.


Dave, thanks for the help,
Ryan--
 
Great minds really DO think alike. That's one of the first things I tried.
The output is slightly different, and useful on some level, but not exactly
what I was looking for. I guess I got my answer.

Thanks for the help!!
Ryan--
 
Another way would be tedious and probably not very fast, but you could use
recordset processing to put the data into a temporary table.
 
That looks like concatenation.

A possible solution involving only SQL and a temp table is like this: Create
a temp table, temp, two fields, one which holds the key (your first field),
and one that will hold your concatenated result. You can use something like:


---------------
SELECT DISTINCT job, iif(false, "", null) AS concat INTO temp
FROM yourTableName
---------------

assuming the table temp does not already exists. I also assumed the first
field name is

Next, run the following update query, once:


----------------------
UPDATE temp INNER JOIN yourTableName
ON temp.job=yourTableName.job
SET concat=(concat + ", ") & yourTableName.SecondFieldName
---------------------


The result is then in the table temp.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top