[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I connect more than 30 tables/queries in an Access query, or accomplish the same result?

Posted on 2011-04-19
27
Medium Priority
?
363 Views
Last Modified: 2012-05-11
Hi!

I have a database in which users can book schedules for their boats.
I was not very familiar with either access or VBA when i made the DB, so I think I got off to a bad start, and now I am stuck with the layout.
So I am hoping to find a way to make this work without having to make it all over.

There are currently 28 slots each day, and when a user selects their usernumber in a combinationbox, the rest of the information about the member's boat appears in the fields to the right of the number.
There are 2 main tables, one with all the slots and the membernumbers assigned to each (schedule-table), and one with all the information about the boat of each membernumber (member-table).
So what I did was to make 27 identical queries of the member-table, and related each slot in the schedule-table to a separate query.
This worked perfectly, until they just now asked me to add 4 more slots to the day.
I created 4 more queries, and followed the same procedure, but as I add number 31, I get "query too complex".

Is there anything I can do to add 4 more slots to the schedule, without too many hours of work?
I experienced with having one big query with many duplicates of the same fileds, which resulted in the query working but the form wouldn't let me put in any data.

0
Comment
Question by:VikingEgil
  • 15
  • 6
  • 5
  • +1
27 Comments
 
LVL 8

Expert Comment

by:edemcs
ID: 35424168
Can you post your SQL?

How about using a Union?
0
 
LVL 58
ID: 35424184
<<I created 4 more queries, and followed the same procedure, but as I add number 31, I get "query too complex">>

  Sorry, but no.  Access (or more accuratley JET, Access's default database engine) has a limit of 32 indexes in a operation.  Your bumping into that limit and it is not modifiable.

  The reason your bumping into the limit is your database design.

  If you can post a sample of the DB with just the tables, there maybe something we can do to change it without too many issues.

JimD
0
 
LVL 58
ID: 35424218
 I just re-read your question and it may not be the problem I think it is.  Try the Union query as has been suggested.

  Also, you can try aliasing the table names to a couple of characters.  Do that by right clicking on the table in query design, then change the name property.  Make it something lime M1, M2, M3, etc.

  One other internal limit that JET has is on analyzing a query; it's roughly a 64K limit.  If it can't work with the query within that, you'll get the query too complex error.

  Shortening table and field names can help with that.

 If all that fails, then your other option is to build up a temp table by executing 31 seperate queries, then using a final query to read the temp table.

JimD.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 35424235
<< If all that fails, then your other option is to build up a temp table by executing 31 seperate queries, then using a final query to read the temp table.>>

  I would do that in code by the way by generating the SQL required for the append on the fly in a loop.

JimD.
0
 

Author Comment

by:VikingEgil
ID: 35427096
Thanks for all answers :) I'm obviously in another time zone than you guys, so I will do these steps early tomorrow!
That is, post the tables and the SQL-code.
I have read the tip about renaming the queries to short names, but I guess that would require me to change every referance to them as well, to match the new name?
I will try this tomorrow too.
0
 
LVL 58
ID: 35428092
<<I have read the tip about renaming the queries to short names, but I guess that would require me to change every referance to them as well, to match the new name?>>

 No, you would be changing the table/query names in the query.  Open the query in design view, then right click on a table/query that is being joined to and select properties.  Then change the name to something shorter.

Every reference within your query to fields in that table/query will then change.  If your SQL statement is long and you have long table/query names, this can help avoid bumping into the limit.

Try changing a half dozen or so and see if you can run with the 31 joins.   If you can, then do all the rest.

JimD.
0
 

Author Comment

by:VikingEgil
ID: 35430994
I see, well that sounds simple enough.
I'll try that in a minute, meanwhile, here is the SQL code.
SELECT [Opplagsliste 10].NAVN AS [Opplagsliste 10_NAVN], [Opplagsliste 10].MOB AS [Opplagsliste 10_MOB], [Opplagsliste 10].BÅT AS [Opplagsliste 10_BÅT], [Opplagsliste 10].LENGDE AS [Opplagsliste 10_LENGDE], [Opplagsliste 10].BREDDE AS [Opplagsliste 10_BREDDE], [Opplagsliste 11].NAVN AS [Opplagsliste 11_NAVN], [Opplagsliste 11].MOB AS [Opplagsliste 11_MOB], [Opplagsliste 11].BÅT AS [Opplagsliste 11_BÅT], [Opplagsliste 11].LENGDE AS [Opplagsliste 11_LENGDE], [Opplagsliste 11].BREDDE AS [Opplagsliste 11_BREDDE], [Opplagsliste 12].NAVN AS [Opplagsliste 12_NAVN], [Opplagsliste 12].MOB AS [Opplagsliste 12_MOB], [Opplagsliste 12].BÅT AS [Opplagsliste 12_BÅT], [Opplagsliste 12].LENGDE AS [Opplagsliste 12_LENGDE], [Opplagsliste 12].BREDDE AS [Opplagsliste 12_BREDDE], [Opplagsliste 13].NAVN AS [Opplagsliste 13_NAVN], [Opplagsliste 13].MOB AS [Opplagsliste 13_MOB], [Opplagsliste 13].BÅT AS [Opplagsliste 13_BÅT], [Opplagsliste 13].LENGDE AS [Opplagsliste 13_LENGDE], [Opplagsliste 13].BREDDE AS [Opplagsliste 13_BREDDE], [Opplagsliste 14].NAVN AS [Opplagsliste 14_NAVN], [Opplagsliste 14].MOB AS [Opplagsliste 14_MOB], [Opplagsliste 14].BÅT AS [Opplagsliste 14_BÅT], [Opplagsliste 14].LENGDE AS [Opplagsliste 14_LENGDE], [Opplagsliste 14].BREDDE AS [Opplagsliste 14_BREDDE], [Opplagsliste 15].NAVN AS [Opplagsliste 15_NAVN], [Opplagsliste 15].MOB AS [Opplagsliste 15_MOB], [Opplagsliste 15].BÅT AS [Opplagsliste 15_BÅT], [Opplagsliste 15].LENGDE AS [Opplagsliste 15_LENGDE], [Opplagsliste 15].BREDDE AS [Opplagsliste 15_BREDDE], [Opplagsliste 16].NAVN AS [Opplagsliste 16_NAVN], [Opplagsliste 16].MOB AS [Opplagsliste 16_MOB], [Opplagsliste 16].BÅT AS [Opplagsliste 16_BÅT], [Opplagsliste 16].LENGDE AS [Opplagsliste 16_LENGDE], [Opplagsliste 16].BREDDE AS [Opplagsliste 16_BREDDE], [Opplagsliste 17].NAVN AS [Opplagsliste 17_NAVN], [Opplagsliste 17].MOB AS [Opplagsliste 17_MOB], [Opplagsliste 17].BÅT AS [Opplagsliste 17_BÅT], [Opplagsliste 17].LENGDE AS [Opplagsliste 17_LENGDE], [Opplagsliste 17].BREDDE AS [Opplagsliste 17_BREDDE], [Opplagsliste 18].NAVN AS [Opplagsliste 18_NAVN], [Opplagsliste 18].MOB AS [Opplagsliste 18_MOB], [Opplagsliste 18].BÅT AS [Opplagsliste 18_BÅT], [Opplagsliste 18].LENGDE AS [Opplagsliste 18_LENGDE], [Opplagsliste 18].BREDDE AS [Opplagsliste 18_BREDDE], [Opplagsliste 19].NAVN AS [Opplagsliste 19_NAVN], [Opplagsliste 19].MOB AS [Opplagsliste 19_MOB], [Opplagsliste 19].BÅT AS [Opplagsliste 19_BÅT], [Opplagsliste 19].LENGDE AS [Opplagsliste 19_LENGDE], [Opplagsliste 19].BREDDE AS [Opplagsliste 19_BREDDE], [Opplagsliste 2].NAVN AS [Opplagsliste 2_NAVN], [Opplagsliste 2].MOB AS [Opplagsliste 2_MOB], [Opplagsliste 2].BÅT AS [Opplagsliste 2_BÅT], [Opplagsliste 2].LENGDE AS [Opplagsliste 2_LENGDE], [Opplagsliste 2].BREDDE AS [Opplagsliste 2_BREDDE], [Opplagsliste 20].NAVN AS [Opplagsliste 20_NAVN], [Opplagsliste 20].MOB AS [Opplagsliste 20_MOB], [Opplagsliste 20].BÅT AS [Opplagsliste 20_BÅT], [Opplagsliste 20].LENGDE AS [Opplagsliste 20_LENGDE], [Opplagsliste 20].BREDDE AS [Opplagsliste 20_BREDDE], [Opplagsliste 21].NAVN AS [Opplagsliste 21_NAVN], [Opplagsliste 21].MOB AS [Opplagsliste 21_MOB], [Opplagsliste 21].BÅT AS [Opplagsliste 21_BÅT], [Opplagsliste 21].LENGDE AS [Opplagsliste 21_LENGDE], [Opplagsliste 21].BREDDE AS [Opplagsliste 21_BREDDE], [Opplagsliste 3].NAVN AS [Opplagsliste 3_NAVN], [Opplagsliste 3].MOB AS [Opplagsliste 3_MOB], [Opplagsliste 3].BÅT AS [Opplagsliste 3_BÅT], [Opplagsliste 3].LENGDE AS [Opplagsliste 3_LENGDE], [Opplagsliste 3].BREDDE AS [Opplagsliste 3_BREDDE], [Opplagsliste 4].NAVN AS [Opplagsliste 4_NAVN], [Opplagsliste 4].MOB AS [Opplagsliste 4_MOB], [Opplagsliste 4].BÅT AS [Opplagsliste 4_BÅT], [Opplagsliste 4].LENGDE AS [Opplagsliste 4_LENGDE], [Opplagsliste 4].BREDDE AS [Opplagsliste 4_BREDDE], [Opplagsliste 5].NAVN AS [Opplagsliste 5_NAVN], [Opplagsliste 5].MOB AS [Opplagsliste 5_MOB], [Opplagsliste 5].BÅT AS [Opplagsliste 5_BÅT], [Opplagsliste 5].LENGDE AS [Opplagsliste 5_LENGDE], [Opplagsliste 5].BREDDE AS [Opplagsliste 5_BREDDE], [Opplagsliste 6].NAVN AS [Opplagsliste 6_NAVN], [Opplagsliste 6].MOB AS [Opplagsliste 6_MOB], [Opplagsliste 6].BÅT AS [Opplagsliste 6_BÅT], [Opplagsliste 6].LENGDE AS [Opplagsliste 6_LENGDE], [Opplagsliste 6].BREDDE AS [Opplagsliste 6_BREDDE], [Opplagsliste 7].NAVN AS [Opplagsliste 7_NAVN], [Opplagsliste 7].MOB AS [Opplagsliste 7_MOB], [Opplagsliste 7].BÅT AS [Opplagsliste 7_BÅT], [Opplagsliste 7].LENGDE AS [Opplagsliste 7_LENGDE], [Opplagsliste 7].BREDDE AS [Opplagsliste 7_BREDDE], [Opplagsliste 8].NAVN AS [Opplagsliste 8_NAVN], [Opplagsliste 8].MOB AS [Opplagsliste 8_MOB], [Opplagsliste 8].BÅT AS [Opplagsliste 8_BÅT], [Opplagsliste 8].LENGDE AS [Opplagsliste 8_LENGDE], [Opplagsliste 8].BREDDE AS [Opplagsliste 8_BREDDE], [Opplagapplagsliste 25].LENGDE, [Opplagsliste 25].BREDDE, opplagsliste.NAVN AS opplagsliste_NAVN, opplagsliste.MOB, opplagsliste.BÅT, opplagsliste.LENGDE, opplagsliste.BREDDE, [Opplagsliste 28].NAVN AS [Opplagsliste 28_NAVN], [Opplagsliste 28].PTLF, [Opplagsliste 28].MOB, [Opplagsliste 28].BÅT, [Opplagsliste 28].LENGDE, [Opplagsliste 28].BREDDE, [Opplagsliste 9].PLASS, [Opplagsliste 9].BÅTFABRIKAT, [Opplagsliste 8].PLASS, [Opplagsliste 8].BÅTFABRIKAT, [Opplagsliste 7].PLASS, [Opplagsliste 7].BÅTFABRIKAT, [Opplagsliste 6].PLASS, [Opplagsliste 6].BÅTFABRIKAT, [Opplagsliste 4].PLASS, [Opplagsliste 4].BÅTFABRIKAT, [Opplagsliste 3].PLASS, [Opplagsliste 3].BÅTFABRIKAT, [Opplagsliste 21].PLASS, [Opplagsliste 21].BÅTFABRIKAT, [Opplagsliste 20].PLASS, [Opplagsliste 20].BÅTFABRIKAT, [Opplagsliste 2].PLASS, [Opplagsliste 2].BÅTFABRIKAT, [Opplagsliste 19].PLASS, [Opplagsliste 19].BÅTFABRIKAT, [Opplagsliste 18].PLASS, [Opplagsliste 18].BÅTFABRIKAT, [Opplagsliste 17].PLASS, [Opplagsliste 17].BÅTFABRIKAT, [Opplagsliste 15].PLASS, [Opplagsliste 15].BÅTFABRIKAT, [Opplagsliste 14].PLASS, [Opplagsliste 14].BÅTFABRIKAT, [Opplagsliste 13].PLASS, [Opplagsliste 13].BÅTFABRIKAT, [Opplagsliste 12].PLASS, [Opplagsliste 12].BÅTFABRIKAT, [Opplagsliste 11].PLASS, [Opplagsliste 11].BÅTFABRIKAT, [Opplagsliste 10].PLASS, [Opplagsliste 10].BÅTFABRIKAT, opplagsliste.BÅTFABRIKAT, opplagsliste.PLASS, [Opplagsliste 16].PLASS, [Opplagsliste 16].BÅTFABRIKAT, [Opplagsliste 5].PLASS, [Opplagsliste 5].BÅTFABRIKAT, [Opplagsliste 22].PLASS, [Opplagsliste 22].BÅTFABRIKAT, [Opplagsliste 23].PLASS, [Opplagsliste 23].BÅTFABRIKAT, [Opplagsliste 25].PLASS, [Opplagsliste 25].BÅTFABRIKAT, [Opplagsliste 26].PLASS, [Opplagsliste 26].BÅTFABRIKAT, [Opplagsliste 27].PLASS, [Opplagsliste 27].BÅTFABRIKAT, [Opplagsliste 24].PLASS, [Opplagsliste 24].BÅTFABRIKAT, [Opplagsliste 28].PLASS, [Opplagsliste 28].BÅTFABRIKAT, bestilling_ut.Låst, opplagsliste.NAVN, [Opplagsliste 29].NAVN, [Opplagsliste 29].MOB, [Opplagsliste 29].BÅT, [Opplagsliste 29].LENGDE, [Opplagsliste 29].BREDDE, [Opplagsliste 29].PLASS, [Opplagsliste 29].BÅTFABRIKAT, [Opplagsliste 30].NAVN, [Opplagsliste 30].MOB, [Opplagsliste 30].BÅT, [Opplagsliste 30].LENGDE, [Opplagsliste 30].BREDDE, [Opplagsliste 30].PLASS, [Opplagsliste 30].BÅTFABRIKAT, bestilling_ut.Medlemsnr29, bestilling_ut.Medlemsnr30
FROM [Opplagsliste 30] RIGHT JOIN ([Opplagsliste 29] RIGHT JOIN (opplagsliste RIGHT JOIN ([Opplagsliste 28] RIGHT JOIN ([Opplagsliste 24] RIGHT JOIN ([Opplagsliste 27] RIGHT JOIN ([Opplagsliste 26] RIGHT JOIN ([Opplagsliste 25] RIGHT JOIN ([Opplagsliste 23] RIGHT JOIN ([Opplagsliste 22] RIGHT JOIN ([Opplagsliste 5] RIGHT JOIN ([Opplagsliste 16] RIGHT JOIN ([Opplagsliste 10] RIGHT JOIN ([Opplagsliste 11] RIGHT JOIN ([Opplagsliste 12] RIGHT JOIN ([Opplagsliste 13] RIGHT JOIN ([Opplagsliste 14] RIGHT JOIN ([Opplagsliste 15] RIGHT JOIN ([Opplagsliste 17] RIGHT JOIN ([Opplagsliste 18] RIGHT JOIN ([Opplagsliste 19] RIGHT JOIN ([Opplagsliste 2] RIGHT JOIN ([Opplagsliste 20] RIGHT JOIN ([Opplagsliste 21] RIGHT JOIN ([Opplagsliste 3] RIGHT JOIN ([Opplagsliste 4] RIGHT JOIN ([Opplagsliste 6] RIGHT JOIN ([Opplagsliste 7] RIGHT JOIN ([Opplagsliste 8] RIGHT JOIN ([Opplagsliste 9] RIGHT JOIN bestilling_ut ON [Opplagsliste 9].MNR = bestilling_ut.Medlemsnr9) ON [Opplagsliste 8].MNR = bestilling_ut.Medlemsnr8) ON [Opplagsliste 7].MNR = bestilling_ut.Medlemsnr7) ON [Opplagsliste 6].MNR = bestilling_ut.Medlemsnr6) ON [Opplagsliste 4].MNR = bestilling_ut.Medlemsnr4) ON [Opplagsliste 3].MNR = bestilling_ut.Medlemsnr3) ON [Opplagsliste 21].MNR = bestilling_ut.Medlemsnr21) ON [Opplagsliste 20].MNR = bestilling_ut.Medlemsnr20) ON [Opplagsliste 2].MNR = bestilling_ut.Medlemsnr2) ON [Opplagsliste 19].MNR = bestilling_ut.Medlemsnr19) ON [Opplagsliste 18].MNR = bestilling_ut.Medlemsnr18) ON [Opplagsliste 17].MNR = bestilling_ut.Medlemsnr17) ON [Opplagsliste 15].MNR = bestilling_ut.Medlemsnr15) ON [Opplagsliste 14].MNR = bestilling_ut.Medlemsnr14) ON [Opplagsliste 13].MNR = bestilling_ut.Medlemsnr13) ON [Opplagsliste 12].MNR = bestilling_ut.Medlemsnr12) ON [Opplagsliste 11].MNR = bestilling_ut.Medlemsnr11) ON [Opplagsliste 10].MNR = bestilling_ut.Medlemsnr10) ON [Opplagsliste 16].MNR = bestilling_ut.Medlemsnr16) ON [Opplagsliste 5].MNR = bestilling_ut.Medlemsnr5) ON [Opplagsliste 22].MNR = bestilling_ut.Medlemsnr22) ON [Opplagsliste 23].MNR = bestilling_ut.Medlemsnr23) ON [Opplagsliste 25].MNR = bestilling_ut.Medlemsnr25) ON [Opplagsliste 26].MNR = bestilling_ut.Medlemsnr26) ON [Opplagsliste 27].MNR = bestilling_ut.Medlemsnr27) ON [Opplagsliste 24].MNR = bestilling_ut.Medlemsnr24) ON [Opplagsliste 28].MNR = bestilling_ut.Medlemsnr28) ON opplagsliste.MNR = bestilling_ut.Medlemsnr) ON [Opplagsliste 29].MNR = bestilling_ut.Medlemsnr29) ON [Opplagsliste 30].MNR = bestilling_ut.Medlemsnr30;

Open in new window

0
 

Author Comment

by:VikingEgil
ID: 35431265
Changed aliases for all the queries (Opplagsliste 1-32) to "o1", "o2", but I still get "query too complex".
I double checked that the names were replaced in the SQL code.
Too bad, that would have been the perfect fix, as I saw that changing the alias didn't actually change the name.
I was worried other references to it, like in macros and vba would fail.

Do you want me to post the DB itself in any form?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35431635
can you explain wht you have to have these 31 identical queries.

is each one querying the same "main" table with a different where clause?

could you use one underlying query ( which we could generate dynamically from some code under a button) to accomplish the same task without the need for these "1 query per timeslot" logic

0
 

Author Comment

by:VikingEgil
ID: 35432166
Yes, thats right, they are all querying the same main table, and are excactly identical, only with a different name.
The reason i did this is because it was the only way I was able to make the information about each boat appear as the membernumber was typed in.
I figured I needed to make relations between all the fields in the schedule table to the table with the information about the boats, but I wasn't able to relate all the fields to the same table.
It's a bit hard to explain, I will add a sceenshot of the DB, maybe it gets a bit clearer.
There is no doubt that I should have done this some other way, I just don't know which

All the fields with "0" in them are from the schedule table, and is where the membernumber is to be typed in.
All the other fields are from the member-table, where the first row is the actual table and the 27 beneath are queries of the same table.
It's norwegian, and the fields labels are "Name, phone, boattype, lenght, width, place and fabricate".
DB-Screen.jpg
0
 

Author Comment

by:VikingEgil
ID: 35432322
Having one underlying query would be great, I would just need a hint of how to pull that off in my layout.

The two other queries that have been suggested might also work (Union-query or 32 different queries with a final query to collect the result).

What would be easiest to implement?

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35432938
are you using these queries so that you can generate columns in the record set?

is there a column for each slot?

i am struggling to visualise why you have all these identical queries.

can you post the sql from a few of them so we can see their differences, and the sql from the big master query which uses all the sub queries,

so we can see if either can be simplified further.
0
 

Author Comment

by:VikingEgil
ID: 35433182
There is a row for each slot, and a query for each row.
I also wonder why I need so many queries, but I coudn't make it work any other way :\

The SQL for the master query is already posted further up.
The SQL for each query of the membertable is identical and look like this:
SELECT opplagsliste.MNR, opplagsliste.NAVN, opplagsliste.PTLF, opplagsliste.MOB, opplagsliste.BÅT, opplagsliste.LENGDE, opplagsliste.BREDDE, opplagsliste.KVM, opplagsliste.BOMKORT, opplagsliste.PLASS, opplagsliste.BÅTFABRIKAT
FROM opplagsliste;

Open in new window


Here is what i was thinking while making the layout, and I appreciate any corrections :)

