Split database, modified backend, imported and having record probl

[Guest]

I have a split database and I needed to make some small changes to one of the
tables in the backend and I added four fields. In the frontend, I created my
queries and reports... Everything is working, so what I needed to do was to
actually make these same modification in the actual working DB. After I made
a copy, I modified the backend and added the four fields... then I copied the
frontend into "actual" database. And every thing works. HOWEVER, I noticed
the tblTransports has 17612 records on the bottom of the table, but when I
open the frmTransports it only shows 16548 (so does the qryTransport).

Are you not allowed to modify a table on split database? If yes, should I be
importing or exporting information from one database to another? What could
be the problem with regards to the # of records? (I did go to the backup
version of my database and the record # is the same 17612 in the table, query
and form, so is this my indication that I didn't do something right????)

Any feedback is welcome---thank God for backups!!! But I really do want to
make these changes and find out what went wrong and how to do it right. And
yes, I have linked the front end to the backend, and I am still getting 17612
in tblTransport, and 16548 in qryTransport and frmTransport... I have even
try linking the tables to the backend of the backup and it still produces the
same results.

Thanks, m.

 

[Douglas J. Steele]

What's the SQL for qryTransport? Odds are it's limiting the number of
records returned (Does it include SELECT DISTINCT or GROUP BY? Does it have
WHERE conditions that may be omitting fields with Nulls in them?)

 

[Guest]

Hey Doug,

Here is my SQL and I don't see Select Distinct or Group By, or a Where
condition. Am I missing something?:

SELECT tblCustomers.CustomerID, tblCustomers.Customer,
tblCustomers.CustPrimaryContact, tblCustomers.CustPhone,
tblTransports.TransID, tblTransports.TransSource,
tblTransports.TransDriverID, tblTransports.TransTruckID,
tblTransports.TransPullerId, tblTransports.TransDateIn,
tblTransports.TransDropDate, tblTransports.TransDeliverBy,
tblTransports.TransporterID, tblTransports.TransCustomerID,
tblTransports.TransCustomer, tblTransports.TransPrimaryContact,
tblTransports.TransCustomerPhone, tblTransports.TransReference,
tblTransports.TransStock, tblTransports.TransVIN, tblTransports.TransYear,
tblTransports.TransMake, tblTransports.TransModel,
tblTransports.TransPickUpFrom, tblTransports.TransDestination,
tblTransports.TransBillTo, tblTransports.TransNotes,
tblTransports.GenerateBill, tblTransports.TransDriverRate,
tblTransports.TransCharge, tblTransports.DriverNotes,
tblTransports.TransPullerRate, tblTransports.TransportCoRate,
tblTransports.Damage, tblTransports.OtherCosts,
[TransDriverRate]*[paypercentage] AS CalculatePay,
[CalculatePay]+[TransPullerRate]+[TransportCoRate]+[Damage]+[OtherCosts] AS
TotalTransportExpense, [TransCharge]-[TotalTransportExpense] AS TransportDelta
FROM tblDrivers INNER JOIN (tblCustomers INNER JOIN tblTransports ON
tblCustomers.CustomerID=tblTransports.TransCustomerID) ON
tblDrivers.DriverID=tblTransports.TransDriverID
ORDER BY tblTransports.TransID;

 

[Douglas J. Steele]

You say there are 17612 rows in the table, and only 16548 in the query. Your
query joins together multiple tables. Are you sure that there's a matching
DriverID in tblDrivers for every value of TransDriverID in tblTransports,
and a matching CustomerID in tblCustomers. for every value of
TransCustomerID in tblTransports?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hey Doug,

Here is my SQL and I don't see Select Distinct or Group By, or a Where
condition. Am I missing something?:

SELECT tblCustomers.CustomerID, tblCustomers.Customer,
tblCustomers.CustPrimaryContact, tblCustomers.CustPhone,
tblTransports.TransID, tblTransports.TransSource,
tblTransports.TransDriverID, tblTransports.TransTruckID,
tblTransports.TransPullerId, tblTransports.TransDateIn,
tblTransports.TransDropDate, tblTransports.TransDeliverBy,
tblTransports.TransporterID, tblTransports.TransCustomerID,
tblTransports.TransCustomer, tblTransports.TransPrimaryContact,
tblTransports.TransCustomerPhone, tblTransports.TransReference,
tblTransports.TransStock, tblTransports.TransVIN, tblTransports.TransYear,
tblTransports.TransMake, tblTransports.TransModel,
tblTransports.TransPickUpFrom, tblTransports.TransDestination,
tblTransports.TransBillTo, tblTransports.TransNotes,
tblTransports.GenerateBill, tblTransports.TransDriverRate,
tblTransports.TransCharge, tblTransports.DriverNotes,
tblTransports.TransPullerRate, tblTransports.TransportCoRate,
tblTransports.Damage, tblTransports.OtherCosts,
[TransDriverRate]*[paypercentage] AS CalculatePay,
[CalculatePay]+[TransPullerRate]+[TransportCoRate]+[Damage]+[OtherCosts]
AS
TotalTransportExpense, [TransCharge]-[TotalTransportExpense] AS
TransportDelta
FROM tblDrivers INNER JOIN (tblCustomers INNER JOIN tblTransports ON
tblCustomers.CustomerID=tblTransports.TransCustomerID) ON
tblDrivers.DriverID=tblTransports.TransDriverID
ORDER BY tblTransports.TransID;

