create a composed queries in access-sql

[mosquito_admin]

Hi everybody !

I have a access 2003 DB with one table, for ex. which contains the info about the users and their PCs like this:
| name | PC1 | PC2 | PC3 |
| Jack |Asus |Lenovo| HP |
|John | DELL | | |
|Anna | |Asus | |



Exist any function in sql query that will jump the fields so that the returned query will be like this:

| name | PC |
| Jack | Asus |
| |Lenovo|
| | HP |
|John | DELL |
|Anna | Asus |

?

And at second, exist any counter type in the sql queries that will count only the fully records, without empty fields so that the returned query will be like this:

| Nr | name | PC |
| 1 | Jack | Asus |
| | |Lenovo|
| | | HP |
| 2 |John | DELL |
| 3 |Anna | Asus |

?

I am new in access and sql so that I don't know many secrets.

[pbaldy]

Presumably this got the data into the basic format:

https://www.accessforums.net/queries...03-a-9425.html

except for the repeating names. The simplest way to get what you want from there is on a report. You can either use Sorting and Grouping to get a single instance of each name, or use the Hide Duplicates property of the textbox containing the name.

The numbering would be fairly simple using the first method, with a textbox that had a control source of:

=1

and its running sum property set to over all.

[mosquito_admin]

Dear pbaldy, give me some examples or more detailed indications to use these instructions, because I trying to create some queries by using that you adviced me but without big success.
PLEASE, help me to resolve it !!!
Thanks in advance

[pbaldy]

How about a more detailed question? What query exactly have you tried, and what is wrong with it? I would suspect the query from the other thread returns the correct data, it just repeats the names which you don't want. Either of the methods above would resolve that problem.

[mosquito_admin]

Presumably this got the data into the basic format:

https://www.accessforums.net/queries...03-a-9425.html

except for the repeating names. The simplest way to get what you want from there is on a report. You can either use Sorting and Grouping to get a single instance of each name, or use the Hide Duplicates property of the textbox containing the name.

The numbering would be fairly simple using the first method, with a textbox that had a control source of:

=1

and its running sum property set to over all.

Hello pbaldy,
I found the solution for my first condition (Exist any function in sql query that will jump the fields) in this way:
1. in the DB list I added a field with unique ID for each record;
2. the query will show each PC model with their possesor and unique ID in a new record (line). Result that the names and unique ID will repeat in the query.
3. I have created a report which will show all the fields of the query, BUT with the following conditions:
3.a. in the unique ID field I have activated "Hide Duplicates" property
3.b. in the name field I have indicated the condition =IIf(ID.IsVisible;[name];Null)

Now I tried to do this for the my second condition (exist any counter type in the sql queries that will count only the fully records, without empty fields) by indicating the =IIf([ID].[IsVisible];Count([Numele]);Null), because I don't understand your solution for this problem (=1).
Can you provide me more detailed information for this solution ?

[mosquito_admin]

Presumably this got the data into the basic format:

https://www.accessforums.net/queries...03-a-9425.html

except for the repeating names. The simplest way to get what you want from there is on a report. You can either use Sorting and Grouping to get a single instance of each name, or use the Hide Duplicates property of the textbox containing the name.

The numbering would be fairly simple using the first method, with a textbox that had a control source of:

=1

and its running sum property set to over all.

Dear pbaldy,
please help me to find a solution for the above problem !!!!