Create an Access Form

[Rubie]

Hello,

I'm trying to create an Access form based on two different queries, but I
get an error message stating that I may need to choose fields from only a
query or only a table. The queries get data from the same table.

My first query does count(*) from a column called (Test) which has data in
it called (car) and (truck) for example and it does give me a total for all
of the data in the column. I also want the query to give me a total on cars
only but I could not get it to do that, so I created a different query to
total cars only and it worked.

Any advice on this would be greatly appreciated.

Thanks,

 

[Golfinray]

Normally, a form is based on one table or query. I would suggest you put the
two tables together or the two queries together. Probably a one-to-many
relationship would be best, then you caould use a subform is necessary.

 

[Rubie]

Thanks Golfinray, I'll give it a try.

Normally, a form is based on one table or query. I would suggest you put the
two tables together or the two queries together. Probably a one-to-many
relationship would be best, then you caould use a subform is necessary.

 

[Rubie]

Hi Golfinray,

I tried linking the queries and creating the form but still got this error
message. (You have chosen fields from record sources which the wizard cannot
connect. You may have chosen fields from a table and from a query based on
that table. If so, try chosing fields from only a table or only a query.)
The field in the queries are the same.

Thanks,

 

[John W. Vinson]

Hello,

I'm trying to create an Access form based on two different queries, but I
get an error message stating that I may need to choose fields from only a
query or only a table. The queries get data from the same table.

My first query does count(*) from a column called (Test) which has data in
it called (car) and (truck) for example and it does give me a total for all
of the data in the column. I also want the query to give me a total on cars
only but I could not get it to do that, so I created a different query to
total cars only and it worked.

Any advice on this would be greatly appreciated.

Thanks,

If you just want to display the counts on the Form, you don't need two queries
at all. Instead create a query based on the table, with two calculated fields:

IsCar: IIF([Test] = "Car", 1, 0)
IsTruck: IIF([Test] = "Truck", 1, 0)

Then in the Footer of the form (you may have to make the footer visible using
the View menu) put a textbox CarCount with a control source

=Sum(IsCar)

etc.

 

[Rubie]

It worked!!!! Thanks so much John!!! Just one thing. How do I get > IsCar:
IIF([Test] = "Car", 1, 0)> to add (car & truck) together and give me the
total.

The way it works now is (when the Query runs the total records is 100 ) and
(when the Form opens Car has 98 and truck has 2). I would like for Car to
show a total of 100. Truck is working great.

Thanks Again.

Hello,

I'm trying to create an Access form based on two different queries, but I
get an error message stating that I may need to choose fields from only a
query or only a table. The queries get data from the same table.

My first query does count(*) from a column called (Test) which has data in
it called (car) and (truck) for example and it does give me a total for all
of the data in the column. I also want the query to give me a total on cars
only but I could not get it to do that, so I created a different query to
total cars only and it worked.

Any advice on this would be greatly appreciated.

Thanks,

If you just want to display the counts on the Form, you don't need two queries
at all. Instead create a query based on the table, with two calculated fields:

IsCar: IIF([Test] = "Car", 1, 0)
IsTruck: IIF([Test] = "Truck", 1, 0)

Then in the Footer of the form (you may have to make the footer visible using
the View menu) put a textbox CarCount with a control source

=Sum(IsCar)

etc.

 

[John W. Vinson]

It worked!!!! Thanks so much John!!! Just one thing. How do I get > IsCar:
IIF([Test] = "Car", 1, 0)> to add (car & truck) together and give me the
total.

To get the total number of records in the form just use a textbox with a
control source

=Count(*)

instead of summing anything.

 

[Rubie]

That did it!!! Thanx a million John.

It worked!!!! Thanks so much John!!! Just one thing. How do I get > IsCar:
IIF([Test] = "Car", 1, 0)> to add (car & truck) together and give me the
total.

To get the total number of records in the form just use a textbox with a
control source

=Count(*)

instead of summing anything.