SQL Join frustrations 1

[bkdobs]

Several parts to this question ... given one table with up to 10 related records ... task create a query to join all related records in one record with 10 fields (may be 0 to 10 related records for any given primary key

a)what is the maximum number of joins one would want to ever make on the same table?

b)would it be more appropriate to store this type of data model in a flat table ie waste disk space and create each record with space to store 10 doubles per record?

c)I have sort been able to generate a query that gets 2 related records but cannot get the syntax correct for nesting larger then one join?

d)some literature sugest that there are several ways to enter nested joins ... Select from join (select ... select from where select

 

[danvlas]

Cross-tab query built on a 'normal join' query?

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[bkdobs]

Hmmm I would like to give you a 100 stars for this one ... I have been struggling with this for a few months now off and on ... now I know what the crosstab is for ...

THANX A WHOOOOOOOOOOOOOOOOLLLLLLLLLLLLEEEEEEEEEEEE bunch!!!

 

[danvlas]

Relax...this is just the beginning )

Any time.






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[bkdobs]

Hmmm just a slight twist ... I need all 10 columns to show up regardless ... with no where clause the crosstab is generating 9 of the 10 columns because the dev table is not completely full ... I could generate a dumby record with 0 in each type but because I have referential integrity enforced that would mean introducing a foriegn widget in to the data.

Using a WHERE clause in the Crosstab I get one record but because the data is optional for each record the crosstab will return a random selection of those 10 fields

is there any way to fool Access and or the Crosstab into thinking there should be 10 fields?

I though I could just use 2 queries but don't think the compiler will be happy with missing field names ie

select nz(ct.missingfield0,0), ... nz(ct.field9,0)
From ct
where ct.part = getblah().

 

[danvlas]

Check the 'ColumnHeadings' property. Pressing F1 (then search) would help a lot.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[bkdobs]

Very interesting ... I had tried the IN function in the SQL statment but the two key details were that if trying to apply this in the design mode it must be done in the properties of that field and secondly the named fields must match the data from the crosstab table field and or as in my case the modified column header name ("text"&field)

Again thanx for your time.

 

[qlark]

Question: can this type of query be made to have only one row of output for all 10 columns?