How to create a query with duplicate fields and restrict data by f

M

MLS1765

I need to create queries using multiple tables with duplicate fields:
Date,StoreNum,Netsales and not show all records from all tables I use.

I have tried relationship joins and it has not been successful.

Thanks
 
J

Jeff Boyce

?You have more than one table with the same fields? That sounds like a
spreadsheet, not a relational database. Is this an Access question?

Could you provide an example of your multiple tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

MLS1765

Yes they are files from fast food restruants with duplicate store numbers in
each table with different types of info. One has finacial date-sales, one
has food costs, one has labor hours, but the store numbers repeat because the
locations are the same but the Rectype field has a diff code for each table.
CD1, ST1, SF1 etc
STORENUM BUSDATE RECTYPE SEQ_NUM FILLER1 VOUCHERPUR GCREDEEMED OTHERDOLLA FILLER2 OPFUND DEPOSIT1 DEPOSIT2 DEPOSIT3 DEPOSIT4 DEPOSIT5 DEPOSIT6 PRPAYOUT MISSFUNDS FILLER
004428 20050413 CD1 6 0.00 0.00 0.00 1234.08 1067.46 0.00 0.00 0.00 0.00 0.00 0.00
015665 20050414 CD1 0 0.00 0.00 0.00 1105.61 1282.21 0.00 0.00 0.00 0.00 0.00 0.00
005222 20050414 CD1 0 0.00 0.00 0.00 895.96 1187.46 0.00 0.00 0.00 0.00 0.00 0.00
004428 20050414 CD1 5 0.00 0.00 0.00 1277.78 1220.34 0.00 0.00 0.00 0.00 0.00 0.00
these are dbase IV formats and the data above has been opened in Excel but
the orig files I am using are database files. This shows how the tables are
laid out. I get all records in all tables relating to the store location
when I run my query.

I only want selected fields from the CD1 table and selected records from
the ST1 table to show up on one combined query and related report. I can't
get this to happen.

The files are indexed by the Corp. Co. and I can't change those indexes.
BUT I should be able to get the data into one combination file/table/report
somehow. I just have been unsuccessful.

Thanks so much.
MLS1765
 
J

Jeff Boyce

I'm still a bit fuzzy on the structure of your data, but it sounds like you
created one table per store, per file/info type. Regardless of where the
data originates, there's no reason why you couldn't create a well-normalized
data structure in Access, and "parse" the incoming data into the correct
tables.

If you are saying you have identically-structured records coming from
multiple stores, all like-structured data belongs in one table, with the
storeID to show which one it belongs to.

It sounds like you are saying the data you receive concerning food costs is
NOT structured the same way the the one for labor hours is structured. But
if all this information is related to a specific store, why not put all the
related information together?

A comment on your example... any time I see repeating fields (in your
example, "DEPOSIT1", "DEPOSIT2", "DEPOSIT3", ...) I see ... a spreadsheet!,
not a relational database.

Forget the initial way the data is organized for a moment and consider how
the data is related. Build your Access table structure accordingly. Then
figure out how to transform the incoming data into the relational structure
....

Good luck!

Jeff Boyce
Microsoft Office/Access MVP
 
Top