In the main form, the schedule-form, I have 28 colummns named Membernumber1 - 28.
This form basically contains which 28 membernumbers that are scheduled for each day, one record is 1 day.
Each of the membernumbers that exists have a record in the membertable with all the information of that boat (membernumbers is assigned to boats not people).
So to extract this information and show it in the fields to the right of the box with the membernumber as it is typed in, I will need to relate each membernumer-slot of the chedule-table to the table with the information of the boat.
As I can't just use the same table to create these fields for each slot (how would the DB know which duplicates of the fields was to show the info of which boat?), I made 27 queries and included all the fields from the queries also in the main query.
I related each slot to a separate query, so that I could place separate connected fields to each slot.
That way I could add the "same" 7 fields 28 times, each row showing the information from the membertable according to the membernumber in the slot.

Is there any way to do this with just one query?
There should be, but I just haven't figured it out.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35433239
woa!

your fields are all hard coded!  i see.

so each of those rows is mapped to the relevant column in the recordset.

i don;t think there is a way to do this without re-writing it :(

you need to have your data normalised i think.

your form should have the to part as those details, and each of your swedish flag coloured rows should be a single record in a linked table.

sorry to bring the bad news, but i don't see how else it could be done now youve reached the limit of the number of fields you can have in a query.
0
 

Author Comment

by:VikingEgil
ID: 35433965
I hadn't really expected an easy soulution to this question, so that's okay :)

