Append Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three tables
Provider info
Provider Monthly Info
Months

I need to take all 12 months and append them to every provider in provider
info, but I need the information to appear in Provider Monthy Info.

Right now under provider monthy info I have each providers name and then the
month beside it. So each provider is in this table 12 times.

I need to know the easist way to do this. I have attempted this differnt
ways and have had no luck.
 
This is a bit puzzling. (Although you didn't say so, my guess is that
the [Provider Monthly Info] Table contains links to both of the other
Tables.) Are you trying to populate a Table with a bunch of empty
records? This isn't often necessary in a relational database, though
perhaps you're trying to set up a Report with lots of blank space.
(Even then, you might be able to accomplish the same thing via a Query.)

If all you're doing is creating lots of blank records, you might do that
more easily by using MS Excel, then importing the results into Access.

If my guess is correct, that [Provider Monthly Info] includes
information about both providers and months, and perhaps other
information, then you should expect to have a reference (I assume via a
short foreign key) to a [Provider info] record in each record [Provider
Monthly Info] that relates to that provider. Imagine that you were
using paper and ink. Wouldn't you include the provider's name on each
page, or at least clip the pages together? In Access, we don't have
paper clips but can do something similar by including a reference to the
related record.

Incidentally, what kinds of information do you store in the [Months]
Table? I can imagine, perhaps, some creative spellings of the month's
name, or maybe a description of holidays occurring in it, but usually
there's not much interesting about a month that you can't compute by
knowing the date. It's possible that you can completely obliterate the
[Months] Table without affecting the way your database functions, if you
do it properly.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
oh the months table is what I used to make an append query. There is nothing
special to it, just all 12 months.

Vincent Johns said:
This is a bit puzzling. (Although you didn't say so, my guess is that
the [Provider Monthly Info] Table contains links to both of the other
Tables.) Are you trying to populate a Table with a bunch of empty
records? This isn't often necessary in a relational database, though
perhaps you're trying to set up a Report with lots of blank space.
(Even then, you might be able to accomplish the same thing via a Query.)

If all you're doing is creating lots of blank records, you might do that
more easily by using MS Excel, then importing the results into Access.

If my guess is correct, that [Provider Monthly Info] includes
information about both providers and months, and perhaps other
information, then you should expect to have a reference (I assume via a
short foreign key) to a [Provider info] record in each record [Provider
Monthly Info] that relates to that provider. Imagine that you were
using paper and ink. Wouldn't you include the provider's name on each
page, or at least clip the pages together? In Access, we don't have
paper clips but can do something similar by including a reference to the
related record.

Incidentally, what kinds of information do you store in the [Months]
Table? I can imagine, perhaps, some creative spellings of the month's
name, or maybe a description of holidays occurring in it, but usually
there's not much interesting about a month that you can't compute by
knowing the date. It's possible that you can completely obliterate the
[Months] Table without affecting the way your database functions, if you
do it properly.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
I have three tables
Provider info
Provider Monthly Info
Months

I need to take all 12 months and append them to every provider in provider
info, but I need the information to appear in Provider Monthy Info.

Right now under provider monthy info I have each providers name and then the
month beside it. So each provider is in this table 12 times.

I need to know the easist way to do this. I have attempted this differnt
ways and have had no luck.
Click to expand...
Click to expand...
 
Okay a little more on what I am doing. I process grant bills. For every
month I have a bunch of things going to it. ie-Status, amount, anything
wrong with it. There are probably 20 questions that go along with this.
When I add a new provider to the list I am able to add twelve months with the
set value. Now since it is a new year I need to add all the month for each
provider. This is where I am getting confussed. I was able to accomplish
this but, I didn't know how to put it in the table I wanted. I did the
append query and everything looked great. Now I just don't know what to do
with that.

Vincent Johns said:
This is a bit puzzling. (Although you didn't say so, my guess is that
the [Provider Monthly Info] Table contains links to both of the other
Tables.) Are you trying to populate a Table with a bunch of empty
records? This isn't often necessary in a relational database, though
perhaps you're trying to set up a Report with lots of blank space.
(Even then, you might be able to accomplish the same thing via a Query.)

