randomly extract value (for specified field) from table

[Valeriya]

Hi guys! I've got the following problem.
I have a query that pulls data from several tables.
After I run the query the date in the "Month/yr_1" field is populated for some records and is NULL for others).
The ones that are populated show the same date(eg. all records in the "Month/yr_1" field show date - "4/8/2006").

What I want to do is this:

- For the records that show "NULL" in the "Month/yr_1" field I want to populate them with the date that shows in the records that are populated.

- I want to use some kind of random function that goes to a specific field in the table where all records show the same date and pull one of those date values randomly so I can populate my blank or "NULL" "Month/yr_1" field with that particular date.

Here the code for that query:

[b]SELECT DISTINCT[/b] [0005 Bins with SOD].GDS, [0005 Bins with SOD].DAM, [0005 Bins with SOD].DAMMgr, [0005 Bins with SOD].REP, [0005 Bins with SOD].AcctName, [0005 Bins with SOD].Cust, [0005 Bins with SOD].BinCls, [0005 Bins with SOD].Bin, [0005 Bins with SOD].ShortItemNbr, [0005 Bins with SOD].Item AS ItemNumber, [0005 Bins with SOD].Description, [0005 Bins with SOD].Maj, [0005 Bins with SOD].Min, [0005 Bins with SOD].QtyOnHand, Trim([0005 Bins with SOD].[ID]) AS [ID#], [0005 Bins with SOD].DateUpdated, [0005 Bins with SOD].ExpDate, [0005 Bins with SOD].OrderType, [0005 Bins with SOD].OrderNbr, [0005 Bins with SOD].Reference, [0005 Bins with SOD].OrderDt, [0005 Bins with SOD].Ship, [0005 Bins with SOD].StatusCodeNext, Counts.ID, Counts.Comment AS Counts_Comment, Counts.[Month/Yr], Counts_1.[Month/Yr] AS [Month/Yr_1],
IIf([counts].[month/yr] Is Null,IIf([ship] Between counts_1.[month/yr]-10 And counts_1.[month/yr],"Replenish",
IIf(counts_1.[month/yr]<[ship],"Ship After Count","NOT COUNTED")),"") [b]AS[/b] Comment [b]INTO[/b] [0006 In Bin and Not Counted]
[b]FROM[/b]([0005 Bins with SOD] [b]LEFT JOIN[/b] Counts ON ([0005 Bins with SOD].Cust = Counts.Cust) AND ([0005 Bins with SOD].ID = Counts.ID)) 
[b]LEFT JOIN[/b]Counts[b] AS[/b] Counts_1 [b]ON[/b] [0005 Bins with SOD].Cust = Counts_1.Cust
[b]WHERE[/b] ((([0005 Bins with SOD].QtyOnHand)>0) [b]AND[/b] (([0005 Bins with SOD].StatusCodeNext)<"300") [b]AND[/b] ((Counts.ID) Is Null) [b]AND [/b]((IIf([counts].[month/yr] Is Null,
IIf([ship] [b]Between[/b] [counts_1].[month/yr]-10 And [counts_1].[month/yr],"Replenish",
IIf([counts_1].[month/yr]<[ship],"Ship After Count","NOT COUNTED")),"")) Not Like "Ship*"))
[b]ORDER BY[/b] Trim([0005 Bins with SOD].[ID]);

Here the Output:

Cust ID# Ship Month/Yr_1 Comment
2136 020589 6/8/2004 NOT COUNTED
5822 021009 6/2/2005 4/8/2006 NOT COUNTED
1771 030054 3/30/2006 NOT COUNTED
1771 030054 3/30/2006 4/8/2006 Replenish.
3178 030397 12/21/2005 NOT COUNTED
1564 030596 3/15/2006 NOT COUNTED
3575 034133 12/9/2005 4/8/2006 NOT COUNTED
1622 034559 2/23/2006 NOT COUNTED
1564 040027 3/15/2006 NOT COUNTED
1127 040028 12/22/2004 4/8/2006 NOT COUNTED
3575 040035 3/23/2006 4/8/2006 NOT COUNTED
3575 040035 3/23/2006 4/8/2006 NOT COUNTED

As you can see I want to populate the "NULLs" with 4/8/2006 by extracting that date randomly from "Counts" table. Hope this is not too messy and you can understand what I'm trying to accomplish.
PS. In the output I have hidden the fields that bear no importance.

Thank you very much!

Valeriya

 

[mp9]

Have you taken a look at the Nz function for supplying a value in the event of Null data?

http://www22.brinkster.com/accessory/

 

[Valeriya]

Hello mp9,

Using Nz is fine, and I have tried it, but the main thing is to populate the field with the date that shows in the records that are populated.

That would not be a problem to do with DateSerial function like: DateSerial(year(Date()), month(Date()), 8) (each month the date in the date field needs to show 8th day eg. xx/8/xxxx), however, here is the kink...

Each month the database is archived.
Due to the nature of the business, sometimes I have to run this query in the archived database (let say March database)...
Well in that case DateSerial will give me a date for this month like -4/8/2006 yet I need the date to be 3/8/2006...

That is why I want to use some kind of random function that could randomly extract needed date from the date field in the Counts table.(the date there is entered manually and it is always corresponds to the date for that database. Simply to determine,when actually customer counts were due -is to determine for what month reconciliation needs to be done and second for what month you are using the database)

Thank you in advance!

Valeriya

 

[Valeriya]

Is anybody aware of a function, that would randomly get the value from any populated field(Nz function would take care of that part).
Something like:
If (Nz([Counts].[month/yr]),[Counts].[month/yr],Function that I'm looking for(([Counts].[month/yr]))

Please help,
Thanks,
Valeriya

 

[Remou]

Can you not use a nested query that selects the top record from counts where the date is not null?

 

[PHV]

Seems you have a database per month ?
So, you may try this:
Nz([Counts].[Month/Yr], DLookUp('[Month/Yr]','Counts','[Month/Yr] Is Not Null')) AS theMonth

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Valeriya]

Hi guys! I'm truly thankful for all your help. Sorry it took me so long to respond, I was abroad all that time.

PHV, Dlookup was a quick and easy solution to my problem.

Again many thanks!

Valeriya