I used Experts-exchange when I created this DB too, and then too I was recomended to "normalise the data", unfortunately I didn't know what that meant.

You say "the limit of the number of fields"..
I don't really need all the fieds in the table, could it be an idea to remove a field or two from the queries, or is the limit the number of connected queries?
0
 

Author Comment

by:VikingEgil
ID: 35435785
No, that didn't work at all :p

So I'm basically stuch with 30 slots max?
0
 

Author Comment

by:VikingEgil
ID: 35438353
What if I don't use queries at all, and instead program each of the fields with VBA fields, to automatically look up the right data from the member-form, according to the membernumber in the slot?
Is that possible?

Like if the slot MNR1 har the value SBF3243, all the fields to the right will look up the information about this boat in the table.

That would be easy to implement, and would make me able to get rid of the ridiculous layout.

If I could get helt with the VBA code of one such field (if possible), I would easily be able to make the rest.
0
 

Author Comment

by:VikingEgil
ID: 35438693
It would have to be "form current" and "update" of the slots, something like:

Private sub form_current()
Name1 = Dlookup("[Name]",  "Membertable", "Membernumber=Me.Memberslot1")
End sub

I'm not very steady on VBA, but I beliebe I'm on to something here :)
I guess this should have been posted in a VBA zone.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35438911
can you post up a copy of the whole mdb, and i'll see if i can find a quick way to seperate the tables (normalise) and make it easier as a starting point to develop from.

