LU table getting records added

[LMB]

Hi Everyone,

Using Access 2000 and can only do fairly simple databases as I have not much
formal programming training or a large amount of time to learn the more
difficult things. I have a couple of tables in my database. One was
designed to be just a look up table and a field on my form uses these fields
to insert the data. I am sure you will need more information so let me know
is easy terms what you need. The problem is that when I enter records into
the Worked area field on my form, my lookup table gets records added to it.
I started out with 9 work areas. I entered the records for one day and now
there are 18 records. Access assigns the new work areas an auto ID number
so the same work area name has 2 ID's and when I add more records there will
be work areas with hundreds of ID's. The funny thing is that my crosstab
query works fine. It reports the number of hours each employee works in a
work area. When I looked at the data from last year, I had 7000 records in
this table when it is only supposed to have 9 and be a look up table.

I have my main table

tblEmployee
EmployeeID-PK

Then my other tables to keep track of hours worked in certain areas. This
helps us to rotate the employees evenly between areas.

tblAreaWorked
AreaWorkedID-PK
EmployeeID-FK
WorkAreaID-FK
WorkAreaDate
WorkAreaHours

tblWorkedArea
WorkedAreaID-PK
WorkedArea

My relationship is a one to many from the tblEmployee (EmployeeID-PK) to the
tblAreaWorked (EmployeeID-FK) and a one to many from the tblWorkedArea
(WorkedAreaID-PK) to the tblAreaWorked (WorkedAreaID-FK)

Thanks,
Linda

 

[Guest]

Hi Linda,

The problem is that when I enter records into the Worked area field on
my form, my lookup table gets records added to it.

This likely means that you assigned WorkedAreaID as the control source for
the combo box, instead of WorkAreaID (the foreign key).

Access assigns the new work areas an auto ID number
so the same work area name has 2 ID's

tblWorkedArea
WorkedAreaID-PK

A primary key cannot be entered twice, so I'm thinking that you may have
intended to set WorkedAreaID as a primary key field, but so far it is simply
an autonumber data type.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Everyone,

Using Access 2000 and can only do fairly simple databases as I have not much
formal programming training or a large amount of time to learn the more
difficult things. I have a couple of tables in my database. One was
designed to be just a look up table and a field on my form uses these fields
to insert the data. I am sure you will need more information so let me know
is easy terms what you need. The problem is that when I enter records into
the Worked area field on my form, my lookup table gets records added to it.
I started out with 9 work areas. I entered the records for one day and now
there are 18 records. Access assigns the new work areas an auto ID number
so the same work area name has 2 ID's and when I add more records there will
be work areas with hundreds of ID's. The funny thing is that my crosstab
query works fine. It reports the number of hours each employee works in a
work area. When I looked at the data from last year, I had 7000 records in
this table when it is only supposed to have 9 and be a look up table.

I have my main table

tblEmployee
EmployeeID-PK

Then my other tables to keep track of hours worked in certain areas. This
helps us to rotate the employees evenly between areas.

tblAreaWorked
AreaWorkedID-PK
EmployeeID-FK
WorkAreaID-FK
WorkAreaDate
WorkAreaHours

tblWorkedArea
WorkedAreaID-PK
WorkedArea

My relationship is a one to many from the tblEmployee (EmployeeID-PK) to the
tblAreaWorked (EmployeeID-FK) and a one to many from the tblWorkedArea
(WorkedAreaID-PK) to the tblAreaWorked (WorkedAreaID-FK)

Thanks,
Linda

 

[Guest]

Hi Again,

Disregard the last sentence I wrote:

"...so I'm thinking that you may have intended to set WorkedAreaID as a
primary key field, but so far it is simply an autonumber data type."

Somehow I interpreted this statement:
"...so the same work area name has 2 ID's"

as ...so the same work area name has 2 [identical] ID's.

You didn't say that, so my last sentence is incorrect.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Linda,

The problem is that when I enter records into the Worked area field on
my form, my lookup table gets records added to it.

This likely means that you assigned WorkedAreaID as the control source for
the combo box, instead of WorkAreaID (the foreign key).

Access assigns the new work areas an auto ID number
so the same work area name has 2 ID's

tblWorkedArea
WorkedAreaID-PK

A primary key cannot be entered twice, so I'm thinking that you may have
intended to set WorkedAreaID as a primary key field, but so far it is simply
an autonumber data type.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Everyone,

