My query is empty. How do I reestabish relationships to add data

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I have been using the same database for about 6 years. All of a sudden one
query is empty - no data is in it. Something must have gotten corrupted.
How do I refresh or get the query and database working again?

Other suggestions??

Thank you.
 
On Thu, 11 Dec 2008 18:36:00 -0800, Ken

Simply put a backup of the front-end database in place.
You *did* split the app in FE and BE per best practices, right? And
you do have backups?
You can try exporting all objects to a new database.

-Tom.
Microsoft Access MVP
 
I have been using the same database for about 6 years. All of a sudden one
query is empty - no data is in it. Something must have gotten corrupted.
How do I refresh or get the query and database working again?

Other suggestions??

Thank you.
Click to expand...

What about the underlying tables? Do they have data at all, and do they have
the data that you would expect in the query?

And... do you have a backup :-{( ? If you have the SQL text of the query
somewhere, it could be reentered as a new query.
 
Thanks for the feedback. I just returned from an out of town funeral, so am
late responding. I taught myself enough Access to create this database about
7-8 years ago. I haven't done any programming since and am not an expert in
any way with programming Access. I did not do anything to separate a FE from
a BE -- I don't know what is involved doing that and did not do it.

The empty query I first looked at calls upon another query that is also
empty. So the problem is with this new query call Monthly Billing Query.
This gets it input from tables. All the tables have data in them. I don't
know why the Monthly Billing Query tries to get data from tables with data in
them, but the query is empty.

I do have a backup that is about a month old. One solution is to use the
month old backup and figure out all the entries made in the past month and
reenter them. I would like to find out why the database went bad, though and
fix it if I can.

John Vinson asked about SQL text. I have not wored with SQL text, so I
don't have it documented anyplace.

Any suggestions on how to recreate/fix the damaged database?
Thanks.
 
Ken

Backup, backup, backup!

First, backup. Then check to see if that month-old backup works properly.
If so, you could use it. You wouldn't have to (manually) re-enter the last
month's data, since you could use queries to import the most recent data
into your (restored-from-backed-up) new version.

Are you saying that you current tables DO have the right data, it's just
that the queries are not showing it?

If so, consider posting the SQL statement of the query (ies) that isn't
(aren't) working. Open the query in design view, switch to SQL view,
copy/paste into your post. And tell us about your table structure (and
examples of the data). "How" to retrieve data from a table depends on what
data is there!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Following is the SQL and a little other information on the 2 queries
analysis. The tables are fairly simple. I don't know what information I
can give on them. Following the Query is some information on the Customners
- the largest table. I noticed I made an update to the Billing Customer
Query on Nov. 8. That is the last update that works. I believe all I did
was change the date in a text field from "2009" to "2010". If that is all I
did, it shouldn't have caused a problem.

Customer_Business.mdb
Query: Monthly Billing Query Page: 1
Properties
DateCreated: 12/26/2003 3:10:25 PM DefaultView: 2
FilterOnLoad: False GUID: {guid {25CB069A-1EBF-455B-
A99A-EF53FED3AAC3}}
LastUpdated: 11/9/2008 11:31:12 AM MaxRecords: 0
ODBCTimeout: 60 OrderByOn: False
OrderByOnLoad: True Orientation: Left-to-Right
RecordLocks: No Locks RecordsAffected: 0
RecordsetType: Dynaset ReturnsRecords: True
TotalsRow: False Type: 0
Updatable: True

SQL
SELECT Customers.[Customer ID], Customers.CompanyName, Customers.Prefix,
Customers.ContactFirstName, Customers.Title, Customers.[Social Names],
Customers.[Billing Address], Customers.City, Customers.State,Customers.[Zip
Code], [Sales Tax Rate].County, "System Location: " & Trim([System Address])
& "." AS SystemLocation, Customers.[System Address], Customers.CityS,
Customers.[State S], Customers.[Zip Code S], [Type of Service].[Type of
Service], Format([Anniversary Month],"mmmm-dd") & ", 2010." AS
Anniversary,[Monitoring Price].[Payment Period], [Monitoring Price].[Monthly
Payment], [Sales Tax Rate].[Tax Rate],Trim([Prefix]) & " " &
Trim([ContactFirstName]) & " " & Trim([ContactLastName]) AS NameFull,
Trim([City])& ", " & Trim([State]) & " " & [Zip Code] AS AddressFull, "$ " &
Trim([Monthly Payment]) & " per month, paid annually." AS Monthly, [Monthly
Payment]*12 AS [Annual Monitoring], Format([Annual
Monitoring]*[Tax Rate],"$#,###.00") AS [Monitoring Tax], [Annual
Monitoring]+[Monitoring Tax] AS [Monitoring Total], Format([Anniversary
Month],"mmmm") AS [Monitoring Renewal], [Monitoring Price].[Monitoring ID],
[Rate Structure].[Rate/Hour], Format([Billing Invoices]![Invoice Date],"yy")
& Trim([Billing Invoices]![Invoice Number]) AS [Invoice No], [Billing
Invoices].[Invoice Date], [Temporary Value Holder].Month,TemporaryHolder
Customer ID]![Customer ID] AS CustomerFROM [Temporary Value Holder],
[Temporary Holder Customer ID], [Rate Structure] INNER JOIN ((([Type
ofService] INNER JOIN ([Sales Tax Rate] INNER JOIN Customers ON ([Sales Tax
Rate].Counties = Customers.Counties) AND ([Sales Tax Rate].Counties =
Customers.Counties)) ON ([Type of Service].[ServiceID] = Customers.[Service
ID]) AND ([Type of Service].[Service ID] = Customers.[Service ID])) INNER
JOIN[Monitoring Price] ON Customers.[Monitoring ID] = [Monitoring
Price].[Monitoring ID]) INNER JOIN [Billing Invoices] ON Customers.[Customer
ID] = [Billing Invoices].[Customer ID]) ON [Rate Structure].[Rate ID] =
Customers.[Rate ID]ORDER BY [Billing Invoices].[Invoice Date] DESC;




Query: Payments Received Query
Page: 1


Properties

DateCreated: 1/14/2004 12:53:58 DefaultView:
2
LastUpdated: 3/16/2004 10:31:34 MaxRecords:
0
ODBCTimeout: 60 OrderByOn:
False
Orientation: Left-to-Right RecordLocks:
No Locks
RecordsAffected: 0 RecordsetType:
Dynaset
ReturnsRecords: True Type:
0

Updatable: True



SQL
SELECT [Payments Received].[Posting No], [Payments
Received].[Customer ID],[Monthly Billing Query].CompanyName, [Monthly Billing
Query].NameFull,[Payments Received].[Payment Date], [Payments
Received].[Payment Amount],[Payments Received].[Check No], [Payments
Received].[Method of Payment],[Payments Received].Comments, [Monthly Billing
Query].ContactFirstName,[Payments Received].[Posting No] FROM
[Payments Received] INNER JOIN [Monthly Billing Query] ON [Payments
Received].[Customer ID] = [Monthly Billing Query].[Customer ID]


Customer_Business.mdb
Table: Customers Page: 1
Properties
DateCreated: 8/18/2003 4:21:33 PM DefaultView: 2
LastUpdated: 12/21/2008 11:57:30 AM OrderByOn: False
Orientation: Left-to-Right RecordCount: 98
Updatable: True
Columns
Name Type Size
Customer ID Text 6
CompanyName Text 50
Prefix Text 10
ContactFirstName Text 30
Middle Initial Text 2
ContactLastName Text 50
Title Text 20
Social Names Text 23
Billing Address Text 125
City Text 30
State Text 14
Zip Code Text 20
Counties Text 12
System Address Text 125
CityS Text 30
State S Text 14
Zip Code S Text 20
Special Billing Yes/No 1
System Phone Text 30
Phone No 2 Text 30
Extension Text 8
Note Phone Text 30
MobilePhone Text 30
Alternate Phone Text 30
Alt Phone Note Text 30
FaxNumber Text 30
EmailAddress Text 30
Anniversary Month Date/Time 8
Account No Text 9
Monitoring ID Text 6
Equipment ID Text 8
Service ID Text 8
Rate ID Text 3
Notes Text 125
Relationships

C:\Users\Kenneth Freer\Documents\MS Access\Security Electronics\SEC Sunday,
December 21, 2008
Customer_Business.mdb
Table: Customers Page: 2
EquipmentCustomers
Equipment Customers
Equipment ID 1 ï‚¥ Equipment ID
Attributes: Enforced
RelationshipType: One-To-Many
Monitoring PriceCustomers
Monitoring Price Customers
Monitoring ID Monitoring ID
Attributes: Not Enforced
RelationshipType: One-To-Many
Rate StructureCustomers
Rate Structure Customers
Rate ID 1 ï‚¥ Rate ID
Attributes: Enforced, Cascade Updates, Cascade Deletes
RelationshipType: One-To-Many
Type of ServiceCustomers
Type of Service Customers
Service ID Service ID
Attributes: Not Enforced
RelationshipType: One-To-Many
Sales Tax RateCustomers
Sales Tax Rate Customers
Counties Counties
Attributes: Not Enforced
RelationshipType: One-To-Many
Type of ServiceCustomers
Type of Service Customers
Service ID Service ID
Attributes: Not Enforced
RelationshipType: One-To-Many

Thanks. (I hope this isn't too much information.)
 
Back
Top