might have a some time over the coming eater weekend.
0
 

Author Comment

by:VikingEgil
ID: 35439246
Sure I could, but my DB is secured, and needs the .mdw file as a parameter.
Should I post the .mdb and the .mdw, or should I remove the security?
There is some bug on my server that prevents me from using the builtin security wizard in access, is there an easy way to make an unsecured copy of the db?
0
 

Author Comment

by:VikingEgil
ID: 35439777
Figured it out :)
Here it is.
It's all in norwegian, so if you take a look at it, just ask if you want to know what is what.
Also it is in 2000 format, so the form won't work with 2007 by some reason, gets an error on the format-function of the date. Must be a slightly different syntax or something.

But the idea of making a Dlookup for every field instead of a underlying query is no good?
Karenslyst-unsecured.mdb
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 1000 total points
ID: 35440289
right.

i'd definatelsuggest trimming down Bestillin Sporring 1 to remove fields you do not need.

you have all those sub queries in that query which is odd.  there has to be a better way to do this without you having to rebuild the whole thing.

instead of this one table with date column, and a cluster of the same fields for each medlemsnr you should have a table with date, medlemsnr id (1-30) and then the otherfields with medlemsnr data.

not sure how that would affect your big daily form..
0
 

Author Comment

by:VikingEgil
ID: 35446263
Yes, but no matter how many fields i remove, I don't really get to connect more tables.
I will use the tips I have gotten here to create the DB from scratch some time when I have the time and the boat-season is over, but right now I just need to get the last two slots added any way possible.

