Using form to select table for a query

[ToffeeMark]

Hi, been using access for a little while but only just started using forms. If anyone has a good tutorial they can point me to that would be great.

One thing I was wondering was, Is it possible to have an option in forms where you can select the table to be used in a query? same query, but just have the table used as a parameter

for example if i have 12 tables, Jan - Dec. Could I give the user an option to compare Jan Vs Dec, or Jun vs Dec etc by selecting the tables from a drop down in the form.

e.g.

SELECT [January].*
FROM [January];

but Instead have something in the spirit of:

SELECT [ "form Select Table Name"].*
FROM [ "form Select Table Name"];

If anyone could point me in the right direction about what terminology I should be searching, I don't might reading around a bit.

Many thanks

 

[Minty]

You would have to build the RecordSoucre for the form in VBA but it would be possible.
Something like this in the after update event of your combo;

Dim sSql As String

sSql = "SELECT * FROM " &  Me.YourTableCombo & "  ;"

Me.recordsource = sSql

But - I do hope that you don't have 12 tables for Jan, Feb March etc...
That would be a very very bad design.

 

[ToffeeMark]

Well I actually have weekly directory's that are 130K rows long. What I've set up so far is to query the newest versus the previous to see what's new. Might be 20-30 new rows per week...

I was thinking about giving the option to pick the week so you could see changes over a longer period. which is why I was wondering if you could build the query through selecting the table. It probably is a a bad design tbf, but its only a concept at the moment so see what I could use it for. If you have any suggestions, let me know, I'm self taught and doing ok but welcome any suggestions re good design principles.

 

[Mark_]

A Date/year/month/day should never be part of a table as it is data. Much like you would never name a table "Queen Victoria". This is data and should be within the table itself.

You can do queries that return a given month, week, year, or other such, but you would never separate your data into distinct tables like this. I would suggest reading up on normalization.

 

[missinglinq]

A Date/year/month/day should never be part of a table...

Mark meant that 'a Date/year/month/day should never be part of a table's name.'

And his advice is dead on...ignoring this advice would mean that your database was horribly non-normalized...a cardinal sin in relational databases...and would also mean all kinds of unnecessary problems and extra work...both in the original designing of and also in the maintaining your app!

Linq ;0)>

 

[ToffeeMark]

I like the feedback I'm getting so thank you.

The problem I've got I guess is that I have a 130K rows a week and no change indicators.

So from a Normalisation point of view I suppose I would be better using the weekly files to update 1 master sheet, and only append new additions with a date indicator, and mark lines that disappear as discontinued.

The only downside there is that each weekly file has 6 sheets, each with multiple columns and I'm going to have to figure out how to track changes in each sheet and then add date information to indicate when things changed.

I'll do a bit of searching, thanks again for the valuable input.

 

[Minty]

You can load a excel spreadsheet worksbooks into a temporary "staging" table , perform your checks, then import the records you need.

If the workbooks are of the same layout you could loop around them by sheet name using the same code to process them.