Data entry denied in a query.

[quinto]

I used a database in multiple PC and now I cannot make any entry in the query
or forms.
I changed the Recordset type to Dynaset (Inconsistent Updates) but now I
cannot scroll though the records, only the last enry is dispalyed. What did I
do wrong?
I am a novice and I only use macros, I have no experience in script or
programming.
I joined a query and a table but I cannot do any data entry when I change the
Recordset type to Dynaset (Inconsistent Updates) it will work but when I
enter the ID number the rest of the record, such as the name will not be
displayed, This was warking fine in the past.

Quinto

 

[John W. Vinson]

I joined a query and a table but I cannot do any data entry when I change the
Recordset type to Dynaset (Inconsistent Updates) it will work but when I
enter the ID number the rest of the record, such as the name will not be
displayed, This was warking fine in the past.

Please open both queries (the one you joined to the table, and the resulting
query) in SQL view and post the SQL text here. Not all queries can be updated!

John W. Vinson [MVP]

 

[quinto via AccessMonster.com]

Tank you for your quick response, here is the information you asked.
I am really intrigue by MS Access and I want to leran more.

SELECT Names.ID, Names.Title, Names.Last, Names.First, Names.[Seniority #],
[First] & ' ' & [Last] AS [Names], [Last] & ',' & ' ' & [First] AS Names1,
Names.DOH, Names.Address, Names.City, Names.State, Names.Zip, Names.Status,
Names.LicenseExpire, Names.Phone
FROM [Names];

SELECT AssignMonday.StartLocation, AssignMonday.EndLocation, [Date] &
[DriverID] & [shift] AS CDate, AssignMonday.Seniority, AssignMonday.DriverID,
NameComb.Names1, AssignMonday.Bus, AssignMonday.Status, AssignMonday.Date,
AssignMonday.RDO, AssignMonday.Block, AssignMonday.Shift, AssignMonday.
StartTime, AssignMonday.RevStart, AssignMonday.RevEnd, AssignMonday.EndTime
FROM NameComb RIGHT JOIN AssignMonday ON NameComb.ID = AssignMonday.DriverID
WHERE (((AssignMonday.Block) Not Like "*Off*" And (AssignMonday.Block) Not
Like "*ABs*" And (AssignMonday.Block) Not Like "N/A"));

 

[John W. Vinson]

Tank you for your quick response, here is the information you asked.
I am really intrigue by MS Access and I want to leran more.

SELECT Names.ID, Names.Title, Names.Last, Names.First, Names.[Seniority #],
[First] & ' ' & [Last] AS [Names], [Last] & ',' & ' ' & [First] AS Names1,
Names.DOH, Names.Address, Names.City, Names.State, Names.Zip, Names.Status,
Names.LicenseExpire, Names.Phone
FROM [Names];

You're using the word Names as both the name of your table, *AND* as an alias
for the [First] & ' ' & [Last] expression. That may be causing some confusion.
Also, of course the fields [Names] and [Names1] will not be updateable - only
the underlying [First] and [Last] fields.

SELECT AssignMonday.StartLocation, AssignMonday.EndLocation, [Date] &
[DriverID] & [shift] AS CDate, AssignMonday.Seniority, AssignMonday.DriverID,
NameComb.Names1, AssignMonday.Bus, AssignMonday.Status, AssignMonday.Date,
AssignMonday.RDO, AssignMonday.Block, AssignMonday.Shift, AssignMonday.
StartTime, AssignMonday.RevStart, AssignMonday.RevEnd, AssignMonday.EndTime
FROM NameComb RIGHT JOIN AssignMonday ON NameComb.ID = AssignMonday.DriverID
WHERE (((AssignMonday.Block) Not Like "*Off*" And (AssignMonday.Block) Not
Like "*ABs*" And (AssignMonday.Block) Not Like "N/A"));

The CDate field will likewise not be updateable; and this entire query will
not be updateable unless Names.ID is the Primary Key of the Names table, or at
least has a unique index. I hope you don't have tables named AssignMonday,
AssignTuesday, AssignWednesday...!! That would not be good design.

Note also that Date is a reserved word (for the builtin Date function. You
might want to make this field's name AssignDate or something other than Date.

John W. Vinson [MVP]

 

[quinto via AccessMonster.com]

I definitely understand that this is not a good designed databased, but it
was done out of desperation by me with no experience. I needed something to
make drivers assignments.
What I did is set up uppend queries that takes the day schedules and matched
them to the driver that picked that assignments, creates a table and then I
make adjustment to that table without making changes to the master table. The
reason I use the namecomb query is so all I have to do is enter the
employee ID and the name would be entered by the query in one fiield with the
first and last name joined. As stated this was wotking fine but I kept adding
forms and reports that capture times, attendance, accidents and other actions
associated with transportation. I must have changed some settings that has
interfered with this process., The table that I work with need to accept
multiple ID because a driver may have more than one assignments in one day
but the master does not accept duplicate.

Quinto

Tank you for your quick response, here is the information you asked.
I am really intrigue by MS Access and I want to leran more.

[quoted text clipped - 4 lines]

Names.LicenseExpire, Names.Phone
FROM [Names];

You're using the word Names as both the name of your table, *AND* as an alias
for the [First] & ' ' & [Last] expression. That may be causing some confusion.
Also, of course the fields [Names] and [Names1] will not be updateable - only
the underlying [First] and [Last] fields.

SELECT AssignMonday.StartLocation, AssignMonday.EndLocation, [Date] &
[DriverID] & [shift] AS CDate, AssignMonday.Seniority, AssignMonday.DriverID,

[quoted text clipped - 4 lines]

WHERE (((AssignMonday.Block) Not Like "*Off*" And (AssignMonday.Block) Not
Like "*ABs*" And (AssignMonday.Block) Not Like "N/A"));

The CDate field will likewise not be updateable; and this entire query will
not be updateable unless Names.ID is the Primary Key of the Names table, or at
least has a unique index. I hope you don't have tables named AssignMonday,
AssignTuesday, AssignWednesday...!! That would not be good design.

Note also that Date is a reserved word (for the builtin Date function. You
might want to make this field's name AssignDate or something other than Date.

John W. Vinson [MVP]

 

[John W. Vinson]

I definitely understand that this is not a good designed databased, but it
was done out of desperation by me with no experience. I needed something to
make drivers assignments.
What I did is set up uppend queries that takes the day schedules and matched
them to the driver that picked that assignments, creates a table and then I
make adjustment to that table without making changes to the master table. The
reason I use the namecomb query is so all I have to do is enter the
employee ID and the name would be entered by the query in one fiield with the
first and last name joined. As stated this was wotking fine but I kept adding
forms and reports that capture times, attendance, accidents and other actions
associated with transportation. I must have changed some settings that has
interfered with this process., The table that I work with need to accept
multiple ID because a driver may have more than one assignments in one day
but the master does not accept duplicate.

I'm sorry, this has me completely baffled.

I suspect your "master table" was set up to include *everything* - and that
it's not properly normalized. I have no idea what tables you have or how
they're related, but just FWIW here's how I'd set up a scheduling database:

Drivers
DriverID <Autonumber or manually assigned primary key>
LastName
FirstName
<other biographical data>

Jobs
JobID
JobStart <date/time the job should be started>
<other information about the job>

Assignments
AssignmentID <Autonumber Primary Key>
DriverID <who's assigned to the job>
JobID <what they're assigned to>
JobStarted <when they actually started>
JobCompleted <when they finished>

I don't know what other information you need to track, but you certainly
should NOT need to do any maketable queries or append queries *at all*, unless
I'm completely misunderstanding the problem.

John W. Vinson [MVP]

 

[quinto via AccessMonster.com]

I'm sorry to take so much of your time and thank you for your patience.
It is possible that I am over my head.
I have over 250 querie, 45 forms, 75 different reports, I never counted the
macros

The tables are very simple. Table 1: (Names) has employees personal data ID
number manually assigned as the primary key no duplcate.
Table 2: work schedule for seven days, schedule may changed daily and
diffrent responsabilty daily identified by blocks or route numbers. Column
one ID (Primary Key Duplicate OK). Column two: block or route number. Three:
start time plus 5 other columns with various other times information.
Table 3: has all employees records of attendance and other invformation all
identified by Code for each category.

There are a number of tables used for Combo boxes in the forms.
Each day assignments may change for employee to replace absentee and
reassigned to other work listed in the weekly table.
There are seven tables one for each day, poulated by Append Query from table
2 and it is here that the names and the assignments are brought together with
another simple query, Here is whre the prble arises by not permitting data
entry or not dispalying the names. Changes are made without compromizing the
original work schedule. At the end of the day this information is saved into
another payroll table by append query and deleted from the day table. the
table now is ready for the next week assignments. Because the manager wanted
to be able to work the scheduled ahed of time I created the seve tables.
I want to be able to edit the data in the day table by just changing the ID
number and the name diplayed by query.

I definitely understand that this is not a good designed databased, but it
was done out of desperation by me with no experience. I needed something to

[quoted text clipped - 10 lines]

multiple ID because a driver may have more than one assignments in one day
but the master does not accept duplicate.

I'm sorry, this has me completely baffled.

I suspect your "master table" was set up to include *everything* - and that
it's not properly normalized. I have no idea what tables you have or how
they're related, but just FWIW here's how I'd set up a scheduling database:

Drivers
DriverID <Autonumber or manually assigned primary key>
LastName
FirstName
<other biographical data>

Jobs
JobID
JobStart <date/time the job should be started>
<other information about the job>

Assignments
AssignmentID <Autonumber Primary Key>
DriverID <who's assigned to the job>
JobID <what they're assigned to>
JobStarted <when they actually started>
JobCompleted <when they finished>

I don't know what other information you need to track, but you certainly
should NOT need to do any maketable queries or append queries *at all*, unless
I'm completely misunderstanding the problem.

John W. Vinson [MVP]