So in the meantime I think I will attempt to use Dlookup just for the 2 last slots that didn't fit due to the limit.

I'm not following you completely on your last tip there, the schedule table is currently a date and 30 timeslots, are you saying I should add the fields with the boat info there too, instead of in separate tables/queries?
That would be a LOT of fields in the table, and I don't know if the fields would be automatically updateted with the info as soon as one enters a membernumber?

Anyway, thanks a lot for having a look :)
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 35447963
<<So in the meantime I think I will attempt to use Dlookup just for the 2 last slots that didn't fit due to the limit.>>

  Another approach would be to write a function to return the value, but Dlookup() will get you through.  Just don't do that for all the other joins as well (you will have a performance problem).

<<I was not very familiar with either access or VBA when i made the DB, so I think I got off to a bad start, and now I am stuck with the layout.>>

  yes, you did and this is why your having a problem with the queries.  Your database design needs to be changed.  You have several tables with repeating fields.  For example, SommerOpp.  The time solts should be represented by records in the table and not be appearing as fields.  Read on.  This will be clearer in a minute.

<<I'm not following you completely on your last tip there, the schedule table is currently a date and 30 timeslots, are you saying I should add the fields with the boat info there too, instead of in separate tables/queries?>>

 No, what he's suggesting is that your table should look like this for SommerOpp:

