Getting info out of database

[Jeff F]

Newbie here so I'll try to explain my situation the best I can.
I've got one table with the following:
EODID - End Of Day ID
Date
Gross Sales

a second table with:
EODID
Item Sales
Sizes Sold
(but no date, only the EODID that refers to a specific day)

How do I link the tables where I can do a search (based on date) that will
return info from the table that has no date.

Also, the second table will list multiple sizes (small, medium, large) for
the same EODID. I did a query that would return one of the sizes for a day
but when I added a second size to the same query, it returned nothing.
Thanks for any help

 

[Vincent Johns]

Newbie here so I'll try to explain my situation the best I can.
I've got one table with the following:
EODID - End Of Day ID
Date
Gross Sales

a second table with:
EODID
Item Sales
Sizes Sold
(but no date, only the EODID that refers to a specific day)

I'm a bit suspicious here. Do you intend [Gross Sales] to, at least
some of the time, equal the sum of [Item Sales] values in records that
share an [EODID] value? If so, how do you want to maintain it? You
could calculate the total and then pop up a message that it doesn't
match. Or (preferably, I think) you could erase the [Gross Sales] field
and simply calculate it in a Query whenever you need it.

Is [Item Sales] intended to reflect the amount of money that one person
paid for one [Sizes Sold] item? Actually, you don't mention customers
in these Tables, so you'd have no way based on these data to create a
receipt

How do I link the tables where I can do a search (based on date) that will
return info from the table that has no date.

First, I'd give the Tables suggestive names, such as [SalesDetailsByDay].

Second, if the first Table really contains nothing besides the 3 fields
you showed here (including one that I think should be calculated), the
only thing of value that the first Table adds is the [Date] value.
That's not enough, IMHO, to justify an entire Table. I'd move [Date] to
the second Table.

Also, the field name [Date] isn't very suggestive -- maybe something
more specific like [DateOfSale] would convey more information.

(Some people on this newsgroup advocate avoiding field names like "Date"
because "Date" is a reserved word in VBA. I'm not sure that that's
enough of a reason not to use the name, as Access can identify a field
name like [Date] because of the square brackets around it. But even if
Access is not confused by that name, giving the field a more specific
name could save YOU or another developer a lot of time trying to
remember/infer specifically what kind of data the field contains.)

Also, the second table will list multiple sizes (small, medium, large) for
the same EODID. I did a query that would return one of the sizes for a day
but when I added a second size to the same query, it returned nothing.
Thanks for any help

Then what would a record in the secord Table mean? That a "small" item
had been sold for $5.30 on 11/2/05? Another record in the same Table
might tell you that another "small" item had been sold for $2.77 on
11/2/05 (but not to whom). Is this what you want?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

Newbie here so I'll try to explain my situation the best I can.
I've got one table with the following:
EODID - End Of Day ID
Date
Gross Sales

a second table with:
EODID
Item Sales
Sizes Sold
(but no date, only the EODID that refers to a specific day)

How do I link the tables where I can do a search (based on date) that will
return info from the table that has no date.

Create a Query in the New Query window. Add both tables; join them by
EODID. Put an appropriate criterion on the field [Date] - which, by
the way, you should rename since it's a reserved word. Access *WILL*
get it confused with the Date() today's-date function.

Also, the second table will list multiple sizes (small, medium, large) for
the same EODID. I did a query that would return one of the sizes for a day
but when I added a second size to the same query, it returned nothing.
Thanks for any help

How did you do the size? There ARE no records in the table for which
Size is simultaneously Small and Large. You need to use "OR" logic -
put "Small" on one line of the query grid, and "Large" on the next
line down, so that it will return records if their value of Size is
either "Small" OR "Large". If you have a date criterion on the Date
field, it needs to be on both lines also.


John W. Vinson[MVP]

 

[Jeff F]

I probably need to elaborate more. I'm trying to get data out of my Point of
Sale database (Nexxus?) so I can print weekly, one page reports for my
managers. The info I need is Sales, Void $, customer counts, # of Large
pizzas sold, $ of wine sold, etc. all for the particular day (Mon - Sun) to
compare previous or current weeks numbers. I can import the tables I need
from the Nexxus dbase.
Tables:
DayFileSummary (gives me most of my info)
ItemSummary (Lists all items sold for the day, but does not total "larges,
Smalls"
etc. Menu items are summarized, 3 Large Cheese
pizza. There
will be for instance 10 different entries for a day
for the
different larges that were sold)
Queries:
Size (EODID, Size, qty)
DFSummary (everything from "gross sales, voids, labor cost") I've got a
form to
enter a specific date range. This query refers to that
form in the
criteria.
There actually is no "date" field. I was mistaken. They called it "business
day". They also refer to the days of the week with numbers (1-7) in "DOW"
field. I don't know if I need a separate table to identify what day 1 is or
not.