What's the SQL for qryTransport? Odds are it's limiting the number of
records returned (Does it include SELECT DISTINCT or GROUP BY? Does it
have
WHERE conditions that may be omitting fields with Nulls in them?)

 

[Guest]

Doug--

Okay, you were right!!!!! (yes, that's right a woman telling you "you're
right", but you probably get that all the time)... I pulled a query and there
are exactly 1064 records WITHOUT transDriverID. Apparently, I did not make
this a required field in the table.

So what would be the best remedy to fix this problem? Do I go back to all
the records and fill in this field (and do I have to do this in order to move
forward)? Can I make this a required field in the back end of the split
database, or do I do something as an event, like AFTER UPDATE (this is a
combo box on the form) to prevent the user from not selecting a transDriverID?

Thanks for figuring this out... It was driving me NUTs. Anymore help or
advise is extremely appreciated!!!! m

You say there are 17612 rows in the table, and only 16548 in the query. Your
query joins together multiple tables. Are you sure that there's a matching
DriverID in tblDrivers for every value of TransDriverID in tblTransports,
and a matching CustomerID in tblCustomers. for every value of
TransCustomerID in tblTransports?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hey Doug,

Here is my SQL and I don't see Select Distinct or Group By, or a Where
condition. Am I missing something?:

SELECT tblCustomers.CustomerID, tblCustomers.Customer,
tblCustomers.CustPrimaryContact, tblCustomers.CustPhone,
tblTransports.TransID, tblTransports.TransSource,
tblTransports.TransDriverID, tblTransports.TransTruckID,
tblTransports.TransPullerId, tblTransports.TransDateIn,
tblTransports.TransDropDate, tblTransports.TransDeliverBy,
tblTransports.TransporterID, tblTransports.TransCustomerID,
tblTransports.TransCustomer, tblTransports.TransPrimaryContact,
tblTransports.TransCustomerPhone, tblTransports.TransReference,
tblTransports.TransStock, tblTransports.TransVIN, tblTransports.TransYear,
tblTransports.TransMake, tblTransports.TransModel,
tblTransports.TransPickUpFrom, tblTransports.TransDestination,
tblTransports.TransBillTo, tblTransports.TransNotes,
tblTransports.GenerateBill, tblTransports.TransDriverRate,
tblTransports.TransCharge, tblTransports.DriverNotes,
tblTransports.TransPullerRate, tblTransports.TransportCoRate,
tblTransports.Damage, tblTransports.OtherCosts,
[TransDriverRate]*[paypercentage] AS CalculatePay,
[CalculatePay]+[TransPullerRate]+[TransportCoRate]+[Damage]+[OtherCosts]
AS
TotalTransportExpense, [TransCharge]-[TotalTransportExpense] AS
TransportDelta
FROM tblDrivers INNER JOIN (tblCustomers INNER JOIN tblTransports ON
tblCustomers.CustomerID=tblTransports.TransCustomerID) ON
tblDrivers.DriverID=tblTransports.TransDriverID
ORDER BY tblTransports.TransID;

What's the SQL for qryTransport? Odds are it's limiting the number of
records returned (Does it include SELECT DISTINCT or GROUP BY? Does it
have
WHERE conditions that may be omitting fields with Nulls in them?)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a split database and I needed to make some small changes to one
of
the
tables in the backend and I added four fields. In the frontend, I
created
my
queries and reports... Everything is working, so what I needed to do
was
to
actually make these same modification in the actual working DB. After I
made
a copy, I modified the backend and added the four fields... then I
copied
the
frontend into "actual" database. And every thing works. HOWEVER, I
noticed
the tblTransports has 17612 records on the bottom of the table, but
when I
open the frmTransports it only shows 16548 (so does the qryTransport).

Are you not allowed to modify a table on split database? If yes, should
I
be
importing or exporting information from one database to another? What
could
be the problem with regards to the # of records? (I did go to the
backup
version of my database and the record # is the same 17612 in the table,
query
and form, so is this my indication that I didn't do something
right????)

Any feedback is welcome---thank God for backups!!! But I really do want
to
make these changes and find out what went wrong and how to do it right.
And
yes, I have linked the front end to the backend, and I am still getting
17612
in tblTransport, and 16548 in qryTransport and frmTransport... I have
even
try linking the tables to the backend of the backup and it still
produces
the
same results.

Thanks, m.