adding new column to recordset

[pradeep.sands]

Hi all,


I have a recordset built from a query (and this query is very complex and made from several tables).
Ok, now my problem is, I need to add one Boolean column which is not related to any table to this recordset. Anybody can help me with this?
Thanks

[June7]

Fields can be constructed with expressions. What do you want this field to calculate so that result is either Yes or No?

[pradeep.sands]

Hi,
Thanks for your reply. Well, here I explain what I am exactly looking for.
Like I said, I have a recordset built from a query (and this query is a complex one made from several other tables). Below you can see this query (i completely modified the original).


Please note that there is NO primary key here.
the first two records are same. But the query shows in two seperate records because the Machine ID is different. And similar is the case with other 4 records. they r same but refer to different machines (1,2,3,4).
Now, I copied this query (using DoCmd) into another temporary Query and here I used 'DISTINCT' to the 'partRefNum' and hidden the MachineID field. So my new query looks like this:



Now, like I asked in previous post, I need to add new fields to this query (which are Boolean) that should look like this:



If you see the relation of fields of this query with the first query, the MachineID field in first query (that is creating multiple records) is now converted into different fields instead. So that multiple records are eliminated.

So, can you help me how do I implement this?

[June7]

You could try using the first query as the source for a CROSSTAB. The Machine fields won't be checkboxes, they will have a count of how many records related to each machine, according to the grouping you set. In this case, the group is defined by 5 fields.

As long as there is at least one record for each machine, the crosstab will result in a column for each machine.

[pradeep.sands]

@June7: Could you please eloborate it in more detail..i'm confused with the description.

[June7]

Use the query wizard for CROSSTAB. Access Help has guidelines on building CROSSTAB query.

[pradeep.sands]

@June7: Thanks again. Now i understand. But w.r.t. your previous answer, I want a boolean/checkbox for Machine in column and not the count of records like u mentioned.
So is there any alternative for this?

[ssanfu]

PMFJI,

You can't really define a column as a particular type (at least I haven't been able to find a way). And a check box is a control on a form or report.

But I create "fake" crosstab queries; the only thing is that the number of crosstab fields is fixed, unlike a normal crosstab that can adjust to the number of values in the column. In this case, my "faked" crosstab is fixed at 4 (see the formulas). If the number of machine IDs increases to 6, you would have to modify the queries, form and reports that use the machine ID.

The easiest way to explain this is to look at the example mdb (A2000). The two queries are "Query1" and "Query1_Fake_Crosstab".

Warning: If you have two records that are identical

PartRefNum     Desc     ValA        ValB        ValC   Machine_ID
   1           abcd       20          30          40         3
   1           abcd       20          30          40         3

the result will be -2, not -1. I have not found any way around this.

[June7]

No, not directly in the CROSSTAB query. CROSSTAB query is not editable. Why is checkbox critical?

Might be able to bind the CROSSTAB to a form or report. Convert the machine counts to a True/False (-1/0) value with an expression and bind those calculated fields to checkbox controls.

[ssanfu]

Convert the machine counts to a True/False (-1/0) value

@June,
You probably already know this, but here is a technique I use when I have a number field, but I just want to show true or false. (no conversion required)

False is defined as zero.
True is actually defined as not zero. So any non zero number is true.

On a form what has a text box bound to a number field, add a check box bound to the same field as the text box.

Enter a zero in the text box. The check box should be unchecked.
Enter -1 in the text box. The check box should be checked.
Enter a zero in the text box. The check box should be unchecked.
Enter 3,000 in the text box. The check box should be checked.

[June7]

Might have been aware of that at some time but didn't recall. If the true CROSSTAB approach is taken will still need an expression to convert the nulls to 0.