Prev: video or animations on ms accses 2003
Next: How do you execute an Access Pgm?
From: John Spencer on 16 Mar 2010 08:26
First does Partno in LivedB_MBB010 have the same values in it that you have in
LIVEDB_MBB680.PROCNO_PMR. In other words are the two the same type of thing?

By the way there is no reason to use a totals (group by query) for form2. You
might want to use DISTINCT keyword if you are trying to remove duplicates. An
aggregate query normally makes sense only if you are doing something besides
grouping the fields (max, min, avg, first, last, etc.)

SELECT DISTINCT LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
FROM LIVEDB_MBI070 INNER JOIN (LIVEDB_MBB160 INNER JOIN LIVEDB_MBB680
ON LIVEDB_MBB160.ACCOUNT15_SG = LIVEDB_MBB680.ACCOUNT15_PMR)
ON (LIVEDB_MBI070.ACCOUNT15_LAB = LIVEDB_MBB680.ACCOUNT15_PMR) AND
(LIVEDB_MBI070.SKILLGRADE = LIVEDB_MBB160.SKILLGRADE_SG)

WHERE LIVEDB_MBB680.PROCNO_PMR=[Forms]![Form1]![List7]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Craig wrote:
> As requested, here is the sql for the query that appears on Form1
>
> SELECT LIVEDB_MBB010.PARTNO, LIVEDB_MBB010.DESCRIPTION2
> FROM LIVEDB_MBB010
> WHERE (((LIVEDB_MBB010.PARTNO) Like "FG*") AND
> ((LIVEDB_MBB010.DESCRIPTION2)=[Forms]![Form1]![List5]))
> ORDER BY LIVEDB_MBB010.PARTNO;
>
>
> And then, here is the sql for the query that is done for Form2
>
> SELECT LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
> LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
> LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
> LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
> LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
> LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
> LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
> LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
> LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
> LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
> LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
> LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
> LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
> LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
> LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
> LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
> LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
> LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
> FROM LIVEDB_MBI070 INNER JOIN (LIVEDB_MBB160 INNER JOIN LIVEDB_MBB680 ON
> LIVEDB_MBB160.ACCOUNT15_SG = LIVEDB_MBB680.ACCOUNT15_PMR) ON
> (LIVEDB_MBI070.ACCOUNT15_LAB = LIVEDB_MBB680.ACCOUNT15_PMR) AND
> (LIVEDB_MBI070.SKILLGRADE = LIVEDB_MBB160.SKILLGRADE_SG)
> GROUP BY LIVEDB_MBB680.SKILLGRADE_PMR01, LIVEDB_MBB680.ACCOUNT15_PMR,
> LIVEDB_MBB680.PROCNO_PMR, LIVEDB_MBB680.PROCVER_PMR,
> LIVEDB_MBB680.PARTNO_PMR, LIVEDB_MBB680.PROCSTAGE_PMR,
> LIVEDB_MBB680.RESOURCENO_PMR, LIVEDB_MBB680.SKILLGRADE_PMR02,
> LIVEDB_MBB680.SKILLGRADE_PMR03, LIVEDB_MBB680.SKILLGRADE_PMR04,
> LIVEDB_MBB680.SKILLGRADE_PMR05, LIVEDB_MBB680.SKILLGRADE_PMR06,
> LIVEDB_MBB680.SKILLGRADE_PMR07, LIVEDB_MBB680.SKILLGRADE_PMR08,
> LIVEDB_MBB680.SKILLGRADE_PMR09, LIVEDB_MBB680.SKILLGRADE_PMR10,
> LIVEDB_MBB680.RSMANUSAGE_PMR01, LIVEDB_MBB680.RSMANUSAGE_PMR02,
> LIVEDB_MBB680.RSMANUSAGE_PMR03, LIVEDB_MBB680.RSMANUSAGE_PMR04,
> LIVEDB_MBB680.RSMANUSAGE_PMR05, LIVEDB_MBB680.RSMANUSAGE_PMR06,
> LIVEDB_MBB680.RSMANUSAGE_PMR07, LIVEDB_MBB680.RSMANUSAGE_PMR08,
> LIVEDB_MBB680.RSMANUSAGE_PMR09, LIVEDB_MBB680.RSMANUSAGE_PMR10,
> LIVEDB_MBB680.RSMANLEVEL_PMR01, LIVEDB_MBB680.RSMANLEVEL_PMR02,
> LIVEDB_MBB680.RSMANLEVEL_PMR03, LIVEDB_MBB680.RSMANLEVEL_PMR04,
> LIVEDB_MBB680.RSMANLEVEL_PMR05, LIVEDB_MBB680.RSMANLEVEL_PMR06,
> LIVEDB_MBB680.RSMANLEVEL_PMR07, LIVEDB_MBB680.RSMANLEVEL_PMR08,
> LIVEDB_MBB680.RSMANLEVEL_PMR09, LIVEDB_MBB680.RSMANLEVEL_PMR10
> HAVING (((LIVEDB_MBB680.PROCNO_PMR)=[Forms]![Form1]![List7]));
>
> Not sure if it helps at all, but Form1 also has the following VBA to help
> populate the list boxes
>
> List7.value = Null
> List7.Requery
>
>
>
>
>
> "KARL DEWEY" wrote:
>
>> Post the SQL of both queries by open in design view, clicking on VIEW - SQL
>> View, highlight all, copy, and paste in a post.
>>
>> --
>> Build a little, test a little.
>>
>>
>> "Craig" wrote:
>>
>>> I am currently trying to get to grips with Access 2003, and i seem to have a
>>> handle on some of the basics.
>>>
>>> I have managed to write a couple of queries, which feed from the one before
>>> ie a list of results is displayed, the user then clicks an entry, and this
>>> then goes into a second query to populate a second list box.
>>>
>>> The thing i am trying to do is now have a query on a second form [Form2]
>>> reference a click from [Form1]
>>>
>>> I thought the criteria would read as this
>>>
>>> [Forms]![Form1]![List7] [List7] being the field on the first form, but
>>> it does not seem to be the case.
>>>
>>> Hopefully it is a naming convention i am failing to get correct
First  |  Prev  | 
Pages: 1 2
Prev: video or animations on ms accses 2003
Next: How do you execute an Access Pgm?