pleeeeez i need help in this one!!!!!!!!

S

shami11136

i have a table with 2 fields one called names and the senond called number,
lets say that the data as following:
moutaz 1
moutaz 1
waseem 3
waseem 3
waseem 3
waseem 3
i want the code that return back the rows of all the names as it is in the
number field i mean even though that waseem appears 4 time but because of
number 3 i want it to bring only waseem 3 times not 4 also the name moutaz
should appear only once, how can i do that???
 
B

Bob Barrows

shami11136 said:
i have a table with 2 fields one called names and the senond called
number, lets say that the data as following:
moutaz 1
moutaz 1
waseem 3
waseem 3
waseem 3
waseem 3
i want the code that return back the rows of all the names as it is
in the number field i mean even though that waseem appears 4 time but
because of number 3 i want it to bring only waseem 3 times not 4 also
the name moutaz should appear only once, how can i do that???
Click to expand...

This one boggles my mind. I'm afraid to ask, so ...

This query retrieves the data you want:

select top 1 names,number from tablename where names='moutaz'
union all
select top 3 names,number from tablename where names='waseem'

What? You say that moutaz's number won't always be 1? Oh well ...

Given that your table is really designed as you say it is, automating
this will not be possible without using VBA to loop through a recordset
to generate the sql statement you need to use.
 
J

John Spencer (MVP)

Add a table to your database with just one number field and create ten records
with the values from 0 to 9

SELECT DISTINCT TheName, TheNumberTable.TheNumberFIeld
FROM YourTable, TheNumberTable
WHERE TheNumberTable.TheNumberField < YourTable.YourNumber

If you have more than 9 as a value for your number field, you can either
expand the number of records in the new table or use the table two (or more
times) in your query.

SELECT DISTINCT TheName
, Ones.TheNumberField + 10* Tens.TheNumberField
FROM YourTable, TheNumberTable as Ones, TheNumberTable as Tens
WHERE Ones.TheNumberField + 10* Tens.TheNumberField< YourTable.YourNumber


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

Bob Barrows

Clever. I forgot about using a Numbers table...
Add a table to your database with just one number field and create
ten records with the values from 0 to 9

SELECT DISTINCT TheName, TheNumberTable.TheNumberFIeld
FROM YourTable, TheNumberTable
WHERE TheNumberTable.TheNumberField < YourTable.YourNumber

If you have more than 9 as a value for your number field, you can
either expand the number of records in the new table or use the table
two (or more times) in your query.

SELECT DISTINCT TheName
, Ones.TheNumberField + 10* Tens.TheNumberField
FROM YourTable, TheNumberTable as Ones, TheNumberTable as Tens
WHERE Ones.TheNumberField + 10* Tens.TheNumberField<
YourTable.YourNumber


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Top