Cross Tab Query Schedule

[crashnburn99]

Good afternoon. I am trying to create a cross tab query that will generate a report in the form of a weekly schedule. Currently we have a table that houses all of our schedules that are linked to our customers table. This is the last piece of the puzzle and after reading a lot of post in regards to similar situations I am still failing to grasp the concept. I need it to have Monday - Friday on the top and 12 AM - 12 PM on the side, while having the details of the job in the middle. I am not sure what information I can provide to help, but I will gladly provide it upon request. Thank you very much for the help.

 

[JHB]

Show/post some sample data, and a "picture" which show how you want it should end up with.

 

[crashnburn99]

I attached an excel sheet that shows how I would like the report to be laid out. I left the middle info blank, I figured I would state the tables and fields here. I tried to upload my database but it was too large. So I will try and be as descriptive as possible. Here are the tables that are involved and the info they that I need from them for the query:

Customers

Last Name
First Name
Business Phone
Home Phone
Mobile Phone
Warranty Company
Maintenance

Addresses

Address
City
State
ZIP

Schedule

Date
Time
Job Details

I hope that helps. Also not all of our customers meet all of the requirements, for example not all customers are Maintenance customers. Not sure if that will matter but I figured all info is good info. Now I may be way off base with this whole thing, but from what I have gathered on this forum and others, a cross tab query is the best way to get a report like this. I will go in any direction that works, so if this is way off please let me know.

EDIT: I removed everything but the tables and I was able to upload it. I hope it helps

 

[JHB]

Sorry, but I need more sample data as only 1 record.
It is easier for you to change them you have than that I should sit down and invent some.

 

[crashnburn99]

I added some more dummy information into the 3 tables that will be used. I hope it is enough, but if not let me know and I will make more alterations. Thank you very much for your help.

 

[JHB]

Now the samples are ok, but I need to know exactly which information you want shown in the "middle", (and from which table the information have to comes).
You can try to put in the information in the picture/spreadsheet you showed in post no. 3, to see if it gives some mening.

 

[crashnburn99]

I was hoping that everything from my second post could go in the middle, but im not 100% sure how crosstab queries work so I dont know if that is do-able.

 

[crashnburn99]

Something like this. It looks pretty crammed but we usually print them out on landscape with smaller font. It is for the employees that are out in the field so they have a sheet of all of their jobs.

 

[JHB]

Try the attach database, open the one form, type in 2 dates and hit the button.

I've also attach a picture from the output.

 

[crashnburn99]

Thank you very much JHB! I tried out the form and it creates the excel sheet just fine but it doesn't populate the scheduled information. I have attached a screenshot of the excel sheet after it was created. Thank you again! This is a lot further than I ever could of gotten.

 

[JHB]

Ok, below (attached file) you se what I get for the same dates. I think it has something to do with the PM/AM format, I'll se what I can do about it after the weekend.

 

[crashnburn99]

Thank you very much JHB!!!!

 

[JHB]

Try the attached database, it should work now, but ... (I have some problem with Excel when I change to U.S. format).
The picture showed the PM/AM in the Excel sheet.

 

[crashnburn99]

JHB thank you so so very much!!! This is perfect!!!

 

[crashnburn99]

Dang it! I moved it over to my database and now when I run it I get an error. Debugger highlights this part of the code:

objXLApp.Cells.Borders.LineStyle = xlContinuous

Claiming a compile error - variable is not defined

any thoughts???

 

[JHB]

You need a reference for the Excel object library, se the picture how to set it, (it don't need to be the same "version" number).

 

[crashnburn99]

THANK YOU SOOOO MUCH!!!! That worked perfect JHB! You are a life saver! Thank you so very much!