New to Access: Single query for multiple unrelated tables

[caringsharingbristolbilly]

Hello. I'm pretty up on my Excel knowledge, but have switched to Access recently for a database I'm working on. I now realise how much Excel 'nannies' you!

Anyway, my problem.

I have a database consisting of 8 data tables showing client data. The data items on the tables aren't related, but they do share 2 column headings and data types within those headings (one of which is Date, the other is Status). I want to do a report showing the Status Categories I've applied for clients over a certain date range, broken down by each of the 8 tables. As I want the user to be prompted for the Start Date and End Date, and I don't want them to be prompted 8 times, is there any way I can include all 8 tables in one query?

When I've tried, it keeps asking me to set up a relationship for the tables, but they aren't really related as they don't share client information. Or are they?

If you need any clarification at all, please ask.

As I'm feeling Access and I are getting off to a shaky start, any help would be gloriously received, and I will send a packet of cadbury's buttons via e-mail (rather a jpeg of some) to anyone who can help me out!!

Thanks in advance,
Bill Knibb

 

[bat17]

I would use a form to hold your start and end dates, you can link you query criteria to these fields using the builder.
You can then add a button to the form to open the report for you. You can also add validation to the button to check that the dates requested make sense!

HTH

peter

 

[caringsharingbristolbilly]

Re: New to Access: Single query for multiple unrelated table

Hi Peter,

Thanks for that.

I'm probably being thicker than the offspring of a village idiot and a TV weathergirl, but where exactly would the extra form hold this data? Would it need to be held on a new table, or somewhere else? Also, what's the syntax for referring to it when I do get it input?

Many apologies for being so dense, but even the book I've got is tricky to get your head round when new to it all...

Cheers again...

 

[bat17]

Create a new form, not attached to anything. Add two textboxes, again un-attached and give them sensible names.
txtStartDate
txtEndDate

Save the form as frmOpenReport

the criteria for the query would be
>=[Forms]![frmOpenReport]![txtStartDate] and <=[Forms]![frmOpenReport]![txtStartDate]

you can use the wizard on the toolbar to create a button that will then open your report for you.

Of course, if you have a convenient form you always stuff the textboxes there and adjust criteria to suit


HTH

Peter

 

[caringsharingbristolbilly]

Re: New to Access: Single query for multiple unrelated table

I've done exactly as you said, and the query seems to run okay, but the actual report (which I constructed using a basic Report Wizard kinda deely) appears with no information on it... Any ideas? I've tried the button with the standard Open Report function, and also getting it to run a macro, but I can't get the Report itself to include the data...

Oh, and when I just run the report on its own (i.e. not from the new form I've created) it runs fine... It seems to be losing the start and end date data during the button click operation or something...

Any help much appreciated again...

 

[bat17]

Only thing that I can think of is that the code is posting some criteria with the open command. Can you post the code behind the button for us to look at please.

Peter

 

[caringsharingbristolbilly]

Re: New to Access: Single query for multiple unrelated table

Apologies if this is too much, but I thought it'd be better than nothing given the fact I'm not sure what I'm doing...
RufusAddressDATEStatusReport is the query-based report I'm trying to open.


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "RufusAddressDATEStatusReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "RufusAddressDATEStatusReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

Private Sub Detail_Click()

End Sub

 

[bat17]

The code looks OK. Lets see if I have this straight
With the dates in the form
you open one of the queries and get the expected data.
You open the report manualy and get the expected data
you open the report with the button and get a report with no data

If that is the case I am at a loss. could try shut down and reboot. often clears strange happenings! Thumping the monitor does not help but it can make you feel better

peter

 

[caringsharingbristolbilly]

Re: New to Access: Single query for multiple unrelated table

Essentially yes. When I run either the query or the report manually, it asks me for the required information in a pop-up dialog box, and once entered, works fine. When I click on the button on the form (having entered the data in the required boxes), it DOESN'T ask for the information again - presumably it's found it in the text boxes - but brings up a blank report.

I'll try and reboot. If that fails, I'll punch the monitor. If that fails, I'll punch the base unit and throw the mouse and keyboard across the office. If that fails, I'll take the monitor to America, find Mr Gates, and try and insert it in him. As I say, I'm new to Access, but I understand that's the standard troubleshooting technique, right?

Thanks for all your help again...

Postscript: I have just tried rebooting. Same problem. Do you know where Bill lives then?

 

[bat17]

Something is not right as the query should take the data from the form without giving you a pop box, can you post the SQL behind the query please.

Peter