RE: Table as Criteria
- From: "Marcelo" <Marcelo@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 3 Aug 2005 13:23:02 -0700
Brian, I found my answer. What I would like to do was a UNION operation.
Thanks for all help!
"Brian" escreveu:
> I don't quite understand. Can you give an example or a littel more detail?
>
> "Marcelo" wrote:
>
> > Brian, just perfect! Thanks a lot!
> >
> > Actually I have some Queries from all these tables, and this Append method
> > do not permit to append queries, at least I couldn't do this.
> >
> > Any idea?
> >
> >
> > "Brian" escreveu:
> >
> > > Do a backup first...
> > >
> > > First of all, pick one table to be used as the master. Let's say you start
> > > with May. Add an extra field for the month (assuming that each of your
> > > existing tables was for a separate month). Don't call it "Month", though,
> > > because this is a reserverd word in Access, like "Date". Call it MonthID or
> > > something.
> > >
> > > Run an update query for the master table to update its MonthID entry to
> > > "May" . You should now see that all the records in the table have "May" in
> > > the MonthID.
> > >
> > > (You may want to actually store the first day of each month in the MonthID,
> > > or next year's May entries will look like they are in the same month as this
> > > year's May entries.)
> > >
> > >
> > >
> > > Now, create an append query that appends * from your June table to the
> > > master table. In the query builder, add another field that is just "June" ,
> > > and set this to append to the MonthID field. Run the query. You should now
> > > see May & June records in the table, with the respective MonthID entry.
> > >
> > > Now, change two things in the query: draw the recrods from the July table
> > > (still appending to the master table) and change the "June" to "July" in the
> > > field entry for MasterID. Keep doing this until you are done with all the
> > > excess tables.
> > >
> > > When you are done, everything will be in one table, but each line will have
> > > a MonthID that will identify which month's entry this is for.
> > >
> > > Hint: if you want to ensure just ONE entry per customer per month, you can
> > > go to the master table's design view, highlight the Customer and MonthID
> > > fields together, and click the Primary Key button (assuming you don't have or
> > > need another primary key). This will prevent more than one entry in this
> > > table per month for a given customer.
> > >
> > > You can still use an AutoNumber field to have a single entry that is unique
> > > for each record; the AutoNumber field does not have to be the primary key.
> > >
> > > Once you have all of this working, delete the individual month-related
> > > tables, since all the information will be in the master table. It will be a
> > > lot simpler to manage this way, and you can quite simply filter by the month.
> > >
> > > "Marcelo" wrote:
> > >
> > > > Thanks Brian, It's perfect.
> > > >
> > > > About your question, I'm using all these tables because I receive one of
> > > > each customer every month. I can join all of then, that's a good idea.
> > > >
> > > > By the way, how can I join then, I mean, how can I add the data from two or
> > > > more tables in just one?
> > > >
> > > > Thank you again!
> > > >
> > > > Marcelo
> > > >
> > > >
> > > > "Brian" escreveu:
> > > >
> > > > > Build your SQL statement in VBA using the table names picked by the user from
> > > > > a combo box that lists the relevant table names on the form?
> > > > >
> > > > > If your table names are Table1, Table2, etc, you want a SQL string something
> > > > > like this:
> > > > >
> > > > > Select Table1.* from Table1 etc.
> > > > > Select Table2.* from Table2 etc.
> > > > >
> > > > > so you could build it like this:
> > > > >
> > > > > Dim strSQL as String
> > > > > strSQL = "Select " & TableName & ".* from " & TableName
> > > > >
> > > > > This begs a more fundamental design question, however. Is there a pressing
> > > > > reason why you have many tables with the same field names? In general, one
> > > > > would use just one such table, with an extra field that contains the unique
> > > > > data related to the current name of each of the tables. That is, instead of
> > > > > having a table for each month - June, July, August, etc. - just have a single
> > > > > table but add a DataMonth field that contains June, July, or August for each
> > > > > entry. Just an idea...
> > > > >
> > > > > "Marcelo" wrote:
> > > > >
> > > > > > Hi!
> > > > > >
> > > > > > I know VBA and SQL, but I'm new in Access.
> > > > > > I would like to do a dynamic query, but don't know how, so if anybody could
> > > > > > help me I appreciate it.
> > > > > >
> > > > > > What I have: many tables with the same fields, but different data.
> > > > > > What I want: One query where you can choose the table from a list of tables,
> > > > > > combo box, anything.
> > > > > >
> > > > > > Any Idea?
> > > > > >
> > > > > > Thanks, Marcelo.
.
- References:
- Table as Criteria
- From: Marcelo
- RE: Table as Criteria
- From: Brian
- RE: Table as Criteria
- From: Marcelo
- RE: Table as Criteria
- From: Brian
- RE: Table as Criteria
- From: Marcelo
- RE: Table as Criteria
- From: Brian
- Table as Criteria
- Prev by Date: Re: simple criteria question
- Next by Date: Re: Missing dates?
- Previous by thread: RE: Table as Criteria
- Next by thread: Query to group and count demographic data
- Index(es):
Relevant Pages
|