If all you're doing is creating lots of blank records, you might do that
more easily by using MS Excel, then importing the results into Access.

If my guess is correct, that [Provider Monthly Info] includes
information about both providers and months, and perhaps other
information, then you should expect to have a reference (I assume via a
short foreign key) to a [Provider info] record in each record [Provider
Monthly Info] that relates to that provider. Imagine that you were
using paper and ink. Wouldn't you include the provider's name on each
page, or at least clip the pages together? In Access, we don't have
paper clips but can do something similar by including a reference to the
related record.

Incidentally, what kinds of information do you store in the [Months]
Table? I can imagine, perhaps, some creative spellings of the month's
name, or maybe a description of holidays occurring in it, but usually
there's not much interesting about a month that you can't compute by
knowing the date. It's possible that you can completely obliterate the
[Months] Table without affecting the way your database functions, if you
do it properly.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
I have three tables
Provider info
Provider Monthly Info
Months

I need to take all 12 months and append them to every provider in provider
info, but I need the information to appear in Provider Monthy Info.

Right now under provider monthy info I have each providers name and then the
month beside it. So each provider is in this table 12 times.

I need to know the easist way to do this. I have attempted this differnt
ways and have had no luck.
Click to expand...
Click to expand...
 
Sorry, it's still not obvious to me what went wrong. If "everything
looked great," then you probably got the (mostly) blank Table that you
wanted to create, didn't you? Maybe if you post a (small, sanitized)
sample from the relevant Tables, along with what results you want to see
that you didn't get, it would be clearer. If the results wound up in
the wrong Table, you could modify your Append Query to target the Table
to which you wanted to append stuff, or you could copy the appended
records from the new Table and paste them into the old one, or there
might be other ways to take care of it that I haven't thought of. (But
from your description, I'm not really sure that that's what happened.)

Incidentally, setting up a blank Table and filling in data as you get
them might be easier to do in Excel. You could use the Excel worksheet
in your database by creating, in Access, a Table linked to the
worksheet. You could base your Access Queries, Reports, &c., on the
linked Table, using Excel for the data entry. It may be that doing
everything in Access, as I think you're trying to do now, confers no
special advantage on you.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Okay a little more on what I am doing. I process grant bills. For every
month I have a bunch of things going to it. ie-Status, amount, anything
wrong with it. There are probably 20 questions that go along with this.
When I add a new provider to the list I am able to add twelve months with the
set value. Now since it is a new year I need to add all the month for each
provider. This is where I am getting confussed. I was able to accomplish
this but, I didn't know how to put it in the table I wanted. I did the
append query and everything looked great. Now I just don't know what to do
with that.

:

This is a bit puzzling. (Although you didn't say so, my guess is that
the [Provider Monthly Info] Table contains links to both of the other
Tables.) Are you trying to populate a Table with a bunch of empty
records? This isn't often necessary in a relational database, though
perhaps you're trying to set up a Report with lots of blank space.
(Even then, you might be able to accomplish the same thing via a Query.)

If all you're doing is creating lots of blank records, you might do that
more easily by using MS Excel, then importing the results into Access.

If my guess is correct, that [Provider Monthly Info] includes
information about both providers and months, and perhaps other
information, then you should expect to have a reference (I assume via a
short foreign key) to a [Provider info] record in each record [Provider
Monthly Info] that relates to that provider. Imagine that you were
using paper and ink. Wouldn't you include the provider's name on each
page, or at least clip the pages together? In Access, we don't have
paper clips but can do something similar by including a reference to the
related record.

Incidentally, what kinds of information do you store in the [Months]
Table? I can imagine, perhaps, some creative spellings of the month's
name, or maybe a description of holidays occurring in it, but usually
there's not much interesting about a month that you can't compute by
knowing the date. It's possible that you can completely obliterate the
[Months] Table without affecting the way your database functions, if you
do it properly.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Chey wrote:

I have three tables
Provider info
Provider Monthly Info
Months

I need to take all 12 months and append them to every provider in provider
info, but I need the information to appear in Provider Monthy Info.

Right now under provider monthy info I have each providers name and then the
month beside it. So each provider is in this table 12 times.

I need to know the easist way to do this. I have attempted this differnt
ways and have had no luck.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top