ProjectNumbers with ProjectGroups (1 Viewer)

[DaRTHY]

Hello My Friends,

I have 2 Type of Fields. First one is : "ProjectNumber" other one is : "ProjectGroups". I will write here sort : "ProjectNumber" as a PNr. "ProjectGroups" as a PGr.

What do I need : (I really do not know how can i explain my wish basically.)

At Pictures as you can see There is 7 Different PNr.'s. These are :
P000231, P000646, P000921, P001671, P001724, P100043, P100521
And those PNr.'s own (Most of them) multiple PGr.'s.

What I want to see on my screen when I write my query:
(I will write result here and try to explain)
P000231, P001671, P100521 Those 3 Projects i want to see on my screen.
PGr.'s of PNr.'s own "All" and "rest of number(s)"

if PGr.'s of PNr.'s is only "Number" or only "All", i want to ignore these records at my query.

For Example : P000646 has only "All" PGr. and i do not want to see it. as same as P100043. It has only "All"

P000921 and P001724 have more "number" at the PGr.'s BUT it does not own "All". these are only numbers.

It shall be both. "Number + All" like them : P000231, P001671, P100521

Hope so i could explain what do i need. If you do not understand, i will try it again.

Is that possible to write that kind of Query ? And How ?

Thanks for your patience and understanding.

 

[RogerCooper]

You need to have to 3 tables, one for ProjectNumber, another for ProjectGroups and another linking the 2 tables.

You can set an "all" field for ProjectNumber with a True/False value. This is a better setup in general, as I assume that your have other information associated with ProjectNumber & ProjectGroups

 

[plog]

Can you provide better sample data to illustrate what you have and what you want? Give me 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what you hope to end up with in your query when you feed the query the data from A.

Providing this data in a spreadsheet would work best. One tab for A, one tab for B.

 

[DaRTHY]

Hello,
Thanks for answering to me. I will send my database.

a-) I deleted some Contents. Because it is forbidden and these are not important now.
b-) There was more than 2000 Projects and Projects with Milestone more than 30000. I deleted them either. Otherwise the DB was more than 100Mb.
c-) The Tables have been built too long time ago. I cant chance them. d-) I am getting these Tables from the real system and it is normally Excel. I am exporting them to my Department DB and we are playing on Records. I am exporting every week.
e-) They want to eliminate the other Projects.
f-) Result shall be what did i try to explain
g-) My team want to see only the project has at group"all+Numbers"
the project -> group can be not only All or only number(s)

Every project is beginning with group "all". than a project is branching out (Not all of them) and coming new groups. And sometimes (not all of them) they are ignoring the Group "all" and using only numbers. When they are ignoring these Groups, i cant export that groups to my DB.

Thats why i need to see on my query result => a same project number and their groups with "all+numbers" together.

actually i wrote same things i guess so...

Merry Christmas to all of you

 

[plog]

If that post was for me, that's not what I asked for. I wanted starting sample data and expected results. You at best gave me startign sample data. I need expected results. Not expected results explained, actual data that is expected.

With that said, your tables are incorrectly structured. Here's a few of the things I see:

1. Ordered field names--those double letter named fields (AA..OO) shouldn't be in Milestone. They should be in their own table, but not like you have them. Tables shouldn't grow horizontally to accomodate data (with more columns), but vertically (with more rows). That means you shouldn't have any fields named for double letters. Instead you just add more records to antoher table for each date you have.

2. No keys. You should be using autonumber primary keys to provide each record in your tables a unique value. Then you should use that value in tables you want to link together.

3. Poor names. You should only use alphanumeric characters for field and table names. That means no spaces. It just makes coding more difficult down the road.

4. Poor names 2. You have used reserved words for field names (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe). This too will cause difficulty down the road when coding. [Time], [Name], [Begin] and [End] should be prefixed with what they represent (what is it naming, what is beginning, what is ending?).

Clean up those things then repost your database. Let's get your tables fixed before moving to queries.

 

[DaRTHY]

I can understand what do you mean. But i did not choose those names and tables. They are coming from the server. I am just exporting to excel and importing to our department DB than trying to do their jobs. And every week to change DB Tables its not possible...

ps : The MS names are not real.. but the real names are also same. KF DS its going like that. And they have all together. Only 2 tables and they are like exactly what did i post here.