Records

M

Mat

hello, i have a form with a subform that the form asks for a persons name,
and the subform asks for a product and quantity, and works out the price from
there.

the only problem is, that the record navigation bar shows that there are 30
records, when there are only 13 receipts.

can someone help me with this please?
 
G

Graham R Seach

Mat,

My guess is that you have some extra, empty, data in that table. Open up the
table itself and have a look. If there are some blank records in there,
delete them, but you'll need to find out why your design allows these
records.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
M

Mat

hi graham

i have just had a look, and i deleted all records in the tables that are
associated with the forms, and entered information back into them, and it
still did the same thing.

i have found out, though, that when i use the information brought from
Queries into a form and a subform, that's when it does it. But, when i bring
the information from tables, it doesn't do it, it only counts the receipts
that i have.

Is there a way i can avoid this, because i already have information setup
using queries, like working out Subtotals, and totals, or will i have to use
the actual tables?

thankyou in advance
 
G

Graham R Seach

Mat,

Then it's probably the queries. If you post the SQL, we can see if there's
anything obvious that we can fix.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
M

Mat

Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No] =
Order1.[Receipt No]) ON [Products List].[Product Name] = Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product Name]
= Order1.[Product Name];


Thank you in advance
 
M

Mat

Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No] =
Order1.[Receipt No]) ON [Products List].[Product Name] = Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product Name]
= Order1.[Product Name];


Thank you in advance
 
M

Mat

Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No] =
Order1.[Receipt No]) ON [Products List].[Product Name] = Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product Name]
= Order1.[Product Name];


Thank you in advance
 
G

Graham R Seach

Matt,

I'm guessing the second query is used for the subform. Having Customer.[Full
Name] in the SELECT clause will return the customer name for every order
line item they have. Since you already have that information on the main
form (courtesy of the first query), you don't need it on the subform, so I'd
say remove it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Mat said:
Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON
Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No] =
Order1.[Receipt No]) ON [Products List].[Product Name] = Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product
Name]
= Order1.[Product Name];


Thank you in advance

Graham R Seach said:
Mat,

Then it's probably the queries. If you post the SQL, we can see if
there's
anything obvious that we can fix.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
Click to expand...
 
G

Graham R Seach

Mat,

Have you checked each of the 30 records its returning. What fields are
missing or are there duplicates?


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Mat said:
Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON
Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No] =
Order1.[Receipt No]) ON [Products List].[Product Name] = Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product
Name]
= Order1.[Product Name];


Thank you in advance

Graham R Seach said:
Mat,

Then it's probably the queries. If you post the SQL, we can see if
there's
anything obvious that we can fix.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
Click to expand...
 
M

Mat

hi

i never saw this before, but, in the first query, when it is run, it returns
duplicate values. like, if there are more than one record with the same
receipt number in the second query, for example 3, then the first query
returns 3 records that have the same receipt number, with the same full name,
the same address etc...

mat

Graham R Seach said:
Mat,

Have you checked each of the 30 records its returning. What fields are
missing or are there duplicates?


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Mat said:
Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON
Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No] =
Order1.[Receipt No]) ON [Products List].[Product Name] = Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product
Name]
= Order1.[Product Name];


Thank you in advance

Graham R Seach said:
Mat,

Then it's probably the queries. If you post the SQL, we can see if
there's
anything obvious that we can fix.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

hi graham

i have just had a look, and i deleted all records in the tables that
are
associated with the forms, and entered information back into them, and
it
still did the same thing.

i have found out, though, that when i use the information brought from
Queries into a form and a subform, that's when it does it. But, when i
bring
the information from tables, it doesn't do it, it only counts the
receipts
that i have.

Is there a way i can avoid this, because i already have information
setup
using queries, like working out Subtotals, and totals, or will i have
to
use
the actual tables?

thankyou in advance

:

Mat,

My guess is that you have some extra, empty, data in that table. Open
up
the
table itself and have a look. If there are some blank records in
there,
delete them, but you'll need to find out why your design allows these
records.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

hello, i have a form with a subform that the form asks for a persons
name,
and the subform asks for a product and quantity, and works out the
price
from
there.

the only problem is, that the record navigation bar shows that there
are
30
records, when there are only 13 receipts.

can someone help me with this please?
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Mat,

I assume (in the first query) that Query2 returns all the line items for the
order. If that's the case, then it's going to return a complete dataset for
every line item. If there are 3 line items for a specific person, then
you'll get 3 records in the dataset.

If the first query is for the main form, eliminate all the unnecessary
fields, returning only those fields relating to the person and the order
itself - not the line items. This might require that you write a SELECT
DISTINCT query.

If you post the table structures, we might be able to help with that.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Mat said:
hi

i never saw this before, but, in the first query, when it is run, it
returns
duplicate values. like, if there are more than one record with the same
receipt number in the second query, for example 3, then the first query
returns 3 records that have the same receipt number, with the same full
name,
the same address etc...

mat

Graham R Seach said:
Mat,

Have you checked each of the 30 records its returning. What fields are
missing or are there duplicates?


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Mat said:
Hey,

This is the SQL for the first query: -
SELECT Order2.[Receipt No], Order2.[Full Name], Date() AS [Date],
Order2.[Employee ID], Customer.Address, Customer.[Phone Number],
Customer.[E-mail Address], Customer.Fax, Order2.Cash, Order2.Cheque,
Order2.EFTPOS, Order2.[Credit Card]
FROM [Products List] INNER JOIN ((Order2 INNER JOIN Customer ON
Order2.[Full
Name] = Customer.[Full Name]) INNER JOIN Order1 ON Order2.[Receipt No]
=
Order1.[Receipt No]) ON [Products List].[Product Name] =
Order1.[Product
Name];


And, here is the SQL for the second query: -
SELECT Order1.[Receipt No], Customer.[Full Name], Order1.[Product
Name],
Order1.Quantity, [products list.sales price]*[order1.quantity] AS
SubTotal
FROM [Products List] INNER JOIN ((Customer INNER JOIN Order2 ON
Customer.[Full Name] = Order2.[Full Name]) INNER JOIN Order1 ON
Order2.[Receipt No] = Order1.[Receipt No]) ON [Products List].[Product
Name]
= Order1.[Product Name];


Thank you in advance

:

Mat,

Then it's probably the queries. If you post the SQL, we can see if
there's
anything obvious that we can fix.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

hi graham

i have just had a look, and i deleted all records in the tables that
are
associated with the forms, and entered information back into them,
and
it
still did the same thing.

i have found out, though, that when i use the information brought
from
Queries into a form and a subform, that's when it does it. But, when
i
bring
the information from tables, it doesn't do it, it only counts the
receipts
that i have.

Is there a way i can avoid this, because i already have information
setup
using queries, like working out Subtotals, and totals, or will i
have
to
use
the actual tables?

thankyou in advance

:

Mat,

My guess is that you have some extra, empty, data in that table.
Open
up
the
table itself and have a look. If there are some blank records in
there,
delete them, but you'll need to find out why your design allows
these
records.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

hello, i have a form with a subform that the form asks for a
persons
name,
and the subform asks for a product and quantity, and works out
the
price
from
there.

the only problem is, that the record navigation bar shows that
there
are
30
records, when there are only 13 receipts.

can someone help me with this please?
Click to expand...
Click to expand...
Click to expand...
 
Top