have one form open many tables

[MartinL]

I need to have 52 separate tables for each week of the year. I decided to do
this instead of having just one table because I need for the users to simply
copy from excel and paste append to access. The weekly information is then
updated and shared by many users. Any way what I am trying to do is to have
only one form to open each of the tables. The table design for all of them is
exactly the same. I have a switchboard with command buttons for week so I
would like to indicate my forms Recordsource which button is being clicked.

Thanks in advance!
Martin L.

 

[Klatuu]

Your design is not correct.
You should have only one table.
Your query should append to that table. You can add a field to the table to
identify the week the data belongs to. Then in your append query create a
calculated control that determines the week of the year. That calculation
can be used to populate the week field.

Now, if you want to show data for only a specific week, you can filter your
form based on that week.

Here is a formula that will return the year and the week as a 6 character
string as yyyyww:

Year(Date) & format(datepart("ww",#1/1/2009#),"00")

So in the query designer the field would be something like:

WeekInfo: Year(Date) & format(datepart("ww",#1/1/2009#),"00")

Believe me, using 52 different tables is really, really wrong. It will
create more problems than it will solve. You have, so far, discovered only
one of them.

 

[MartinL]

Your design is not correct.
You should have only one table.
Your query should append to that table. You can add a field to the table to
identify the week the data belongs to. Then in your append query create a
calculated control that determines the week of the year. That calculation
can be used to populate the week field.

Now, if you want to show data for only a specific week, you can filter your
form based on that week.

Here is a formula that will return the year and the week as a 6 character
string as yyyyww:

Year(Date) & format(datepart("ww",#1/1/2009#),"00")

So in the query designer the field would be something like:

WeekInfo: Year(Date) & format(datepart("ww",#1/1/2009#),"00")

Believe me, using 52 different tables is really, really wrong. It will
create more problems than it will solve. You have, so far, discovered only
one of them.
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Thanks for your input. Just on question, what is the simplest way for the 25
users to copy from excel and update to access and also have the week field
updated automatically. I really need to keep this simple for the users.

Thanks again.

 

[Klatuu]

Create the query that appends the data from the Excel sheet to the your table.
Put a button on a form to perform the operation.

The hardest part will be to allow the user to identify the path to the
workbook and which sheet in the book to import. That requires using either
the Common Dialog ActiveX control or you can use the code from
http://www.mvps.org/access/api/

Select the first option Call Windows File Open/Save Dialog box and
copy/paste the code into a standard module.

That will allow you to select the Excel file you want to import from.

Then use the TransferSpreadsheet to import from the file.

Here is an example from one of my applications:

Do While True
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
"\\rsltx1-bm01\busmgmt\Vought " & Me.txtCurrYear & "\Pipeline\", _
"Excel Spreadsheets (*.xls) *.xls", , _
"xls", Me!txtCurrYear & " " & Left(Me!cboPeriod.Column(1), 3) _
& " Pipeline.xls", "Select Pipeline", , True)
If varGetFileName = "" Then
If MsgBox("Cancel Import?", vbQuestion + vbYesNo, "Import
PipeLine") _
= vbYes Then
Exit Sub
End If
Else
Exit Do
End If
Loop

DoCmd.TransferSpreadsheet acLink, 8, "PipelineLink", _
varGetFileName, True, "80%!A3300"

Note this example links to the worksheet. You may want to use the acImport
instead. I used it this way because sometimes the data in the worksheet was
not correct and I then had to manipulate it before I could append it to the
table.

 

[tina]

i can't think of anything you could do, or set up for your users to do, that
would be made easier or simpler by creating 52 identical tables. that is
flat-file (spreadsheet) design, not relational design, which is going to
make it EXTREMELY difficult to utilize the power of Access - might just as
well keep the data in Excel spreadsheet format.

if you can tell us *what* you're trying to do, rather than *how* you think
it must be done, we can probably help you build a solution that will be easy
for your users while adhering to relational design principles.

hth

 

[MartinL]

i can't think of anything you could do, or set up for your users to do, that
would be made easier or simpler by creating 52 identical tables. that is
flat-file (spreadsheet) design, not relational design, which is going to
make it EXTREMELY difficult to utilize the power of Access - might just as
well keep the data in Excel spreadsheet format.

if you can tell us *what* you're trying to do, rather than *how* you think
it must be done, we can probably help you build a solution that will be easy
for your users while adhering to relational design principles.

hth

Actually the only reason I moved to Access is because we previously had a
shared Excel file in a Sharepoint, but due to the update restrictions we had
to move to Access which is a multisuer application. In other words we only
need to have the flat file desgin because the main purpose is to have the 25
users update their information and for it to be viewable by the managers and
supervisors. Our company is in manufacturing so it's very important to have
the information up to date.

 

[John W. Vinson]

Actually the only reason I moved to Access is because we previously had a
shared Excel file in a Sharepoint, but due to the update restrictions we had
to move to Access which is a multisuer application. In other words we only
need to have the flat file desgin because the main purpose is to have the 25
users update their information and for it to be viewable by the managers and
supervisors. Our company is in manufacturing so it's very important to have
the information up to date.

That's a VERY good reason for following Tina's excellent advice and properly
normalizing your data.

Having the information scatterd over 25 tables will make it much HARDER to be
sure it's accurate and up to date. Having it in a single properly normalized
table will make it EASIER.

 

[MartinL]

I really appreciate your comments regarding the best practice using Access
but the weekly information is only used for the upcoming production week. So
the information is valid only from week zero which is the current week thru
week one, after that the information is no longer useful. That's why it is
not important to have it in a relational model but just a flat model. And
basically I'm stuck with either using Excel or using Access and since Access
is multiuser I'm sitcking with that. So if you know how to open a Form from a
control button with a specific record source please let me know. Actually my
boss asked me since last Friday that I must have this running by Monday on
Access and the way I implemented it is creating one table for week 26 and one
form for that table, so next week I will need to create the table for week 27
and the form for week 27 and so on. But even that is better than having the
shared Excel on our Sharepoint which is very limited regarding the number of
users and also not as reliable as Access.

Thanks again for your comments.
ML

 

[MartinL]

I found the following code in one of the other discussions regarding this but
I can't seem to make it work. Any suggestion is appreciated.

forms!MyFormNameHere.RecorSource = "MyTableOrQueryNameHere"

Thanks.

ML

 

[John W. Vinson]

I found the following code in one of the other discussions regarding this but
I can't seem to make it work. Any suggestion is appreciated.

forms!MyFormNameHere.RecorSource = "MyTableOrQueryNameHere"

Thanks.

ML

The property is RecordSource, not RecorSource - does that help?

 

[tina]

i work in manufacturing myself; i'm a clerk in a factory that makes rubber
o-rings and custom parts, so i understand your concerns about current data -
though i'd imagine that most any company needs current, valid data for
decision-making purposes.

so far, looks like 1) you know how to import data into a table, and 2) you
know how to bind a table (or query?) to a form. so far, so good.

i personally hate working with Excel files, and don't do it if i can
possibly avoid it - so i'm going to take the easy route and suggest that you
import the Excel data to a temporary table. then create an Append query to
add the records to a permanent table. include a date field in the permanent
table to indicate either the date of import, or a date from the week that
the data represents - what date you use depends on your business needs. i'm
suggesting a date field because from a date value you can derive a specific
week, a specific month, or quarter, or year, as needed now *or* potentially
needed in the future. i'd also recommend you don't get rid of "obsolete"
records after the week of direct interest has passed; keeping those records
won't hurt anything, and you never know when analyzing old data may prove
useful, so might as well hang on to them.

now that the data is being ultimately stored in a single table, with a date
field that allows you to classify each record into an appropriate group (wk
26 of 2009, wk 27 of 2009, etc), build *one* form to bind to the table - or
more likely, to a query based on the table. you can build the form to
automatically show the records for a specific group (this week, last week,
whatever) by default. and you can give the user the ability to choose a
different group than the default, and display those records instead.

also, once you've set up the import, and the append query, once, you can use
VBA code or a macro to automate that process, so next time (and the time
after, and the time after that) you can do it all by clicking a button.

i know that the above is general, not specific, but i'm trying to explain
the ability that Access will give you, to provide your customers with the
data they need at their fingertips. for specific how-to instructions, we
need to know specific information, so: suggest you start by importing the
data into a temporary table, creating a permanent table, and building an
Append query to get the data from the temp table into the perm table - with
the date value discussed above. come back with specific questions you may
have as you take these steps. then create the form to display the data. and
we'll go on from there.

hth

 

[MartinL]

Tina, thanks for the suggestion. I will need to work on your suggestion when
I have a little more extra time. The problem is that in our company we build
mid to heavy duty trucks and our current production schedule is so short we
are doing all kinds of stuff in order to get our parts on time for production
so that doesn't leave much extra time because I'm in inventory control. I
appreciate your support.

ML

 

[tina]

you're welcome.

i did a small demo for you, to show the automated-import setup and the
user-can-filter-data-in-a-form setup. it's a small A2000 db, built in A2003,
and an A97 version, and a tiny Excel file with a few rows of dummy data -
total 53KB zipped together. if you'd like to see it, post a disguised
version of your email address and i'll send it to you.

hth