Make Table query not populating

[Marsha]

I've got two seperate make-table queries that are both supposed to
populate into the same table. One works, the other doesn't. I have a
feeling it is something very simple I'm overlooking, but I can't figure
out what it is. I don't get any errors on the query that isn't
populating...just that it will add 0 rows?

Query 1 that works:

SELECT DISTINCT Trim([ScanTest7].[SSN]) AS SSN1, Trim([VisitDate]) AS
VisitDate1, Trim([VisitType]) AS VisitType1, ScanTest7.MedAdj,
Trim([Residence]) AS Residence1, Trim([PrimaryDiagnosis]) AS Diagnosis,
ScanTest8.StimulatorState,
IIf([ScanTest8].[Hoehnandyahr]>0,[ScanTest8].[HoehnandYahr]," ") AS
[HoehnAndYahr Scale],
IIf([Scantest5].[leveloffunctioning]>0,[ScanTest5].[leveloffunctioning],"
") AS [Schwab and England Scale], ScanTest.PainScale AS [Pain Index],
IIf([ScanTest2].[Memory]>0,[ScanTest2].[Memory]," ") AS [UPDRS 1],
IIf([ScanTest2].[Vividdreamshallucinations]>0,[ScanTest2].[Vividdreamshallucinations],"
") AS [UPDRS 2],
IIf([ScanTest2].[Depressed]>0,[ScanTest2].[Depressed]," ") AS [UPDRS
3], IIf([ScanTest2].[Speach]>0,[ScanTest2].[Speach]," ") AS [UPDRS 5],
IIf([ScanTest2].[Swallowing]>0,[ScanTest2].[Swallowing]," ") AS [UPDRS
7], IIf([ScanTest2].[Falling]>0,[ScanTest2].[Falling]," ") AS [UPDRS
13], IIf([ScanTest3].[Walking]>0,[ScanTest3].[Walking]," ") AS [UPDRS
15], IIf([ScanTest4].[Dyskinesias]>0,[ScanTest4].[Dyskinesias]," ") AS
[UPDRS 32],
IIf([ScanTest4].[howdisablingdyskinesias]>0,[ScanTest4].[howdisablingdyskinesias],"
") AS [UPDRS 33],
IIf([ScanTest5].[periodofdayoff]>0,[ScanTest5].[periodofdayoff]," ") AS
[UPDRS 39],
IIf([ScanTest5].[sleepproblems]>0,[ScanTest5].[Sleepproblems]," ") AS
[UPDRS 41],
IIf([ScanTest5].[lightheadeddizzy]>0,[ScanTest5].[lightheadeddizzy],"
") AS [UPDRS 42], ScanTest7.CurAmantadine, ScanTest7.CurBenzotropine,
ScanTest7.CurBromocriptine, ScanTest7.CurCitalopram,
ScanTest7.CurClozapine, ScanTest7.CurEntacapone,
ScanTest7.CurFludrocortisone, ScanTest7.CurFluoxetine,
ScanTest7.CurFluvoxamine, ScanTest7.CurLevodopaCarbidopaCR,
ScanTest7.CurLevodopaCarbidopaIR, ScanTest7.CurMidodrine,
ScanTest7.CurOther, ScanTest7.CurParaoxetine, ScanTest7.CurPergolide,
ScanTest7.CurPramipexole, ScanTest7.CurQuetiapine,
ScanTest7.CurRopinirole, ScanTest7.CurSelegiline,
ScanTest7.CurSertraline, ScanTest7.CurTolcapone,
ScanTest7.CurTrihexyphenidyl,
[memory]+[VividDreamsHallucinations]+[Depressed] AS [UPDRS I],
[Speach]+[Swallowing]+[Falling]+[Walking] AS [UPDRS II],
[Dyskinesias]+[HowDisablingDyskinesias]+[pERIODOFDAYOFF] AS [UPDRS
III], [SleepProblems]+[LightheadedDizzy] AS [UPDRS IV],
[NF06-Scantest3].[EQ-5D] INTO Visit
FROM (((((((ScanTest7 INNER JOIN (ScanTest INNER JOIN ScanTest6 ON
ScanTest.SSN = ScanTest6.SSN) ON ScanTest7.SSN = ScanTest.SSN) INNER
JOIN ScanTest8 ON ScanTest.SSN = ScanTest8.SSN) INNER JOIN ScanTest5 ON
ScanTest.SSN = ScanTest5.SSN) INNER JOIN ScanTest4 ON ScanTest.SSN =
ScanTest4.SSN) INNER JOIN ScanTest2 ON ScanTest.SSN = ScanTest2.SSN)
INNER JOIN ScanTest3 ON ScanTest.SSN = ScanTest3.SSN) INNER JOIN
Scantest9 ON ScanTest.SSN = Scantest9.SSN) INNER JOIN [NF06-Scantest3]
ON (ScanTest.VisitDate = [NF06-Scantest3].VisitDate) AND (ScanTest.SSN
= [NF06-Scantest3].SSN);

Query 2 that doesn't work:

SELECT DISTINCT Trim([NF06-Scantest1].[SSN]) AS SSN1,
IIf([NF06-ScanTest1].[VisitDate]," ") AS VisitDate1,
Trim([NF06-Scantest5].[VisitType]) AS VisitType1,
Trim([NF06-Scantest6].[MedicationAdjustment]) AS MedAdj,
Trim([NF06-Scantest2].[Residence]) AS Residence1,
([NF06-Scantest5].[PrimaryDiagnosis]) AS Diagnosis,
IIf([NF06-Scantest8].[HoehnandYahr]>0,[NF06-ScanTest8].[HoehnandYahr],"
") AS [HoehnAndYahr Scale],
IIf([NF06-Scantest8].[SchwabandEngland]>0,[NF06-Scantest8].[SchwabandEngland],"
") AS [Schwab and England Scale], [NF06-Scantest2].PainIndex AS [Pain
Index], IIf([NF06-Scantest2].[Memory]>0,[NF06-Scantest2].[Memory]," ")
AS [UPDRS 1],
IIf([NF06-Scantest2].[Hallucinations]>0,[NF06-Scantest2].[Hallucinations],"
") AS [UPDRS 2],
IIf([NF06-Scantest2].[SadDepressed]>0,[NF06-Scantest2].[SadDepressed],"
") AS [UPDRS 3],
IIf([NF06-Scantest2].[Speech]>0,[NF06-Scantest2].[Speech]," ") AS
[UPDRS 5],
IIf([NF06-Scantest2].[Swallowing]>0,[NF06-Scantest2].[Swallowing]," ")
AS [UPDRS 7], IIf([NF06-Scantest3].[Falls]>0,[NF06-Scantest3].[Falls],"
") AS [UPDRS 13],
IIf([NF06-Scantest3].[AbilitytoWalk]>0,[NF06-Scantest3].[AbilitytoWalk],"
") AS [UPDRS 15],
IIf([NF06-Scantest7].[Dyskinesias]>0,[NF06-ScanTest7].[Dyskinesias],"
") AS [UPDRS 32],
IIf([NF06-Scantest7].[DisablingDyskinesias]>0,[NF06-Scantest7].[DisablingDyskinesias],"
") AS [UPDRS 33],
IIf([NF06-ScanTest7].[PercentOff]>0,[NF06-Scantest7].[PercentOff]," ")
AS [UPDRS 39],
IIf([NF06-Scantest7].[SleepProblems]>0,[NF06-Scantest7].[SleepProblems],"
") AS [UPDRS 41],
IIf([NF06-Scantest7].[Dizzy]>0,[NF06-Scantest7].[Dizzy]," ") AS [UPDRS
42], ([NF06-Scantest6].[AmantadineCr]) AS CurAmantadine,
([NF06-Scantest6].[ApomorphineCr]) AS CurApomorphine,
([NF06-Scantest6].[BotulinumACr]) AS CurBotulinumA,
([NF06-Scantest6].[BotulinumBCr]) AS CurBotulinumB,
([NF06-Scantest6].[ClozapineCr]) AS CurClozapine,
([NF06-Scantest6].[FludrocortisoneCr]) AS CurFludrocortisone,
([NF06-Scantest6].[MidodrineCr]) AS CurMidodrine,
([NF06-Scantest6].[QuetiapineCr]) AS CurQuetiapine,
([NF06-Scantest6].[CitalopramCr]) AS CurCitalopram,
([NF06-Scantest6].[FlueoxetineCr]) AS CurFlueoxetine,
([NF06-Scantest6].[FluvoxamineCr]) AS CurFluvoxamine,
([NF06-Scantest6].[BenztropineCr]) AS CurBenztropine,
([NF06-Scantest6].[PramipexoleCr]) AS CurPramipexole,
([NF06-Scantest6].[SelegilineCr]) AS CurSelegiline,
([NF06-Scantest6].[TrihexyphenidylCr]) AS CurTrihexyphenidyl,
([NF06-Scantest6].[ParoxetineCr]) AS CurParoxetine,
([NF06-Scantest6].[SertralineCr]) AS CurSertraline,
([NF06-Scantest6].[Levodopa-carbidopa-CRCr]) AS
[CurLevodopa-carbidopa-CR],
([NF06-Scantest6].[Levodopa-Carbidopa-IRCr]) AS
[CurLevodopa-carbidopa-IR], ([NF06-Scantest6].[RopiniroleCr]) AS
CurRopinirole, ([NF06-Scantest6].[TolcaponeCr]) AS CurTolcapone,
([NF06-Scantest6].[Levodopa-carbidopa-entacaponeCr]) AS
[CurLevodopa-carbidopa-entacapone], ([NF06-Scantest6].[EntacaponeCr])
AS CurEntacapone, ([NF06-Scantest6].[MemantineCr]) AS CurMemantine,
([NF06-Scantest6].[RivastigmineCr]) AS CurRivastigmine,
([NF06-Scantest6].[GalantamineCr]) AS CurGalantamine,
([NF06-Scantest6].[DonepezilCr]) AS CurDonepezil,
([NF06-Scantest6].[Other1Cr]) AS CurOther,
[memory]+[Hallucinations]+[SadDepressed] AS [UPDRS I],
[Speech]+[Swallowing]+[Falls]+[AbilitytoWalk] AS [UPDRS II],
[Dyskinesias]+[DisablingDyskinesias]+[PercentOff] AS [UPDRS III],
[SleepProblems]+[Dizzy] AS [UPDRS IV], [NF06-Scantest3].[EQ-5D] INTO
Visit
FROM ((((((([NF06-Scantest1] INNER JOIN [NF06-Scantest2] ON
([NF06-Scantest1].SSN = [NF06-Scantest2].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest2].VisitDate)) INNER JOIN
[NF06-Scantest3] ON ([NF06-Scantest1].SSN = [NF06-Scantest3].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest3].VisitDate)) INNER JOIN
[NF06-Scantest4] ON ([NF06-Scantest1].SSN = [NF06-Scantest4].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest4].VisitDate)) INNER JOIN
[NF06-Scantest5] ON ([NF06-Scantest1].SSN = [NF06-Scantest5].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest5].VisitDate)) INNER JOIN
[NF06-Scantest6] ON ([NF06-Scantest1].SSN = [NF06-Scantest6].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest6].VisitDate)) INNER JOIN
[NF06-Scantest7] ON ([NF06-Scantest1].SSN = [NF06-Scantest7].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest7].VisitDate)) INNER JOIN
[NF06-Scantest8] ON ([NF06-Scantest1].SSN = [NF06-Scantest8].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest8].VisitDate)) INNER JOIN
[NF06-Scantest9] ON ([NF06-Scantest1].SSN = [NF06-Scantest9].SSN) AND
([NF06-Scantest1].VisitDate = [NF06-Scantest9].VisitDate);

Suggestions??

 

[John Vinson]

I've got two seperate make-table queries that are both supposed to
populate into the same table. One works, the other doesn't.
I have a
feeling it is something very simple I'm overlooking, but I can't figure
out what it is. I don't get any errors on the query that isn't
populating...just that it will add 0 rows?

Exactly. A MakeTable query does just that: it CREATES A NEW TABLE, and
gives you an error if you try to make a table which already exists.

Your second query should certainly *not* be a MakeTable query; perhaps
an Append query to append to the table created by the first query
would be appropriate.

Note that MakeTable queries are (in my experience) rarely needed. If
you're just creating a table as the basis for a Report, you don't NEED
a new table - a SELECT query (perhaps a UNION query in this case to
combine the two sets of data) can be used as the basis for a form, a
report, an export, almost anything that you can do with a new table.

I would also *strongly* suggest that you rethink your table design!
You're storing a lot of data in fieldnames, spreadsheet style. You'll
find life much easier if you normalize the data so that you have (say)
a Drugs table related many-to-many to your main table, via a
DrugsPrescribed table.

John W. Vinson[MVP]

 

[Marsha]

Thanks for the help. I was wondering about that with the make table
query, that is, whether I should change it to an append. I'll give it a
try and see if it works.

RE: the table design for this one is very confusing and somewhat
inefficient, I agree. The reason it is such though, is that it has all
of the fieldnames for a specific report that it's pulling from tables
that are based off of values from a questionnaire. Each table must
have only the values from that questionnaire page. Right now there is
a table that is just drug values, but then it is sorted into the other
table (via the make table soon to be append query) for the report,
along with only certain values selected from the other eight tables
that respond to the questionnaire pages, which a report is then based
off of.

If you've got other ideas, I'm open to them.

 

[John Vinson]

RE: the table design for this one is very confusing and somewhat
inefficient, I agree. The reason it is such though, is that it has all
of the fieldnames for a specific report that it's pulling from tables
that are based off of values from a questionnaire. Each table must
have only the values from that questionnaire page. Right now there is
a table that is just drug values, but then it is sorted into the other
table (via the make table soon to be append query) for the report,
along with only certain values selected from the other eight tables
that respond to the questionnaire pages, which a report is then based
off of.

If you've got other ideas, I'm open to them.

If you can construct a MakeTable query to do this... then you can
simplify that MakeTable query into a Select query, and base the report
on THAT.

Having the data in a new table simply makes the whole process less
efficient; you are creating all the joins, all the alias fieldnames,
etc. either way, but in the maketable query Access must create the new
table, create indexes on it, populate the table, update the system
tables, etc. etc.

If there are two or more queries that go into this, you can still do
it without any maketables - use a UNION query splicing them together.

John W. Vinson[MVP]