Query Problem

[sameh.abdelkawy]

Hello everyone,

I have office 2010 and 2019

I have a problem in query, and I would like to show it in the Form.

I have one table which has 4 fields:

1- Shipment Name

2- Date Shipment (Entrance), it could be repeated.

3- Date Shipment (Registration), it could be repeated.

4- Shipment Code, no repetition, (primary key)

I need to make a query with 2 columns:

1- Shipment Name

2- The second column has to get a unique date entrance or registration or code.

If it has duplicate date entrance, then show date registration.

If it has duplicate date registration, then show shipment code.

I would like to know how to make it.

Thanks for any help in advance.

 

[oleronesoftwares]

If it has duplicate date entrance, then show date registration.

If it has duplicate date registration, then show shipment code.

You might have to create two queries, to cover the two scenerios

Then in VBA write an If statement to determine which of the queries will be shown.


Are you calling the query from a combo box?

 

[theDBguy]

Hi. Welcome to AWF!

Can you show us some sample data and the result you want from them?

 

[MajP]

I think you can do this with a calculated field, but if you can post some test data that would help.
Something like this (untested)

Ent_Reg_Code: iif(dcount("*","YourTable","ShipmentName = '" & [ShipmentName] & '' AND Not ISNULL([Entrance])") = 0, [Entrance], dcount("*","YourTable","ShipmentName = '" & [ShipmentName] & "' AND NOT ISNULL([Registration]) ) = 0, [registration],[ShipmentCode])

Not sure how you can have multiple entrance and not have multiple receiving unless you have null in the recieving.

 

[sameh.abdelkawy]

Hello,

Thank you so much for your replies.
I just need to make a query with 2 columns:
Shipment Name and the (Date Entrance or Registration or Code).

Thanks for any help

 

[theDBguy]

Hello,

Thank you so much for your replies.
I just need to make a query with 2 columns:
Shipment Name and the (Date Entrance or Registration or Code).

Thanks for any help

Hi. The problem is we can't see what you got, so it's a little hard to imagine what a "duplicate" actually means. That's why it would help a lot if you could share some sample data with us.

 

[sameh.abdelkawy]

You might have to create two queries, to cover the two scenerios

Then in VBA write an If statement to determine which of the queries will be shown.


Are you calling the query from a combo box?

Hello,

Thank you so much for your replies.
I just need to make a query with 2 columns:
Shipment Name and the (Date Entrance or Registration or Code).

Thanks for any help

 

[sameh.abdelkawy]

I think you can do this with a calculated field, but if you can post some test data that would help.
Something like this (untested)

Ent_Reg_Code: iif(dcount("*","YourTable","ShipmentName = '" & [ShipmentName] & '' AND Not ISNULL([Entrance])") = 0, [Entrance], dcount("*","YourTable","ShipmentName = '" & [ShipmentName] & "' AND NOT ISNULL([Registration]) ) = 0, [registration],[ShipmentCode])

Not sure how you can have multiple entrance and not have multiple receiving unless you have null in the recieving.

Thank you so much. I'll test it and tell you about it.

 

[MajP]

Fyi that is a wild guess on my part. Not sure you can test it. What you are asking is not that simple.

 

[sameh.abdelkawy]

I attached sample data. I hope it can help.
Thanks!

 

[theDBguy]

I attached sample data. I hope it can help.
Thanks!

Hi. You're halfway there. What we need now is the actual result you want to see out of that sample data. For example, you said "If the date entrance is the same," - the same as what? That's the part I still don't understand.

 

[MajP]

Also your sample data must include demonstrate the three cases

1. No duplicate entrance show entrance
2. Else, No duplicate registration then show date registration.
3. Else then show shipment code.

I do not see how you get to rules 2 or 3.

 

[sameh.abdelkawy]

Hi,

If the date entrance is the same, then choose date registration. And if the date registration is the same, then choose shipment code. I need it in ascending order
I mean if there are duplicate date entrance and date registration. In this sample we have 10-11-21 in date entrance repeated 2 times.
Then we exclude it and search for date registration. If it is okay, then show it in the output. If it's repeated like 11-11-21 repeated 2 times, we exclude it and add the last one (Shipment Code)
I hope the attached file can help.
Thank you all so much for your replies.

 

[sameh.abdelkawy]

Also your sample data must include demonstrate the three cases

1. No duplicate entrance show entrance
2. Else, No duplicate registration then show date registration.
3. Else then show shipment code.

I do not see how you get to rules 2 or 3.

Hi. You're halfway there. What we need now is the actual result you want to see out of that sample data. For example, you said "If the date entrance is the same," - the same as what? That's the part I still don't understand.

I mean if there are duplicate date entrance and date registration. In this sample we have 10-11-21 in date entrance repeated 2 times.
Then we exclude it and search for date registration. If it is okay, then show it in the output. If it's repeated like 11-11-21 repeated 2 times, we exclude it and add the last one (Shipment Code)
I hope the attached file can help.

 

[sameh.abdelkawy]

Also your sample data must include demonstrate the three cases

1. No duplicate entrance show entrance
2. Else, No duplicate registration then show date registration.
3. Else then show shipment code.

I do not see how you get to rules 2 or 3.

I mean if there are duplicate date entrance and date registration. In this sample we have 10-11-21 in date entrance repeated 2 times.
Then we exclude it and search for date registration. If it is okay, then show it in the output. If it's repeated like 11-11-21 repeated 2 times, we exclude it and add the last one (Shipment Code)
I hope the attached file can help.

 

[oleronesoftwares]

since there are three conditions to be tested one after the other

Using a dcount function with an if else if statement should do it

 

[sameh.abdelkawy]

since there are three conditions to be tested one after the other

Using a dcount function with an if else if statement should do it

Okay. Can you help me in that? I'll be very grateful for it.
Thank you!

 

[Pat Hartman]

Maybe if you explained why you want such a strange process and how it will help the user we could offer a rational solution. Your data doesn't even come close to covering all possibilities. What problem are you trying to solve?

 

[oleronesoftwares]

Are you calling the query from a combo box?

Do answer this question.

 

[sameh.abdelkawy]

Hi everyone,

Forget any system complexity about the previous example. What I need to make is putting data in ascending order under three conditions:
1- Date Entrance
2- Date Registration
3- Shipment Code
If duplicate date entrance, THEN add date registration, If duplicate date registration, THEN add shipment code.
I need to apply what in the attached image in Access.
And sorry if I make any confusion in my previous example.

Thank you for any help.