Using Access 2000 and can only do fairly simple databases as I have not much
formal programming training or a large amount of time to learn the more
difficult things. I have a couple of tables in my database. One was
designed to be just a look up table and a field on my form uses these fields
to insert the data. I am sure you will need more information so let me know
is easy terms what you need. The problem is that when I enter records into
the Worked area field on my form, my lookup table gets records added to it.
I started out with 9 work areas. I entered the records for one day and now
there are 18 records. Access assigns the new work areas an auto ID number
so the same work area name has 2 ID's and when I add more records there will
be work areas with hundreds of ID's. The funny thing is that my crosstab
query works fine. It reports the number of hours each employee works in a
work area. When I looked at the data from last year, I had 7000 records in
this table when it is only supposed to have 9 and be a look up table.

I have my main table

tblEmployee
EmployeeID-PK

Then my other tables to keep track of hours worked in certain areas. This
helps us to rotate the employees evenly between areas.

tblAreaWorked
AreaWorkedID-PK
EmployeeID-FK
WorkAreaID-FK
WorkAreaDate
WorkAreaHours

tblWorkedArea
WorkedAreaID-PK
WorkedArea

My relationship is a one to many from the tblEmployee (EmployeeID-PK) to the
tblAreaWorked (EmployeeID-FK) and a one to many from the tblWorkedArea
(WorkedAreaID-PK) to the tblAreaWorked (WorkedAreaID-FK)

Thanks,
Linda

 

[LMB]

Hi Tom and Thanks,

I looked at the control source for the combo box on my subform is WorkArea.
In my Control Source I have 4 options to choose from WorkAreaDate, WorkArea,
WorkAreaHours and EmployeeID. When I click on the ... for this property, I
get an expression builder and all that is in there is WorkArea.

Row/Source Type is Table/Query and Row Source has this in it SELECT
[QRYWorkAreas].[WorkArea] FROM [QRYWorkAreas]. I created a query so that
the work area names would be in ABC order instead of ID order.

In my qryWorkAreas there was only the name of the area and not the ID. Is
this the problem? I use wizards a lot so sometimes they insert things that
I don't necessarily understand but seem to work most of the time. When I
click on the ... button next to Row Source an SQL builder appears but only
the worked area is available to insert into the query grid.

Now when I look in the properties of the subform that has that combo box on
it, the record source is qryAreasWorked. In the area just above the query
grid, I have tblAreasWorked and tblWorkArea. My 4 fields in this query are
WorkAreaDate, WorkArea, WorkAreaHours and EmployeeID and are all from
tblAreasWorked. The tables are joined and have the many to one arrow going
from the WorkAreaID in tblAreasWorked to WorkAreaID in the tblWorkArea.
Below is the sql that I don't really grasp yet but may help you to see
what's going on.

SELECT TBLAreaWorked.WorkAreaDate, TBLWorkAreas.WorkArea,
TBLAreaWorked.WorkAreaHours, TBLAreaWorked.EmployeeID
FROM TBLWorkAreas RIGHT JOIN TBLAreaWorked ON TBLWorkAreas.WorkAreaID =
TBLAreaWorked.WorkAreaID
ORDER BY TBLAreaWorked.WorkAreaDate;

Not sure what to do next. My idea is to bring the WorkAreaID-FK from my
tblWorkArea down to the grid and then use that for my control source?

Hope this makes sense, I hope I am getting better at describing this stuff.

Thanks,
Linda

 

[Guest]

Hi Linda,

I think the quickest way for me to help you is if you can send me a
compacted & preferably zipped copy of your database. If necessary, make a
copy and replace any sensitive data with dummy data.

I need to leave for work now, so it will be approx. 10 hours before I can
get back to this.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Guest]

PS. My e-mail address is shown at the bottom of the contributors page in my
signature block. Please do not post your real e-mail address (or mine) to a
newsgroup message. Doing so will only invite the unwanted attention of
spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Linda,

I think the quickest way for me to help you is if you can send me a
compacted & preferably zipped copy of your database. If necessary, make a
copy and replace any sensitive data with dummy data.

I need to leave for work now, so it will be approx. 10 hours before I can
get back to this.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[LMB]

Hi Tom,

I just got back to this too. I'll get it ready and send. There is no real
rush. The thing has worked as far as I can tell for a year the way it is, I
just know something is not right about it and want to get it right. I
promise not to post your address or mine <g>

Thanks,
Linda