Complex Query?
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--- -- RyGuy
|
0 |
|
Utf
3/4/2008 7:07:02 PM
access.queries
6343 articles. 
1 followers.
10 Replies
808 Views
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)? -- Dave Hargis, Microsoft Access MVP "ryguy7272" wrote: > 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--- > > > -- > RyGuy
|
|
0 |
|
Utf
3/4/2008 8:00:01 PM
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-- -- RyGuy "Klatuu" wrote: > 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)? > -- > Dave Hargis, Microsoft Access MVP > > > "ryguy7272" wrote: > > > 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--- > > > > > > -- > > RyGuy
|
|
0 |
|
Utf
3/4/2008 8:29:03 PM
How are the two tables related? -- Dave Hargis, Microsoft Access MVP "ryguy7272" wrote: > 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-- > > > -- > RyGuy > > > "Klatuu" wrote: > > > 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)? > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "ryguy7272" wrote: > > > > > 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--- > > > > > > > > > -- > > > RyGuy
|
|
0 |
|
Utf
3/4/2008 8:35:00 PM
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... -- RyGuy "Klatuu" wrote: > How are the two tables related? > -- > Dave Hargis, Microsoft Access MVP > > > "ryguy7272" wrote: > > > 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-- > > > > > > -- > > RyGuy > > > > > > "Klatuu" wrote: > > > > > 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)? > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "ryguy7272" wrote: > > > > > > > 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--- > > > > > > > > > > > > -- > > > > RyGuy
|
|
0 |
|
Utf
3/4/2008 8:39:01 PM
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. -- Dave Hargis, Microsoft Access MVP "ryguy7272" wrote: > 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... > > -- > RyGuy > > > "Klatuu" wrote: > > > How are the two tables related? > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "ryguy7272" wrote: > > > > > 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-- > > > > > > > > > -- > > > RyGuy > > > > > > > > > "Klatuu" wrote: > > > > > > > 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)? > > > > -- > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > 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--- > > > > > > > > > > > > > > > -- > > > > > RyGuy
|
|
0 |
|
Utf
3/4/2008 8:43:00 PM
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-- -- RyGuy "Klatuu" wrote: > 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. > -- > Dave Hargis, Microsoft Access MVP > > > "ryguy7272" wrote: > > > 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... > > > > -- > > RyGuy > > > > > > "Klatuu" wrote: > > > > > How are the two tables related? > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "ryguy7272" wrote: > > > > > > > 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-- > > > > > > > > > > > > -- > > > > RyGuy > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > 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)? > > > > > -- > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > 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--- > > > > > > > > > > > > > > > > > > -- > > > > > > RyGuy
|
|
0 |
|
Utf
3/5/2008 8:48:00 PM
I think a Pivot Table might work, but I am not that familiar with using them. -- Dave Hargis, Microsoft Access MVP "ryguy7272" wrote: > 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-- > > -- > RyGuy > > > "Klatuu" wrote: > > > 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. > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "ryguy7272" wrote: > > > > > 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... > > > > > > -- > > > RyGuy > > > > > > > > > "Klatuu" wrote: > > > > > > > How are the two tables related? > > > > -- > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > 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-- > > > > > > > > > > > > > > > -- > > > > > RyGuy > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > 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)? > > > > > > -- > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > > > 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--- > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > RyGuy
|
|
0 |
|
Utf
3/5/2008 8:51:03 PM
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-- -- RyGuy "Klatuu" wrote: > I think a Pivot Table might work, but I am not that familiar with using them. > -- > Dave Hargis, Microsoft Access MVP > > > "ryguy7272" wrote: > > > 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-- > > > > -- > > RyGuy > > > > > > "Klatuu" wrote: > > > > > 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. > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "ryguy7272" wrote: > > > > > > > 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... > > > > > > > > -- > > > > RyGuy > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > How are the two tables related? > > > > > -- > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > 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-- > > > > > > > > > > > > > > > > > > -- > > > > > > RyGuy > > > > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > > > 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)? > > > > > > > -- > > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > > > > > 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--- > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > > RyGuy
|
|
0 |
|
Utf
3/5/2008 9:09:01 PM
Another way would be tedious and probably not very fast, but you could use recordset processing to put the data into a temporary table. -- Dave Hargis, Microsoft Access MVP "ryguy7272" wrote: > 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-- > > > > -- > RyGuy > > > "Klatuu" wrote: > > > I think a Pivot Table might work, but I am not that familiar with using them. > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "ryguy7272" wrote: > > > > > 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-- > > > > > > -- > > > RyGuy > > > > > > > > > "Klatuu" wrote: > > > > > > > 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. > > > > -- > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > 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... > > > > > > > > > > -- > > > > > RyGuy > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > How are the two tables related? > > > > > > -- > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > > > 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-- > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > RyGuy > > > > > > > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > > > > > 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)? > > > > > > > > -- > > > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > > > > > > > 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--- > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > > > RyGuy
|
|
0 |
|
Utf
3/5/2008 9:13:01 PM
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
"ryguy7272" <ryguy7272@discussions.microsoft.com> wrote in message
news:29FCF8A0-88E0-4FE7-943E-6DD7C145EABD@microsoft.com...
> 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---
>
>
> --
> RyGuy
|
|
0 |
|
Michel
3/6/2008 7:30:29 PM
Similar Artilces:
query question 12-05-07
Hi all, I need some help with this one not sure if it even possible. I have a table that has identifier number, transaction number, status, and comments field. In the 1st query I look in the comments field to find a specific date (using Like). That query shows me all that is Postperiod. What I need is for the preperiod records to show up in a query by themselves. I have tried to use NOT Like but this doesn't provide me with correct results. Anyone have any idea how I can do this? thanks in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acces...
Help with a query 04-21-07
First of all thank you for taking the time to read my question. I am having a problem running a query on a simple table. Trying to run a query on a table with a field called ENCOUNTERS and a field called CODES. What I'm trying to ask get out of the query is this: Every time an ENCOUNTERS field has a CODES value of 69210, I want it to return all other CODES results for that ENCOUNTERS number. On 20 Apr 2007 18:39:20 -0700, "scottpconnors@yahoo.com" <scottpconnors@yahoo.com> wrote: >First of all thank you for taking the time to read my question. I am >having a proble...
website prevents excel import web query
can it be that a website prevents import web query from importing data? this is the website http://www.capital.gr/xa/allstocks.asp and it seems that it imports everything but the stock prices... can that be? is there a way around this, thanks, S, ...
Problem with query dates
Hi My records date range in the database is Betwene 20/05/20010 and 22/05/20010 . When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the records less than this date, OK. But if I change the month, this date to 24/04/2010 00:00:00 shows the same records. That is even if I change the month the records that appear are always less than the 24 days without following month. There lies my problem. Note: My date style is dd-mm-yyyy and time is hh: mm: ss I really need to fix this.Seabra Dim Q1, SQL As String Dim DateTime1 As Date Dim ConnString As S...
Changing the Recourd Source (underlying query) of a report
Hi there, I'm trying to build a QBF form to generate numerous types of data. On my form "reportselection", I have a bunch of fields where selections "criterias" can be inputted by the user. The first one is a drop-down box listing the names of the reports available for viewing by the user. The user selects the report he/she wants to see. Based on the report chosen, certain fields will be made available (Visible) to the user. From the fields, the user will be able to make his query based on the criteria fields on the form. Press a button which previews th...
<>0 criteria in query does not work
I created a query that has many calculations. It all works fine until I enter the criteria "<>0" in one of the calculated columns. It then treats the columns that are the addends in that equation as if they were parameter queries. In other words, it asks me to input values for the two columns that I am adding in that column. If I take it a step back and input the <>0 on the criteria lines for those two columns that are added together, it than asks me to input values for the columns that are added together to calculate those columns. What am I doing wrong!!!!! ...
Query to create dates
I want to create a query that returns the dates of the next five Fridays. Is this possible? Simon Next Friday is: Date() - Weekday(Date()) + 6 If you want a query that gives you 5 records for the next 5 Fridays: 1. Create a table with 1 field of type Number, named (say) CountID. Mark the field as primary key. Save the table as (say) tblCount. Enter the records 0 to 4 (5 records all up.) 2. Create a query based on this table. In the Field row, enter: TheDate: Date() - Weekday(Date()) + 6 + 7 * [CountID] -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access u...
Look up combos in Query Criteria?
I am trying to build a database that narrows the record choices as different fields are selected. I believe that this would be a query, but whenever I build a query and select a field choice, the record in the main table changes. For example: Car Dealer enters car ID, make, model, and color into main database. I as a consumer, select make model and color, and the query lists only records that match. I would prefer a lookup box in the query, as we don’t want to rely on stuff being named correctly, or maybe a burgundy from the list, would suffice for a red preference. Keep in mind, ch...
Form with combo box to set criteria for query and report
Using Access 2003 a form has been created with a combo box to select the name of a practice. The criteria row in the query shows the form information. When the command button in the form is clicked to run the report the report runs with the labels appearing from the underlying query but does not show any of the detail information. What am I missing in the setup of the form or elsewhere? Basenji - It sounds like there are no records that match the criteria you are using. Post your SQL statement, and maybe we can spot the issue. To post the SQL, go into Query Design, then ch...
Query Criteria 04-28-10
I have a query where I am trying to get a count of items. The first column is: Less than 10: (IIf([Days in Queue]<10,"")) This gives a count for each queue that has been under 10 days and is working correctly. I cannot get the criteria to work for the # ranges after that. I want it to return the count for anything that has been in queue for 10 to 20 days. I know what I have below does not work. I need something for the =10-20 part. Right now I get all zeros returned. 10-20: (IIf([Days in Queue]=10-20,"")) Please help! -- Message posted via http://w...
#error in calculated query field
I can't figure this one out: I have a text field that contains either: 1. a double 2. a string beginning with a comparison operator followed by a double 3. null I have created two functions that will nicely convert either (1) or (2) above into two fields, the operator (if there is one) and the number. However, if the text field is null, I receive #error in both of my new fields. I have tried returning an empty string from the function that fills the operator field, or a null from the function that fills the numeric field, but I still get #error. THIS IS DRIVING ME NUTS!! Here are...
Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...
XPath queries with Namespaces
I am having trouble writing an XPath query that deals with namespaces. I've see the documentation that says that I have to use a XmlNamespaceManager and qualify my queries such that they match up with the definitions added to the XmlNamespacemanager, but I want to write a query that pulls all of the elements from my document that would otherwise match the expression--regardless of their namespace. Does anyone know how to do this? Thanks, Wells * Wells Caughey wrote in microsoft.public.dotnet.xml: >I am having trouble writing an XPath query that deals with namespaces. I've >...
stop returning column headings from DB query
I put together a query to go against Oracle that returns a block of data to the spreadsheet. Is there an option somewhere to have the "Return Data to Excel" do so without also returning the column headings? I want the data only. thanks - Russ There are 2 places that you can change that setting..... 1)When you return data from MS Query the dialog where you select the location for the data has a Properties button. Click that button UNcheck: Include Field Names.......click [OK] Continue selecting the query destination OR 2)After the data has been pulled from Oracle Right-click...
Add a line/row number in front of query results
How can a add a line number in front of the returned query results? Example: If a query returns 3 records: Dog Cat Mouse I would like to query to return the results with line numbers: 1 Dog 2 Cat 3 Mouse The query would add line/row numbers for the returned results. Thank you in advance, I have asked this question in the past but having a hard time in trying to explain this simple concept or maybe its not so simple. Michael Kintner RowNum Sample SELECT (SELECT COUNT(*)+1 FROM PERMISSIONS P1 WHERE P1.ID < P.ID) AS ROWNUM, P.* FROM PERMISSIONS P HTH Pieter "Michael Kint...
Using an Object from a Current form for Criteria in a Query
I am using Access 2000. I have a Command Button on a form that is designed to preview or print that customer's invoice. I use a Pop-Up Dialog Box. The end user will supply 2 pieces of data in this Dialog Box. This data, together with the Customer ID from the current form. will provide the 3 necessary pieces of info to pass on to the query, and eventually the report, that creates the invoice. I suspect that the "Me." or "Me!" "command" or "action" must be used, but am not clear about how to use it. Perhaps someone can give me so...
Query to join records according to "hierarchy"
My table is something like this: Fields: Name; Group John; 1-1 Carl; 1-1 Brian; 1-2 Charles; 2-1 Eddie; 2-2 Carmen; 3-1 Ellen; 3-2 ......and so on What I need is to combine the field Name in group 1-1 to 1-2, 2-2, 3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2. Is this possible? If so, how can I do it? The results from the query would be something like this: Name A Name B Name C Name D John(1-1) Brian(1-2) Eddie(2-2) Ellen(3-2) Carl(...
Passing a query parameter using code to feed a subform
Hello, Can someone please help with this issue I have in wanting to reuse a query that is the source for a subform that is used across my database by many main forms. The query is itself linked to a grouped query (Max of date) so it potentially has many records behind it. It basically retrieves the latest price of stock. I want to pass a parameter to this query (StockID) to a) speed up Access loading the form (because I believe that unless you have a MDE it "tests" your query on loading) and b) keep my queries to one per job. Should I pass the parameter to the final query (a selec...
Query formatting problem
I have a MS access database that keeps track of road segments for my work. The road segments are stored in a table (along with another table that stores project information) and I have a query that calculates length by subtracting the beginning MP from the end MP. The query is fine for the most part except for a few values that are not being calculated correctly. Here is the code: SELECT PROJECTS.Number_ID AS PROJECTS_Number_ID, PROJECTS.OO_NUM AS PROJECTS_OO_NUM, PROJECTS.PROJECT, PROJECTS.CO_NAME, PROJECTS.CO_NUM, PROJECTS.DISTRICT, PROJECTS.Date, PROJECTS.COMMENT, ROUTE_...
Complex Look Up (amended)
Hi. I desparately need help with this look up function The look up data is as below Sheet 1 ColumnA Column B Column C Start Date End Date Exchange Rate 26/10/09 25/11/09 1.123 26/11/09 25/12/09 1.654 26/12/09 25/01/09 1.897 Sheet 2 Column A Column B Date Exchange Rate 05/11/09 ________ 15/01/09 ________ 21/12/09 ________ OK, what I need to do is in Sheet 2, I need to look up for the dates in column A and get the loo...
please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...
Count or sum help on query
This is my SQL code for a query I'm running that is working great for my report. What I need now is that I need the total of Y's and N's for each column. So I need to know how to make my sql put at the bottom on report Total count for how many Y's for conductor casing,and how many N's, how many Y's for surface casing, How many N's, How many Y's for Prod casing, how many N's, How many CMPS Y's and how many N's and How many Wells on Prod Y's and N's count. Then my report will be complete. This is my code so far and my query is run...
Multiple If statements in the criteria of a query
I'm trying to instert the following in the criteria section of a query in query builder; the itself is the record source for a list box ******************************************************************************************************* If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation" Then Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _ "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]" Else: If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = &quo...
Query joining two tables with different dates
I'm sure there is a simple solution to this, but being fairly new to Access I would appreciate some assistance... I have a query that joins two tables. One table has call details for each day that an agent works. The other table has details for each call that is graded for each agent. However, calls can be graded on days that the agent does not work. When joining the tables, the query will only display calls graded on dates that the agent works. This is what I would like to do to correct this: If there was a call graded on a day that the agent did not work, this data be rolled...
Excel 2003 This Workbook contains queries to external data
Excel 2003 I get the following message box when opening an excel spreadsheet and displaying an extnernal microsoft query. This Workbook contains queries to external data that refresh automatically. Allows you to click either Enable automatic refresh or Disable automatic refresh. I know this is a new security enhancement and is turned on by default. How do you change the default to off so this does not appear every time ...
Hi all, I need some help with this one not sure if it even possible. I have a table that has identifier number, transaction number, status, and comments field. In the 1st query I look in the comments field to find a specific date (using Like). That query shows me all that is Postperiod. What I need is for the preperiod records to show up in a query by themselves. I have tried to use NOT Like but this doesn't provide me with correct results. Anyone have any idea how I can do this? thanks in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acces...
Help with a query 04-21-07
First of all thank you for taking the time to read my question. I am having a problem running a query on a simple table. Trying to run a query on a table with a field called ENCOUNTERS and a field called CODES. What I'm trying to ask get out of the query is this: Every time an ENCOUNTERS field has a CODES value of 69210, I want it to return all other CODES results for that ENCOUNTERS number. On 20 Apr 2007 18:39:20 -0700, "scottpconnors@yahoo.com" <scottpconnors@yahoo.com> wrote: >First of all thank you for taking the time to read my question. I am >having a proble...
website prevents excel import web query
can it be that a website prevents import web query from importing data? this is the website http://www.capital.gr/xa/allstocks.asp and it seems that it imports everything but the stock prices... can that be? is there a way around this, thanks, S, ...
Problem with query dates
Hi My records date range in the database is Betwene 20/05/20010 and 22/05/20010 . When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the records less than this date, OK. But if I change the month, this date to 24/04/2010 00:00:00 shows the same records. That is even if I change the month the records that appear are always less than the 24 days without following month. There lies my problem. Note: My date style is dd-mm-yyyy and time is hh: mm: ss I really need to fix this.Seabra Dim Q1, SQL As String Dim DateTime1 As Date Dim ConnString As S...
Changing the Recourd Source (underlying query) of a report
Hi there, I'm trying to build a QBF form to generate numerous types of data. On my form "reportselection", I have a bunch of fields where selections "criterias" can be inputted by the user. The first one is a drop-down box listing the names of the reports available for viewing by the user. The user selects the report he/she wants to see. Based on the report chosen, certain fields will be made available (Visible) to the user. From the fields, the user will be able to make his query based on the criteria fields on the form. Press a button which previews th...
<>0 criteria in query does not work
I created a query that has many calculations. It all works fine until I enter the criteria "<>0" in one of the calculated columns. It then treats the columns that are the addends in that equation as if they were parameter queries. In other words, it asks me to input values for the two columns that I am adding in that column. If I take it a step back and input the <>0 on the criteria lines for those two columns that are added together, it than asks me to input values for the columns that are added together to calculate those columns. What am I doing wrong!!!!! ...
Query to create dates
I want to create a query that returns the dates of the next five Fridays. Is this possible? Simon Next Friday is: Date() - Weekday(Date()) + 6 If you want a query that gives you 5 records for the next 5 Fridays: 1. Create a table with 1 field of type Number, named (say) CountID. Mark the field as primary key. Save the table as (say) tblCount. Enter the records 0 to 4 (5 records all up.) 2. Create a query based on this table. In the Field row, enter: TheDate: Date() - Weekday(Date()) + 6 + 7 * [CountID] -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access u...
Look up combos in Query Criteria?
I am trying to build a database that narrows the record choices as different fields are selected. I believe that this would be a query, but whenever I build a query and select a field choice, the record in the main table changes. For example: Car Dealer enters car ID, make, model, and color into main database. I as a consumer, select make model and color, and the query lists only records that match. I would prefer a lookup box in the query, as we don’t want to rely on stuff being named correctly, or maybe a burgundy from the list, would suffice for a red preference. Keep in mind, ch...
Form with combo box to set criteria for query and report
Using Access 2003 a form has been created with a combo box to select the name of a practice. The criteria row in the query shows the form information. When the command button in the form is clicked to run the report the report runs with the labels appearing from the underlying query but does not show any of the detail information. What am I missing in the setup of the form or elsewhere? Basenji - It sounds like there are no records that match the criteria you are using. Post your SQL statement, and maybe we can spot the issue. To post the SQL, go into Query Design, then ch...
Query Criteria 04-28-10
I have a query where I am trying to get a count of items. The first column is: Less than 10: (IIf([Days in Queue]<10,"")) This gives a count for each queue that has been under 10 days and is working correctly. I cannot get the criteria to work for the # ranges after that. I want it to return the count for anything that has been in queue for 10 to 20 days. I know what I have below does not work. I need something for the =10-20 part. Right now I get all zeros returned. 10-20: (IIf([Days in Queue]=10-20,"")) Please help! -- Message posted via http://w...
#error in calculated query field
I can't figure this one out: I have a text field that contains either: 1. a double 2. a string beginning with a comparison operator followed by a double 3. null I have created two functions that will nicely convert either (1) or (2) above into two fields, the operator (if there is one) and the number. However, if the text field is null, I receive #error in both of my new fields. I have tried returning an empty string from the function that fills the operator field, or a null from the function that fills the numeric field, but I still get #error. THIS IS DRIVING ME NUTS!! Here are...
Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...
XPath queries with Namespaces
I am having trouble writing an XPath query that deals with namespaces. I've see the documentation that says that I have to use a XmlNamespaceManager and qualify my queries such that they match up with the definitions added to the XmlNamespacemanager, but I want to write a query that pulls all of the elements from my document that would otherwise match the expression--regardless of their namespace. Does anyone know how to do this? Thanks, Wells * Wells Caughey wrote in microsoft.public.dotnet.xml: >I am having trouble writing an XPath query that deals with namespaces. I've >...
stop returning column headings from DB query
I put together a query to go against Oracle that returns a block of data to the spreadsheet. Is there an option somewhere to have the "Return Data to Excel" do so without also returning the column headings? I want the data only. thanks - Russ There are 2 places that you can change that setting..... 1)When you return data from MS Query the dialog where you select the location for the data has a Properties button. Click that button UNcheck: Include Field Names.......click [OK] Continue selecting the query destination OR 2)After the data has been pulled from Oracle Right-click...
Add a line/row number in front of query results
How can a add a line number in front of the returned query results? Example: If a query returns 3 records: Dog Cat Mouse I would like to query to return the results with line numbers: 1 Dog 2 Cat 3 Mouse The query would add line/row numbers for the returned results. Thank you in advance, I have asked this question in the past but having a hard time in trying to explain this simple concept or maybe its not so simple. Michael Kintner RowNum Sample SELECT (SELECT COUNT(*)+1 FROM PERMISSIONS P1 WHERE P1.ID < P.ID) AS ROWNUM, P.* FROM PERMISSIONS P HTH Pieter "Michael Kint...
Using an Object from a Current form for Criteria in a Query
I am using Access 2000. I have a Command Button on a form that is designed to preview or print that customer's invoice. I use a Pop-Up Dialog Box. The end user will supply 2 pieces of data in this Dialog Box. This data, together with the Customer ID from the current form. will provide the 3 necessary pieces of info to pass on to the query, and eventually the report, that creates the invoice. I suspect that the "Me." or "Me!" "command" or "action" must be used, but am not clear about how to use it. Perhaps someone can give me so...
Query to join records according to "hierarchy"
My table is something like this: Fields: Name; Group John; 1-1 Carl; 1-1 Brian; 1-2 Charles; 2-1 Eddie; 2-2 Carmen; 3-1 Ellen; 3-2 ......and so on What I need is to combine the field Name in group 1-1 to 1-2, 2-2, 3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2. Is this possible? If so, how can I do it? The results from the query would be something like this: Name A Name B Name C Name D John(1-1) Brian(1-2) Eddie(2-2) Ellen(3-2) Carl(...
Passing a query parameter using code to feed a subform
Hello, Can someone please help with this issue I have in wanting to reuse a query that is the source for a subform that is used across my database by many main forms. The query is itself linked to a grouped query (Max of date) so it potentially has many records behind it. It basically retrieves the latest price of stock. I want to pass a parameter to this query (StockID) to a) speed up Access loading the form (because I believe that unless you have a MDE it "tests" your query on loading) and b) keep my queries to one per job. Should I pass the parameter to the final query (a selec...
Query formatting problem
I have a MS access database that keeps track of road segments for my work. The road segments are stored in a table (along with another table that stores project information) and I have a query that calculates length by subtracting the beginning MP from the end MP. The query is fine for the most part except for a few values that are not being calculated correctly. Here is the code: SELECT PROJECTS.Number_ID AS PROJECTS_Number_ID, PROJECTS.OO_NUM AS PROJECTS_OO_NUM, PROJECTS.PROJECT, PROJECTS.CO_NAME, PROJECTS.CO_NUM, PROJECTS.DISTRICT, PROJECTS.Date, PROJECTS.COMMENT, ROUTE_...
Complex Look Up (amended)
Hi. I desparately need help with this look up function The look up data is as below Sheet 1 ColumnA Column B Column C Start Date End Date Exchange Rate 26/10/09 25/11/09 1.123 26/11/09 25/12/09 1.654 26/12/09 25/01/09 1.897 Sheet 2 Column A Column B Date Exchange Rate 05/11/09 ________ 15/01/09 ________ 21/12/09 ________ OK, what I need to do is in Sheet 2, I need to look up for the dates in column A and get the loo...
please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...
Count or sum help on query
This is my SQL code for a query I'm running that is working great for my report. What I need now is that I need the total of Y's and N's for each column. So I need to know how to make my sql put at the bottom on report Total count for how many Y's for conductor casing,and how many N's, how many Y's for surface casing, How many N's, How many Y's for Prod casing, how many N's, How many CMPS Y's and how many N's and How many Wells on Prod Y's and N's count. Then my report will be complete. This is my code so far and my query is run...
Multiple If statements in the criteria of a query
I'm trying to instert the following in the criteria section of a query in query builder; the itself is the record source for a list box ******************************************************************************************************* If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation" Then Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _ "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]" Else: If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = &quo...
Query joining two tables with different dates
I'm sure there is a simple solution to this, but being fairly new to Access I would appreciate some assistance... I have a query that joins two tables. One table has call details for each day that an agent works. The other table has details for each call that is graded for each agent. However, calls can be graded on days that the agent does not work. When joining the tables, the query will only display calls graded on dates that the agent works. This is what I would like to do to correct this: If there was a call graded on a day that the agent did not work, this data be rolled...
Excel 2003 This Workbook contains queries to external data
Excel 2003 I get the following message box when opening an excel spreadsheet and displaying an extnernal microsoft query. This Workbook contains queries to external data that refresh automatically. Allows you to click either Enable automatic refresh or Disable automatic refresh. I know this is a new security enhancement and is turned on by default. How do you change the default to off so this does not appear every time ...