Date - Date
TimeSlot - Number 1 - 21 or actual time
Medlemsnr

  so you would have 21 records in the table for each day, not one record with 21 fields.  That's why you need so many joins.  If the table looked like this, you would only need ONE join to opplagsliste for all the records.

  Here is another question to ask that shows the point; if you wanted to add more time slots because your work day is now longer, can you do that without changing the design of a table?

  With your design no (you would need to add more fields, queries, joins, etc).  With a table design like the one above, you would simply add more records to a table.  Your changing the data, not the design.

Hope that helps...
JimD.




0
 

Author Comment

by:VikingEgil
ID: 35449338
Wow, thanks, that was acutally really clarifying!

I totally get the idea of one record per slot instead of one record per day, but it would of course require a redign of the DB.
I will do this next fall, as the forms are not used for a couple of months then,

As for the question, I would currently have to add more fields to the table, but with a record for each schedule, I guess I would only have to change the number of records per day.

But would it still be possible to keep the current form layout, with one page per day?
In a way connect 28 records of the schedule-table to every page of the form?
0
 
LVL 58
ID: 35449970
<<But would it still be possible to keep the current form layout, with one page per day?>>

  Yes.  The simplest would be a sub-form in continious view.  The main form would control the date and the subform would display all the slots for that date.

JimD.
0
 

Author Comment

by:VikingEgil
ID: 35450113
Sounds great, I think I understand how to pull that off.
Thanks a lot for the guidance!
I will try my best to implement this, althought I might make another question if it gets beyond my level of experience :)
I regret that I did not pay very well attantion to the subject of sub-forms in Access-class, which is why there is no such thing in my DB.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question