Overall, I need a one page report highlighting last weeks data broken down
into days. The data is for managers to plan better (labor, prep, etc) We call
it a Flash Report. I originally had this database working with a report that
prompted for a date range (the form) and everything worked fine. Now my data
is coming from a different source, I've got multiple tables and I need to do
calculations. A little more complex. Sorry for my lack of knowledge on
Access, it's been quite a while since college. If there are any (simple!)
templates or such I could get my feet wet, that would be great.
Thanks again.

Newbie here so I'll try to explain my situation the best I can.
I've got one table with the following:
EODID - End Of Day ID
Date
Gross Sales

a second table with:
EODID
Item Sales
Sizes Sold
(but no date, only the EODID that refers to a specific day)

How do I link the tables where I can do a search (based on date) that will
return info from the table that has no date.

Create a Query in the New Query window. Add both tables; join them by
EODID. Put an appropriate criterion on the field [Date] - which, by
the way, you should rename since it's a reserved word. Access *WILL*
get it confused with the Date() today's-date function.

Also, the second table will list multiple sizes (small, medium, large) for
the same EODID. I did a query that would return one of the sizes for a day
but when I added a second size to the same query, it returned nothing.
Thanks for any help

How did you do the size? There ARE no records in the table for which
Size is simultaneously Small and Large. You need to use "OR" logic -
put "Small" on one line of the query grid, and "Large" on the next
line down, so that it will return records if their value of Size is
either "Small" OR "Large". If you have a date criterion on the Date
field, it needs to be on both lines also.


John W. Vinson[MVP]

 

[Vincent Johns]

Have you looked at the Northwind Traders sample database that ships with
Access? It contains example Tables, Queries, Reports, &c., for an
imaginary food-distributing company.

Or you might find one of the sample databases in the New Database wizard
to be helpful, even though it might not do exactly what you want.
Perhaps you would be interested in the sample "Order Entry" database.
You'd have to throw away some parts and add others of your own, but it
might help you.

With either Northwind Traders or one of the New Database examples,
instead of importing your data into the example database, you could copy
useful parts (e.g., Tables) from the example and paste them into your
own database.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I probably need to elaborate more. I'm trying to get data out of my Point of
Sale database (Nexxus?) so I can print weekly, one page reports for my
managers. The info I need is Sales, Void $, customer counts, # of Large
pizzas sold, $ of wine sold, etc. all for the particular day (Mon - Sun) to
compare previous or current weeks numbers. I can import the tables I need
from the Nexxus dbase.
Tables:
DayFileSummary (gives me most of my info)
ItemSummary (Lists all items sold for the day, but does not total "larges,
Smalls"
etc. Menu items are summarized, 3 Large Cheese
pizza. There
will be for instance 10 different entries for a day
for the
different larges that were sold)
Queries:
Size (EODID, Size, qty)
DFSummary (everything from "gross sales, voids, labor cost") I've got a
form to
enter a specific date range. This query refers to that
form in the
criteria.
There actually is no "date" field. I was mistaken. They called it "business
day". They also refer to the days of the week with numbers (1-7) in "DOW"
field. I don't know if I need a separate table to identify what day 1 is or
not.

Overall, I need a one page report highlighting last weeks data broken down
into days. The data is for managers to plan better (labor, prep, etc) We call
it a Flash Report. I originally had this database working with a report that
prompted for a date range (the form) and everything worked fine. Now my data
is coming from a different source, I've got multiple tables and I need to do
calculations. A little more complex. Sorry for my lack of knowledge on
Access, it's been quite a while since college. If there are any (simple!)
templates or such I could get my feet wet, that would be great.
Thanks again.


:

Newbie here so I'll try to explain my situation the best I can.
I've got one table with the following:
EODID - End Of Day ID
Date
Gross Sales

a second table with:
EODID
Item Sales
Sizes Sold
(but no date, only the EODID that refers to a specific day)

How do I link the tables where I can do a search (based on date) that will
return info from the table that has no date.

Create a Query in the New Query window. Add both tables; join them by
EODID. Put an appropriate criterion on the field [Date] - which, by
the way, you should rename since it's a reserved word. Access *WILL*
get it confused with the Date() today's-date function.

Also, the second table will list multiple sizes (small, medium, large) for
the same EODID. I did a query that would return one of the sizes for a day
but when I added a second size to the same query, it returned nothing.
Thanks for any help

How did you do the size? There ARE no records in the table for which
Size is simultaneously Small and Large. You need to use "OR" logic -
put "Small" on one line of the query grid, and "Large" on the next
line down, so that it will return records if their value of Size is
either "Small" OR "Large". If you have a date criterion on the Date
field, it needs to be on both lines also.


John W. Vinson[MVP]