Excel/ Access connection and data type

[BittenApple]

Team,

I connected Excel to Access and got the data from Access into Excel by starting a new query and connection. Everything went well and I saw the data into Excel.

Data came into Excel as a table and I have to convert the table into a range. How can I make data in Excel to be shown as a range automatically without manpulation?

I have 3 fields in Access whose data type is Number, when I connect Access to Excel, the connected data in worksheet is displayed as a text. I have to convert them back to numbers. What should I do to prevent this issue?

Regards,

Bita

[HansV]

1. Why don't you want the data to be imported as a table?
2. Are those fields empty?

[BittenApple]

Hello Hans,

1. Why don't you want the data to be imported as a table?
Hans, do you mean connected? because once the data is in range, it is easier to insert a chart on it.
At the end when I split the database, end users will see the form and they shouldn't have anything to do with importing data.
I used connection and it was successful and I could see the query of access on a work sheet and I saw the data in workbook is updated. At least, that has been the case so far.

2. Are those fields empty? No

P.S.
However, I didn't use pivot table and pivot chart, they are very limited, after 2 days working on that, that is what I found out. I easily used a new query and created a connection and I saw the query in Access in Excel and I was able to insert a graph that I wanted, that was impossible with pivot tables and pivot charts.

Regards,
Bita

[HansV]

Can't you use your new query as Row Source for a chart in Access itself?

[BittenApple]

Hans,
Can't you use your new query as Row Source for a chart in Access itself?
Bita: I used the data of the query for charts, but not as a row source for the chart.
The charts, I inserted on Access, they looked very bad. I couldn't get the chart I wanted; in Access, we can only have two data points not three. I need a chart with 3 data point; that is why I searched for another approach.

Regards,
Bita

[HansV]

If you set the Row Source of a chart control to a query, you can have any number of data series.

[BittenApple]

Hello Hans,
Yes I do, but I can't make two series to appear in category x as I can do in Excel.
If I had control over the connection to get the correct data type, that would be great.
Regards,
Bita

[HansV]

Without seeing the database and workbook it's impossible for me to tell what causes the problem.

[BittenApple]

Hans,
I will load a database soon.
I was able to fix the issue with data type.
Regards,
Bita

[BittenApple]

Hello Hans,
I have attached a small database.
I wonder what I should do to make two series to be shown in Category X as in Excel.
Regards,
Bit

[HansV]

I see two queries in the database.
Which one do you want to use for the chart?
And can you give me an idea what you want the chart to look like?
Thanks in advance.

[BittenApple]

Hello Hans,
I need a column chart for both of them.
CompanyA and DateOfArrival would be Category X.

NewField1 and NewField2 would be series.

I am able to get this chart in Excel.

Regards,
Bit

[HansV]

See the attached version.
I modified the queries to combine Company_A and DateOfArrival into a single field.
I removed the Format function; instead, the Format property of the average fields has been set to #.##0.00
I created a form with a chart for each query.

Charting1.zip

[BittenApple]

Hello Hans,
Thanks for your answer!!!
Your first chart: you have concatenated two fields: I have 8 hospitals and 12 months and then I will have 8X12 series, this can't be very readable on a chart on the form; this is what I think.
I appreciate your response so very much!
Bita

[HansV]

Charts in Access cannot have a double x-axis as in Excel.

[BittenApple]