Baffeled

[loddiemonster]

I hope this isn’t too confusing: I’m trying to create a query that will pull one of two options for one customer ID. My fields are: ID, lic_id (license ID) and exp_date. In this particular situation, it is possible for one customer ID to have two lic_id entered into my database. If that is the case, I want my query to look at the lic_id field, determine which one I want to see, and reflect that one lic_id in the results. For example: customer ID 100 has two entries in the lic_id field: 4 and 18. 4 means the customer is fully licensed and 18 means they have a provisional license. When the customer has completed the requirements to gain full licensure, I end date the provisional and enter the full license effective date the day after the Provisional’s end date. The full license is valid for two years. I tried creating a IIF function for the query, but it pulls both, and if I give that narrow query criteria, like “true” or “false”, it will only pull one or the other, and if a particular customer only has provisional at the time of the pull, I won’t be able to monitor that license because it’s not reflecting on my query.

Is there a way I can write the query to return only lic_id 4 if the same customer ID has an 18 AND if a customer ID only has 18, that will show on the same query?

 

[arnelgp]

you try union query:

select id, lic_id, [provisional end date field],[full license effective date field] from yourTable where not ([full license effective date field] is null);
union
select id, lic_id, [provisional end date field],[full license effective date field] from yourTable where ([full license effective date field] is null);

 

[loddiemonster]

Thanks for responding! I don’t think that will work. Provisional End Date and Full License End Date is in the same field. I tried running a Union Query with query one showing only provisional licenses and query two showing full licenses, and they’re still overlapping. Consumer ID 100 had a provisional license (lic_id 18) to expire on 1/15/2015. Then Consumer ID 100 received full licensure (lic_id 4) effective 1/16/2015 and expired on 1/16/2017. The Union query is pulling them both, which I only want to see the full licensure exp date (lic_id 4). Consumer ID 101 has ONLY a provisional license (lic_id 18) that expired on 6/30/2017. I want to see both Consumer ID 100 and Consumer 101 appear on my query (with only the full license for Consumer ID 100 OVERRIDING the provisional).

 

[Mark_]

Can you please post your current query definition? That may help us understand your question better.

 

[1268]

Thanks for responding! I don’t think that will work. Provisional End Date and Full License End Date is in the same field. I tried running a Union Query with query one showing only provisional licenses and query two showing full licenses, and they’re still overlapping. Consumer ID 100 had a provisional license (lic_id 18) to expire on 1/15/2015. Then Consumer ID 100 received full licensure (lic_id 4) effective 1/16/2015 and expired on 1/16/2017. The Union query is pulling them both, which I only want to see the full licensure exp date (lic_id 4). Consumer ID 101 has ONLY a provisional license (lic_id 18) that expired on 6/30/2017. I want to see both Consumer ID 100 and Consumer 101 appear on my query (with only the full license for Consumer ID 100 OVERRIDING the provisional).

Have to have a differentiated variable. You have the table so what is unique in all your fields that will also always be true.

Maybe you need to add a status field. This is fairly common. Since you are making updates you can set active or inactive at that time.

Sent from my SM-G950U using Tapatalk

 

[loddiemonster]

The value that will always be the same is the Customer ID for each person that has a license. The lic_id is different. I wish I could add a status field, but our database would not allow that. Several people update the database each day with license information, and I’m trying to create a query that is more user friendly when it’s time to update licensures.

I’m afraid you’re right about the status field, and there’s nothing that can be done about that.

 

[Mark_]

Is the license, its type, and date in a child file to your parent?

 

[Pat Hartman]

Wouldn't you want the license that was effective during a certain time period?

Select ...
From ...
Where Date() Between eff_date and exp_date;

Date() is today's date. If you need some other date, use a parameter.